How do I loop through a MySQL query via PDO in PHP?

Question

I'm slowly moving all of my LAMP websites from mysql_ functions to PDO functions and I've hit my first brick wall. I don't know how to loop through results with a parameter. I am fine with the following:

foreach ($database->query("SELECT * FROM widgets") as $results) {    echo $results["widget_name"]; } 

However if I want to do something like this:

foreach ($database->query("SELECT * FROM widgets WHERE something='something else'") as $results) {    echo $results["widget_name"]; } 

Obviously the 'something else' will be dynamic.

Answer 1

Here is an example for using PDO to connect to a DB, to tell it to throw Exceptions instead of php errors (will help with your debugging), and using parameterised statements instead of substituting dynamic values into the query yourself (highly recommended):

// connect to PDO $pdo = new PDO("mysql:host=localhost;dbname=test", "user", "password");  // the following tells PDO we want it to throw Exceptions for every error. // this is far more useful than the default mode of throwing php errors $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  // prepare the statement. the placeholders allow PDO to handle substituting // the values, which also prevents SQL injection $stmt = $pdo->prepare("SELECT * FROM product WHERE productTypeId=:productTypeId AND brand=:brand");  // bind the parameters $stmt->bindValue(":productTypeId", 6); $stmt->bindValue(":brand", "Slurm");  // initialise an array for the results $products = array(); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {     $products[] = $row; } 

Answer 2

According to the PHP documentation is says you should be able to to do the following:

$sql = "SELECT * FROM widgets WHERE something='something else'"; foreach ($database->query($sql) as $row) {    echo $row["widget_name"]; }