PDO: Count number of rows in a MySQL table.

This is a tutorial on how to count the number of rows in a MySQL table using PHP’s PDO object. For this example, we will assume that we want to count the number of records in a MySQL table called “users”. To do this, we will prepare an SQL COUNT statement and execute it using PDO.

Let’s jump right into an example:

//Instantiate the PDO object and connect to MySQL.
$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=my_database',
    'username',
    'password'
);

//The COUNT SQL statement that we will use.
$sql = "SELECT COUNT(*) AS num FROM users";

//Prepare the COUNT SQL statement.
$stmt = $pdo->prepare($sql);

//Execute the COUNT statement.
$stmt->execute();

//Fetch the row that MySQL returned.
$row = $stmt->fetch(PDO::FETCH_ASSOC);

//The $row array will contain "num". Print it out.
echo $row['num'] . ' users exist.';

In the code above, we:

  1. Connected to MySQL using the PDO object.
  2. Created our SQL statement. In this case, we are using MySQL’s COUNT function to count the number of rows in a table called “users”. We also used the AS keyword to create an alias called num, which will contain the result of COUNT.
  3. After that, we prepared our PDO statement and executed it.
  4. Finally, we fetched the row as an associative array and printed out the “num” element.

Essentially, all we are doing here is selecting a row from MySQL using the PDO object.

Note that another approach is to select all of the rows from the table and then use PHP’s count function to count the number of elements in the array that was returned.

However, this can be resource intensive and wasteful if you are not actually using the data in question. If you do not intend on using the data and are merely looking for a count figure, then you should use the approach above.