MYSQL/PHP: Create an Array using FetchAll with PDO

Question

I am new to PDO and having trouble placing the results of a fetch into an array.

The following code succeeds in querying the database. However, I would like to place the results into an array so that I can then work with the elements of the array individually. I am not sure what exactly $result below is. Is it already an array? Or what is it. I think I need to use fetchAll to get the array. If so, would appreciate proper code to do this. Or if $results below is already an array, then what is the difference between that and what I would get with fetchAll?

//USE PDO TO CONNECT TO DBASE
$hostdb = "my host";
$namedb = "mydb";
$passdb = "bypass";
$userdb = "myusername";

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define and perform the SQL SELECT query
  $sql = "SELECT * FROM `comments` WHERE `userid` IN(118)";
  $result = $conn->query($sql); // THIS WORKS
// $result = $sql->fetchAll(PDO::FETCH_OBJ);  //THIS DOES NOT WORK
   if($result !== false) {

    // Parse the result set
    foreach($result as $row) {
      echo $row['id']. ' - '. $row['name']. ' - '. $row['category']. ' - '. $row['link']. '<br />';
    }
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}

It returns an object but you can iterate over either an object or an array.For fetchall you need prepare with execute, RTFM.Didnt downvote you BTW — Mihai, Feb 14 '17 at 16:22
fetchAll will return the complete resultset into a variable. That variable is an array, one occurance for each row in the resultset. Depending on the parameter you use in fetchAll it will be an array of row arrays or and array of row objects — RiggsFolly, Feb 14 '17 at 16:23
\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 — bxN5, Feb 14 '17 at 16:24
Yes, I think I want fetch all. Because I want to work with this array outside the loop. I want to do some math on values in the array and then store those to a new variable in the array. However, my one liner to fetch all above does not do run: $result = $sql->fetchAll(PDO::FETCH_OBJ); //THIS DOES NOT WORK — zztop, Feb 14 '17 at 16:25
Possible duplicate of [php PDO fetchAll() - while not working, foreach works](http://stackoverflow.com/questions/17729301/php-pdo-fetchall-while-not-working-foreach-works) — ford prefect, Feb 14 '17 at 16:32
Before asking this question, I actually looked at the other one that is supposedly a duplicate--probably same google search--but that question is about using a while loop. Didn't help me with my code. I'm trying to get foreach to work while that question starts with foreach working. — zztop, Feb 14 '17 at 16:54
`$sql->fetchAll()`? PHP is not JavaScript, strings are not objects! — Álvaro González, Feb 14 '17 at 17:42

RiggsFolly · Accepted Answer · 2017-02-14T17:19:45.073

1

fetchAll will return the complete resultset into a variable. That variable is an array, one occurance for each row in the resultset. Depending on the parameter you use in fetchAll it will be an array of row arrays or and array of row objects.

But you have a few little error in your syntax

// Define and perform the SQL SELECT query
$sql = "SELECT * FROM `comments` WHERE `userid` IN(118)";

$result = $conn->query($sql); 
if($result !== false) {

    // use $result here as that you PDO::Statement object
    $allRows = $result->fetchAll(PDO::FETCH_OBJ); 

    // Parse the result set
    foreach($allRows as $row) {
        // also amend here to address the contents of the allRows i.e. $row as objects
      echo $row->id. ' - '. $row->name. ' - '. $row->category. ' - '. $row->link. '<br />';
    }

edited Feb 14 '17 at 17:19

answered Feb 14 '17 at 16:30

RiggsFolly

93,638
21
103
149

This makes sense. And it does not throw error. However, $allRows seems to be empty, both iterating as in your code or using print_r . In the original code, it echoed out about 10 rows so it is not empty. – zztop Feb 14 '17 at 16:50
I had a typo '$row>name' should be `$row->name` is it as simple as that? – RiggsFolly Feb 14 '17 at 17:14
For the record, `fetchAll()` should not be used when the code immediately iterates the result set in the same layer (IIRC, this causes unnecessary memory usage). Instead, traverse the result set object (`$result`) directly with `foreach()` and access the rows' data using array syntax. – mickmackusa Feb 26 '21 at 13:14

Tunçer Damat · Answer 2 · 2017-02-14T17:37:09.530

First of all, to be sure what $result is use var_dump($result) since PDO::query method returns a PDOStatement object. Further, in your case it is not an array, it is a PDOStatement object. Refer to http://php.net/manual/en/pdo.query.php. Afterwards, I would suggest to use PDO::prepare() and PDO::execute() methods to run your SQL query since it is performance friendly, especially if you are going to run it multiple times and safer by preventing you from SQL injections. Refer to http://php.net/manual/en/pdo.prepare.php. And after all, PDO::fetchAll() will return an array of your result set which is what you already want. Refer to PHP PDO::fetchAll() manual. So what I would do is:

$sql = "SELECT * FROM `comments` WHERE `userid` IN(118)";

$statement = $conn->prepare($sql);
if($statement->execute())
{
    $results = $statement->fetchAll('Your prefered fetch style');

    foreach($results as $result) {
      ...
    }
}

MYSQL/PHP: Create an Array using FetchAll with PDO

2 Answers2