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

Tags: pdo mysql php

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.


If you like the foreach syntax, you can use the following class:

// Wrap a PDOStatement to iterate through all result rows. Uses a 
// local cache to allow rewinding.
class PDOStatementIterator implements Iterator

    public function __construct($stmt)
        $this->cache = array();
        $this->stmt = $stmt;

    public function rewind()

    public function valid()
        return (FALSE !== $this->next);

    public function current()
        return $this->next[1];

    public function key()
        return $this->next[0];

    public function next()
        // Try to get the next element in our data cache.
        $this->next = each($this->cache);

        // Past the end of the data cache
        if (FALSE === $this->next)
            // Fetch the next row of data
            $row = $this->stmt->fetch(PDO::FETCH_ASSOC);

            // Fetch successful
            if ($row)
                // Add row to data cache
                $this->cache[] = $row;

            $this->next = each($this->cache);


Then to use it:

foreach(new PDOStatementIterator($stmt) as $col => $val)
By : John K

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):

// $attrs is optional, this demonstrates using persistent connections,
// the equivalent of mysql_pconnect
$attrs = array(PDO::ATTR_PERSISTENT => true);

// connect to PDO
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "password", $attrs);

// 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

// prepare the statement. the place holders 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();
if ($stmt->execute()) {
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $products[] = $row;

// set PDO to null in order to close the connection
$pdo = null;

If you rely on CSS or JavaScript to prevent a user from editing part of a form then this can easily by circumvented by disabling CSS or JavaScript.

A better solution might be to present the non-editable information outside of the form for non-premium members, but include the relevant form fields for premium members.

By : Ian Oxley

This video can help you solving your question :)
By: admin