What is the simplest way to return a ROW as well as loop through the ROWS with PDO?

Question

If I am doing an old query to return a row I would do something like this:

$sql = 'SELECT id FROM table WHERE email="' . mysql_real_escape_string($email) . '" LIMIT 1';
$res = mysql_query($sql);
$row = mysql_fetch_array($res);

echo $row['id'];

How do I do that with a Prepared Statement? I can get this far...

 $stmt = $dbh->prepare("SELECT id FROM table WHERE email = ? LIMIT 1");
 if ($stmt->execute(array($_POST['email']))) {

    // what goes in here to pull out this one row?       

 }

Secondly, if I have multiple rows I would do it like this:

$sql = 'SELECT id FROM table WHERE email="' . mysql_real_escape_string($email) . '" ';
$res = mysql_query($sql);
while($row = mysql_fetch_array($res)) {

   echo $row['id'];

}

Likewise, with PDO I get to a similar place...

 $stmt = $dbh->prepare("SELECT id FROM table WHERE email = ? ");
 if ($stmt->execute(array($_POST['email']))) {

    // what goes in here to loop through the rows??
    //
    // something like this...?
    //
    while ($row = $stmt->fetch()) {
       echo $row['id'];
    }       

 }

Answer 1

Here is what I use:

For more info on PDO see: http://php.net/manual/en/book.pdo.php

How to use:

    //create connection
$connection = new Connection($settings,true);
$conn = $connection->conn;

    //query
   $sql = "SELECT StateName as State, StateAbbr as Abb FROM State";
    $values = array(":Abbr" => "AL");
    $query = new Query($conn);
    $testArr = $query->getArrayFromQuery($sql, $values);

CONNECTION: (Connection.php)

class Connection
{

    public $conn = null;

    /**
     * Creates PDO Database Connection
     *
     * @param array $params             Connection Data (host,database,username,password)
     * @param bool $useErrorReporting   True to Show Errors (optional)
     * @sets Database Connection
     * @access public
     */
    public function __construct($params,$useErrorReporting=false) 
    {
        try
        {   
            $host = "";
            $database = "";
            $username = "";
            $password = "";

            if(isset($params) && is_array($params))
            {
                $host = $params['database_connection']['host'];
                $database = $params['database_connection']['database'];
                $username = $params['database_connection']['username'];
                $password = $params['database_connection']['password'];

                $dsn = 'mysql:dbname='.$database.';host='.$host;
                $dbh = new PDO($dsn, $username, $password, array(PDO::ATTR_PERSISTENT => true));

                //display errors if true
                if($useErrorReporting)
                {
                    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                }
            }
            else
            {
                $dbh = null;
            }

        }
        catch (PDOException $e)
        {
            throw new Exception('Connection Failed: '.$e->getMessage());
        }

        $this->conn = $dbh;
    }

QUERY: Query.php

Class Query
{
    private $conn = null;

    /**
     * sets query properties
     *
     * @param object $conn      pdo connection object
     * @return void
     * @access public
     */
    public function __construct($conn)
    {
        $this->conn = $conn;
    }

    /**
     * getArrayFromQuery
     * gets array from given query
     *
     * @param string $sql       sql statement
     * @param array $values     array values to replace (":value" => 2)
     * @return array
     * @access public
     */
    public function getArrayFromQuery($sql, $values)
    {
        $retValue = array();
        $conn = $this->conn;
        $statement = "";

        try
        {
            //prepare sql statement
            $statement = $conn->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

            //add values
            if(isset($values) && is_array($values))
            {
                $statement->execute($values);
            }

            //set return array to result array
            $retValue = $statement->fetchAll();

        }
        catch (PDOException $e)
        {
            throw new Exception("PDO Query Error: ".$e->getMessage());
        }
        catch(Exception $e)
        {
            throw new Exception("Process Query Error: ". $e->getMessage());
        }

        return $retValue;
    }

}

Answer 2

Assuming you're connected to the DB and $dbh is your PDO object.

<?php

$email = '[email protected]';

$stmt = $dbh->prepare("SELECT `id` FROM `table` WHERE `email` = ?");

$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->bindParam(1, $email, PDO::PARAM_STR);

$stmt->execute();


/* One row. */
$result = $stmt->fetch();

if ($result !== FALSE) {
    $stmt->closeCursor();

    echo $result['id'];
}


/* Multiple rows. */
$result = $stmt->fetchAll();

if ($result !== FALSE) {
    foreach ($result as $row) {
        echo $row['id'];
    }
}

?>