Wednesday, February 1, 2012

PHP PDO Count Results - MySQL

Many PHP developers are seeking a solution in counting number of returning rows from a result set using SELECT statement. The PDOStatement::rowCount( void ) function only works for DELETE, INSERT and/or UPDATE statements by counting affected rows. So that's out.

To help you out there are two solutions I found to be most efficient (if you found other ones please post them so we can all learn from each other and continue developing efficient applications).

Method #1:
Create a database handle:
$dbh = new PDO("mysql:host=localhost;dbname=yourdb', 'username', 'password');
Create SQL query to retrieve records from your database:
$query = "SELECT * FROM table1";
Prepare your query for execution:
$stm = $dbh->prepare($query);
Execute the statement:
$stm->execute();
Get all records into one variable (fetchAll() returns an array):
$result = $stm->fetchAll();
$result variable is an array which holds other data arrays for each row, so just count them:
$number_of_rows = count($result);
Print result or whatever you want to do with it:
echo 'Number of rows: ' . $number_of_rows;

After some research I read that method 1 may break when working with large data sets, I personally haven't had a problem yet.

Method #2:
This method to count number of rows in a result set also works but this method requires two database requests. First (the example below) request is used to see if any results are returned and second time you run the same request to database without the use of COUNT() function.

Create database handle:
$dbh = new PDO("mysql:host=localhost;dbname=yourdb', 'username', 'password');
Create SQL query to retrieve records from your database but now use COUNT() function only:
$query = "SELECT COUNT(*) AS totalRows FROM table1";
Prepare your query for execution:
$stm = $dbh->prepare($query);
Execute the statement:
$stm->execute();
Save result into a variable:
$result = $stm->fetchColumn();
Display the value:
echo 'Number of rows: ' . $result;

No comments:

Post a Comment