How can I use PDO to fetch a results array in PHP?

Question

I'm just editing my search script after reading up on SQL injection attacks. I'm trying to get the same functionality out of my script using PDO instead of a regular MySQL connection. So I've been reading other posts about PDO, but I am unsure. Will these two scripts give the same functionality?

With PDO:

$pdo = new PDO('mysql:host=$host; dbname=$database;', $user, $pass);
$stmt = $pdo->prepare('SELECT * FROM auction WHERE name = :name');
$stmt->bindParam(':name', $_GET['searchdivebay']);
$stmt->execute(array(':name' => $name);

With regular MySQL:

$dbhost = @mysql_connect($host, $user, $pass) or die('Unable to connect to server');

@mysql_select_db('divebay') or die('Unable to select database');
$search = $_GET['searchdivebay'];
$query = trim($search);

$sql = "SELECT * FROM auction WHERE name LIKE '%" . $query . "%'";

if(!isset($query)){
    echo 'Your search was invalid';
    exit;
} //line 18

$result = mysql_query($trim);
$numrows = mysql_num_rows($result);
mysql_close($dbhost);

I go on with the regular example to use

while($i < $numrows){
    $row = mysql_fetch_array($result);

to create an array of matching results from the database. How do I do this with PDO?

Berti

Also, keep in mind that you are not using LIKE in the WHERE clause. I would do the following: $stmt = $pdo->prepare('SELECT * FROM auction WHERE name LIKE ?'); $stmt->bindValue(1, '%'. trim($_GET['searchdivebay']) .'%'); The difference between bindParam and bindValue is that bindParam will bind the variable while bindValue binds the actual vaule of the variable. – Cabin

Answer 1

Take a look at the PDOStatement.fetchAll method. You could also use fetch in an iterator pattern.

Code sample for fetchAll, from the PHP documentation:

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll(\PDO::FETCH_ASSOC);
print_r($result);

Results:

Array
(
    [0] => Array
        (
            [NAME] => pear
            [COLOUR] => green
        )

    [1] => Array
        (
            [NAME] => watermelon
            [COLOUR] => pink
        )
)

Modla

\PDO::FETCH_ASSOC and \PDO::FETCH_NUM allow you to define fetching mode. \PDO::FETCH_ASSOC will return only field => value array, whilst \PDO::FETCH_NUM return array with numerical keys only and \PDO::FETCH_BOTH will return result like in the answer. This constant should be passed to ->fetchAll() method in this case. – Kyne

What if I just wanted to output an array of them now? I just want to say let's output array [0] with the values ​​of name. How can that be done? – Geomancy

Answer 2

There are three ways to fetch multiple rows returned by a PDO statement.

The simplest one is just to iterate over the PDO statement itself:

$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// iterating over a statement
foreach($stmt as $row) {
    echo $row['name'];
}

Another one is to fetch rows using the fetch() method inside a familiar while statement:

$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// using while
while($row = $stmt->fetch()) {
    echo $row['name'];
}

But for the modern web application we should have our database interactions separated from the output and thus the most convenient method would be to fetch all rows at once using the fetchAll() method:

$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// fetching rows into array
$data = $stmt->fetchAll();

Or, if you need to preprocess some data first, use the while loop and collect the data into an array manually:

$result = [];
$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// using while
while($row = $stmt->fetch()) {
    $result[] = [
        'newname' => $row['oldname'],
        // etc
    ];
}

And then output them in a template:

<ul>
<?php foreach($data as $row): ?>
    <li><?=$row['name']?></li>
<?php endforeach ?>
</ul>

Note that PDO supports many sophisticated fetch modes, allowing fetchAll() to return data in many different formats.

Feast

Answer 3

First, a note about preventing the potential use/abuse of wildcard characters in your user input.

If your user supplies any % or _ characters in their $query text and you pass that directly to your prepared statement, the symbols will be interpreted as characters with special meaning. If you want to ensure that these characters are treated as literal characters within the SQL, then you'll need to escape them. For a portable solution (across different SQL dialects), manually set the escape character in the SQL and use it on potential wildcards and the escape character itself.

$query = 'bu%';
$escaped = str_replace(['!', '%', '_'], ['!!', '!%', '!_'], $query);
$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ? ESCAPE '!'");
$stmt->execute(["%$escaped%"]);

Play with my PHPize Demo.

I'd like to offer some additional insights on the available fetching techniques.

• foreach() on the $stmt variable:
  
  

  Using a foreach() has the advantages of not needing to explicitly call fetch() on each iteration AND it affords the ability to access the first level indexes along with the respective row data.
  
  The row payload of each iteration carries not only the associative elements of each row, but also indexed elements containing the same values (as if fetch(PDO::FETCH_BOTH) was being called).

  foreach ($stmt as $i => $row) {
      echo "$i: " . var_export($row, true) . "\n";
  }
  

  Then a single iteration's output will look like this:

  0: array('id' => 5, 0 => 5, 'name' => 'bu%', 1 => 'bu%')
  

  ---

• Calling fetch() in a while() loop:
  
  Because PDO's fetching modes can be applied to fetch() calls, there is better control over the available row structure. The modes are listed in the PHP manual.
  

  A while() loop will not directly offer access to each row's parent index; if you need that integer, then you'll need to declare and increment your counter variable manually.

  $i = 0;
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      echo "$i: " . var_export($row, true) . "\n";
      ++$i;
  }
  

  0: array('id' => 5, 'name' => 'bu%')
  

  I would not use while() with fetch() unless I needed data not available to the simpler foreach() style.

  • 💩 while ($row = $stmt->fetch(PDO::FETCH_BOTH)) {
  • 💩 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  • 💩 while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
  • 💩 while ($row = $stmt->fetch()) {
  • ✅ foreach ($stmt as $row) {

  ---

• fetchAll() (without a loop):

  This is appropriate/common to call when the payload will not be manually looped in the same function/method/layer. In other words, it doesn't make sense to call fetchAll() in the signature of a foreach() because that is double handling.

  • 💩 foreach ($stmt->fetchAll(PDO::FETCH_BOTH) as $row) {
  • 💩 foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
  • 💩 foreach ($stmt->fetchAll(PDO::FETCH_NUM) as $row) {
  • 💩 foreach ($stmt->fetchAll() as $row) {
  • ✅ foreach ($stmt as $row) {

  A clean, reusable model method will most commonly return fetchAll(/*preferred_mode*/);. Most fetchAll() modes can be found in the fetchAll page of PHP manual; all can be found on the PDO constants page.

  var_export($stmt->fetchAll(PDO::FETCH_ASSOC));
  

  array(0 => array('id' => 5, 'name' => 'bu%'))
  

Emmaemmalee