Pdo Fetch One Column from Table into 1-Dimensional Array

PDO fetch one column from table into 1-dimensional array

<?php
$sql = "SELECT `ingredient_name` FROM `ingredients`";
$ingredients = $pdo->query($sql)->fetchAll(PDO::FETCH_COLUMN);

PDO fetchAll array to one dimensional

Take a look at example 2 in the manual. In your first query you can use:

$staff = $select->fetchAll(PDO::FETCH_COLUMN, 0);

And your second array will have the same form as the first array.

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];
}

PDO 'fetchAll' return rows with an extra array

fetchAll will return a two-dimensional array.

• The first dimension is each row.
• The second dimension is each field (key/value pair) within that row.

$resultset[0]['fieldname'] will return the value of fieldname in the first row of the result set.

You're expecting fetchAll to return a single row whereas that's what fetch does.

So to keep it simple:

• If your result set will always only return one row, use fetch.
• If your result set could return multiple (or zero) rows, use fetchAll and then for each to iterate through each row.

MySQL pdo select single column with limit but return one row

fetch retrieves one row, or it returns FALSE if there is no row to fetch. (We could do the processing in a loop, and fetch each "next row", until fetch returns FALSE.

  $query->execute();
  while ( $row = $query->fetch(PDO::FETCH_ASSOC) ) {
     echo $row['title'];
  }

---

There's only one column in the resultset. It seems like we are confused about fetch and fetchAll.

http://php.net/manual/en/pdostatement.fetch.php

http://php.net/manual/en/pdostatement.fetchall.php

---

If we use fetchAll to retrieve all of the rows, we get an array back, each element in the array is a row from the resultset.

  $query->execute();
  if( $rs = $query->fetchAll(PDO::FETCH_ASSOC) ) {
     foreach($rs as $row) {
        echo $row['title'];
     }
  }

PDO retrieves two-dimensional array instead of one-dimensional array

PDO retrieves exactly what you ask it for.

To get a one-dimensional array you have to use PDO::FETCH_COLUMN instead of PDO::FETCH_ASSOC

return $dbh->query("SELECT $column FROM $table" )->fetchAll(PDO::FETCH_COLUMN);

Note that your query is critically prone to SQL injection. PDO shouldn't be used that way. Do not try to automate SQL. It's already perfectly automated. Use a literal query like SELECT foo,bar FROM baz WHERE id=1 instead. There is not that much writing you're probably afraid of, and there will be not that much use of this function in a real world code as you probably hope for.

OR

use a ready-made query builder, such as Doctrine.

I think PDO fetch() and fetchColumn() block each other

It is proper behavior, fetchColumn fetches one column. You cant fetch it twice. You can use:

/* fetches row from the result, if theres no row in the result, fetch returns false */
$row = $stmt->fetch(); 

/* compare if $row is true (in PHP, "boolean value" of unempty array is true) */
if ( $row ) { 
    print_r($row);
} else {
    echo '<div class="error-message">Verify Your Credentials</div>';
} 

---

Related Topics