PDO - Select Statements

<?php
foreach($db->query('SELECT * FROM table') as $row) {
  echo $row['field1'].' '.$row['field2']; //etc...
}

The query() method returns a PDOStatement object. You can also fetch results this way:

<?php
$stmt = $db->query('SELECT * FROM table');
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo $row['field1'].' '.$row['field2']; //etc...
}

or

<?php
$stmt = $db->query('SELECT * FROM table');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Use $results...

If you have lots of parameters. This is how you can do it in PDO:

<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?");
$stmt->execute(array($id, $name));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
<php>
 
The **prepare** method sends the query to the server, and it's compiled with the '?' placeholders to be used as expected arguments.  The **execute** method sends the arguments to the server and runs the compiled statement.  Since the query and the dynamic parameters are sent separately, there is no way that any SQL that is in those parameters can be executed... so NO SQL INJECTION can occur!  This is a much better and safer solution than concatenating strings together.
 
**NOTE**:  When you bind parameters, do NOT put quotes around the placeholders.  It will cause strange SQL syntax errors, and quotes aren't needed as the type of the parameters are sent during **execute** so they are not needed to be known at the time of **prepare**.
 
There's a few other ways you can bind parameters as well. Instead of passing them as an array, which binds each parameter as a String type, you can use **bindValue** and specify the type for each parameter:
 
<code php>
<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?");
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Now if you have lots of parameters to bind, doesn't all those '?' characters make you dizzy and are hard to count? Well, in PDO you can use named placeholders instead of the '?':

<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

You can bind using execute with an array as well:

<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Note the use of PDO::FETCH_ASSOC in the fetch() and fetchAll() code above. This tells PDO to return the rows as an associative array with the field names as keys. Other fetch modes like PDO::FETCH_NUM returns the row as a numerical array. The default is to fetch with PDO::FETCH_BOTH which duplicates the data with both numerical and associative keys. It's recommended you specify one or the other so you don't have arrays that are double the size! PDO can also fetch objects with PDO::FETCH_OBJ, and can take existing classes with PDO::FETCH_CLASS. It can also bind into specific variables with PDO::FETCH_BOUND and using the bindColumn method. There are even more choices! Read about them all here: PDOStatement Fetch documentation.