Geek Answers Handbook

Iterate PDO Query Results

I want to run a query using PDO based on the data in the URL parameter (yes, I know this is prone to attacks, but its internal code for the utility).

$user = 'USER'; $pass = 'PASSWORD'; $dsn = 'mysql:dbname=PRODUCTS;host=HOST'; try { $productDB = new PDO($dsn, $user, $pass); $productDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { $msg = 'PDO ERROR' . $e->getFile() . ' L.' . $e->getLine() . ' : ' . $e->getMessage(); die($msg); } if(isset($_GET['cat'])) { $cat = $_GET['cat']; print "cat = $cat <br>"; $products = $productDB->prepare('SELECT * FROM products WHERE cat_id LIKE ?'); $products->execute(array($cat)); $rows = $products->rowCount(); print "$rows rows returned"; ?> <table border="1"> <tr> <td>product_id</td> <td>product_name</td> </tr> <?php foreach ($products->fetchAll() as $row) { $id = $row['product_id']; $product_name = $row['product_name']; print "<tr>"; print "<th scope=\"row\"><b>$id</b></th>"; print "<td> $product_name </td>"; print "<tr>"; } print "</table>"; } ?>

When I run this code, it prints the correct number of lines depending on the query, but does not populate the table.

I also tried replacing the prepare and execute lines with:

 $products = $productDB->query("SELECT * FROM products WHERE cat_id LIKE $cat");

Returns the correct row counter, but does not help.

And finally, I tried replacing the foreach string with something like:

 $rows = $products->fetchAll(); foreach ($rows as $row) {

My attempts to do the same with a fixed query all work fine, but I am having problems with how to put a variable element in a query and then iterate over the results.

+4
source share
2 answers

Try it (if I understood correctly):

 $products = $productDB->prepare("SELECT * FROM products WHERE cat_id LIKE :cat"); // Now, you can either do this : $products->bindParam('cat', '%'.$cat.'%'); $products->execute(); // or you can call execute with an associative array of your parameterized query. $products->execute(array('cat' => '%'.$cat.'%')); // Then, get all the results like this : $rows = $products->fetchAll(); foreach ($rows as $row) { // Do work here .. } // Or, like this : while ($row = $products->fetch(PDO::FETCH_ASSOC)) { // Do work here .. }

I prefer this time because you are not getting the whole request in one var, reducing the amount of memory needed.

I also recommend using the FETCH_ * parameter to get only the type of array you want.

By the way, you need to know that rowCount should not be used to count the rows returned by SELECT. As php.net said:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by this statement. However, this behavior is not guaranteed for all databases and should not rely on portable applications.

+2
source

You do nothing to save the result:

 $products->execute(array($cat));

need to go to the variable:

 $result = $products->execute(array($cat));

Then, instead of calling $products->fetchAll() use $results->fetchAll() :

 foreach ($result->fetchAll() as $row)

It’s easier for me to use the $query variable (for prepare , etc.) and then get the result as $result or $product . Makes the code a little easier to read.

+3
source

More articles:


All Articles