How to Use Pdo to Fetch a Results Array in PHP

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

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

MYSQL/PHP: Create an Array using FetchAll with PDO

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 />';
    }

PDO fetch array as row

Ok so ill put this here for users that have this issue in the future,
1st of all my mistake was that I did not make a foreach loop as mentioned above by the others.
secondly my SCR statement is wrong here is the correct part of the code that will display the base64 images

    function displayimage()
    {
    try {
      //  define('SITE_ROOT', dirname(__FILE__));
        require SITE_ROOT . '\dbconnect.php';
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $result = $conn->prepare("SELECT * FROM images");
        $result->execute(); 
        $rows = $result->fetchAll();    
        foreach($rows as $row):
        echo '<img height="300" width="300"  src="data:image/png;base64, ' . $row[2] . '">';
        endforeach;

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

PDO equivalent of mysql_fetch_array

I think you are looking for:

while($row = $stmt->fetch(/* PDO::FETCH_ASSOC */)) {
    // do loop stuff
}

PDO::fetchAll() returns an associative array of all of the query results (a 2-D array). This is not recommended for large result sets according to the PHP docs. PDO::fetch() returns just one row from a result set and mimics mysql_fetch_array(). See http://php.net/manual/en/function.mysql-fetch-array.php for more details.

PDO Results to array

I use the following code:

// In case an error occured during statement execution, throw an exception
if (!$stmt->execute()) {
    // Error handling with $stmt->errorInfo()
}

// Fetch all results
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

It doesn't need a foreach loop to process the data afterwards.

PDO Fetch Array like MySQLi

fetchAll() is not the same as fetch_array().

You want fetch() to get one row, not fetchAll() which gets ALL rows.

$result = $query->fetch(PDO::FETCH_ASSOC);

PHP PDO Fetch as multidimensional array with first column as key, and second & third as key-value pair

Basically you're stuck doing it programmatically, but you could do it with a function in the fetchAll call; using FETCH_GROUP to do the grouping by the first column and using the function to return the appropriate array from the second and third columns.

$result = $stmt->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_FUNC,
                          function ($col2, $col3) { 
                              return array($col2 => $col3); 
                          });

Note this returns an array with an extra level:

Array
(
    [column1 value1] => Array
        (
            [0] => Array
                (
                    [column2 value1] => 360
                )
        )
    [column1 value2] => Array
        (
            [0] => Array
                (
                    [column2 value1] => 240
                )
            [1] => Array
                (
                    [column2 value2] => 10
                )
        )
)

You can remove the extra level through a combination of array_map and array_reduce:

$result = array_map(function ($arr) {
    return array_reduce($arr, function ($c, $a) {
        $c[key($a)] = reset($a);
        return $c;
    }, []);
}, $result);

Output:

Array
(
    [column1 value1] => Array
        (
            [column2 value1] => 360
        )
    [column1 value2] => Array
        (
            [column2 value1] => 240
            [column2 value2] => 10
        )
)

However it is probably simpler just to loop through the array:

$result = $stmt->fetchAll(PDO::FETCH_NUM);
$out = array();
foreach ($result as $array) {
    $out[$array[0]][$array[1]] = $array[2];
}

Create single array from MySQL with PHP/PDO

What you need is PDO::FETCH_COLUMN mode:

$sql = "SELECT name FROM " . $this->table_name . "  ORDER BY id";
$prep_state = $this->db_conn->prepare($sql);
$prep_state->execute();

// 0 indicates first column of result
$names = $prep_state->fetchAll(PDO::FETCH_COLUMN, 0); 
print_r($names);
// then do what you want.

---

Related Topics