Row count with PDO

✔ Recommended Answer

When you need only the number of rows, but not the data itself, such a function shouldn't be used anyway. Instead, ask the database to do the count, with a code like this:

$sql = "SELECT count(*) FROM `table` WHERE foo = ?"; $result = $con->prepare($sql); $result->execute([$bar]); $number_of_rows = $result->fetchColumn(); 

For getting the number of rows along with the data retrieved, PDO has PDOStatement::rowCount(), which apparently does work in MySql with buffered queries (enabled by default).

But it's not guaranteed for other drivers. From the PDO Doc:

For most databases,PDOStatement::rowCount() does notreturn the number of rows affected bya SELECT statement. Instead, usePDO::query() to issue a SELECTCOUNT(*) statement with the samepredicates as your intended SELECTstatement, then usePDOStatement::fetchColumn() toretrieve the number of rows that willbe returned. Your application can thenperform the correct action.

But in this case you can use the data itself. Assuming you are selecting a reasonable amount of data, it can be fetched into array using PDO::fetchAll(), and then count() will give you the number of rows.

EDIT: The above code example uses a prepared statement, but if a query doesn't use any variables, one can use query() function instead:

$nRows = $pdo->query('select count(*) from blah')->fetchColumn(); echo $nRows;

Source: stackoverflow.com

Answered By: karim79

Method #2

To get the number of rows returned by a SELECT query using PDO, you can use the rowCount() method of the PDOStatement object returned by the prepare() method.

Here's an example code snippet:

php
// Create a new PDO instance
$pdo = new PDO('mysql:host=localhost;dbname=mydatabase', 'username', 'password');

// Prepare a SELECT query
$stmt = $pdo->prepare('SELECT * FROM mytable WHERE name = :name');

// Bind a parameter
$stmt->bindParam(':name', $name);

// Execute the query
$stmt->execute();

// Get the number of rows returned
$rowCount = $stmt->rowCount();

// Output the result
echo "Number of rows returned: " . $rowCount;

In this example, we first create a new PDO instance and prepare a SELECT query using a named parameter. We then bind a value to the named parameter using the bindParam() method.

After executing the query using the execute() method, we can use the rowCount() method to get the number of rows returned by the query. Finally, we output the result to the screen.

Note that rowCount() may not always return the correct number of rows, depending on the database driver being used. In some cases, it may return -1 or 0 instead of the actual row count. Therefore, it is recommended to test the behavior of rowCount() with your specific database driver and query before relying on it for production code.