Select (with record count)

It is with the select statement that we get the greatest difference between the amount of code needed with mySQLi and the amount needed with PDO. It is also the statement where the actual code you need can vary depending on whether you need to know just how many records that the query returns.

Here's the mySQLi version:

$query = "SELECT name, address FROM usertable WHERE age = ?";
if ($stmt = $mysqli->prepare($query);) {
$stmt->bind_param('i', $age);
$numrows = $stmt->num_rows;
$stmt->bind_result($name, $address);
while($stmt->fetch() {
echo $name.', '.$address;

With this code we need to use store_result() to retrieve the entire resultset in order to be able to retrieve all the results at once in order to give us access to the num_rows property that contains a count of the number of rows in the resultset. We also need to use free_result() to free up the space again once we finish processing the results. We also need to use a bind_result call to bind the fields returned to the PHP variables we want to extract them into.

Here's the PDO version of the same thing:

$stmt = $db->prepare("SELECT name, address FROM usertable WHERE age = :age");
$stmt->bindValue(':age', $age, PDO::PARAM_INT);
$rows = $stmt->fetchAll();
$numrows = count($rows);
foreach($rows as $row) {
echo $row['name'].', '.$row['address'];

In this case the fetchAll returns all of the results at once as a multi-dimensional array. The rest of the code after that is just ordinary PHP extracting information out of an array.

Note one difference in te bove code that is not dependent on whether you use mySQLi or PDO is whether you set up the select query first as shown in the mySQLi example or hard code it in the prepare statement as shown in the PDO example. Both of these ways work with both mySQLi and PDO.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow