How to Count Database Rows Using PDO in PHP?

PDO Alternative for mysql_num_rows

As you transition your PHP codebase to PDO, you may encounter the need to count database rows. The mysql_num_rows function, commonly used with MySQLi extension, offers this functionality but is not available in PDO. Here's how you can achieve row counting using PDO:

Option 1 (Direct Query):

<code class="php">$res = $DB->query('SELECT COUNT(*) FROM table');
$num_rows = $res->fetchColumn();</code>

Here, PDO's query method is used to execute the SQL query. The fetchColumn() method on the resulting object retrieves the value of the first column, in this case, the row count.

Option 2 (Prepared Statement):

<code class="php">$res = $DB->prepare('SELECT COUNT(*) FROM table');
$res->execute();
$num_rows = $res->fetchColumn();</code>

This option involves preparing a statement before execution. While the result is similar to Option 1, the use of prepared statements provides enhanced security and performance benefits.

Checking for Data Existence:

The row count can also be used to verify if data exists:

<code class="php">$res = $DB->query('SELECT COUNT(*) FROM table');
$data_exists = ($res->fetchColumn() > 0) ? true : false;</code>

Applying to Your Code:

To incorporate the row count into your code, you can modify the conditional statement:

<code class="php">$res = $DB->query('SELECT COUNT(*) FROM table');
$message = ($res->fetchColumn() > 0) ? array('status' => 'ok') : array('status' => 'error');</code>

This approach allows you to continue counting rows in your PDO-based PHP applications effectively.

The above is the detailed content of How to Count Database Rows Using PDO in PHP?. For more information, please follow other related articles on the PHP Chinese website!