Using Value of a Column as Index in Results Using Pdo

Using value of a column as index in results using PDO

Fetch as assoc

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

fetch_style

Controls the contents of the returned array as documented in PDOStatement::fetch(). Defaults to value of PDO::ATTR_DEFAULT_FETCH_MODE (which defaults to PDO::FETCH_BOTH)

To return an array consisting of all values of a single column from the result set, specify PDO::FETCH_COLUMN. You can specify which column you want with the column-index parameter.

To fetch only the unique values of a single column from the result set, bitwise-OR PDO::FETCH_COLUMN with PDO::FETCH_UNIQUE.

To return an associative array grouped by the values of a specified column, bitwise-OR PDO::FETCH_COLUMN with PDO::FETCH_GROUP.

That last bit is key. It doesn't seem to be completely documented (that I could find), but instead of PDO::FETCH_COLUMN, you can combine PDO::FETCH_ASSOC with PDO::FETCH_GROUP to achieve the desired result:

$PDOstmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP)

So, given the above data:

$stmt = $PDO_obj->prepare('select * from brands');
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP);
d($result);

Results in:

array (6) [
    '1' => array (1) [
        array (2) [
            'name' => string (10) "Solidfloor"
            'url' => string (10) "solidfloor"
        ]
    ]
    '2' => array (1) [
        array (2) [
            'name' => string (9) "Quickstep"
            'url' => string (9) "quickstep"
        ]
    ]
    '4' => array (1) [
        array (2) [
            'name' => string (10) "Cleanfloor"
            'url' => string (10) "cleanfloor"
        ]
    ]
    '5' => array (1) [
        array (2) [
            'name' => string (12) "Blue Dolphin"
            'url' => string (12) "blue-dolphin"
        ]
    ]
    '6' => array (1) [
        array (2) [
            'name' => string (5) "Krono"
            'url' => string (5) "krono"
        ]
    ]
    '8' => array (1) [
        array (2) [
            'name' => string (7) "Meister"
            'url' => string (7) "meister"
        ]
    ]
]

( d() is just a handy debugging function from the kint library, like var_dump() or print_r() )

Note that the column used to index the array will always be the first column in the results, so you can modify your select statement to choose which column you want. And note also that the indexed column will be stripped out of each row's array; to get around that, you can add the column twice to your select statement (i.e., select id, brands.* from brands, etc.).

There are more parameters documented here: http://php.net/manual/en/pdostatement.fetch.php , like PDO::FETCH_UNIQUE to make sure that each index is used only once.

PHP - PDO fetch resultset with column as index and column as value

I don't think there's anything built-in that will do that. You can do it by using a normal fetch() loop:

$results = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $results[$row['date']] = $row['price'];
}

PDO get result index by column name

There's no way to do it with any PDO flags.

Here' my suggested converting script, it's very small:

$result = [];
foreach($array as $arr) foreach($arr as $k=>$v) $result[$k][] = $v;

print_r($result);

PDO: row name as index of result array

Instead of using PDO::FETCH_BOTH as fetching mode, you'll probably want to use PDO::FETCH_ASSOC -- to fetch your data as an associative array.

Then you can access the members of the array by: $result['id'], $result['caption'], etc.

PDO supports several interesting fetching modes ; including

• associative-array : the keys of the array will be the column names, as returned from the database ; which is probably what you are used to
• objects ; including instances of the class you specify

To see what's possible, you might want to take a look at the different PDO::FETCH_* constants -- the list can be found here : Predefined Constants.

fetch resultset with one column as index and another as value in Yii2

You can do something like this:

$models = YourModel::find()->all();
$results = ArrayHelper::map($models, 'date', 'price');

or you can loop the AR results like:

$results = [];
foreach($models as $model) {
    $results[$model->date] = $model->price;
}

Assign value PHP variable as column name during fetching

Fetch the data as number-indexed array instead of associative one. Also, don't use extract, it's unsafe because it can overwrite existing variables.

$batch_Value = array();
if($num>0){
    while ($row = $stmt->fetch(PDO::FETCH_NUM)){
      $batch_Value[] = $row[0];
    }
}

Or if the desired column is not the first one, then you can find the value in the $row array (which is an associative one, see print_r($row); for details about it), with the following:

$batch_Value = array();
if($num>0){
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
      $batch_Value[] = $row[$batch_id];
    }
}

How to print column values with pdo and make a link based on row id

Assuming you want all records you use the pdo oject like this

$stm = $pdo->query('SELECT * FROM YorTable');$data = $stm->fetchAll(PDO::FETCH_ASSOC);

MySQL PDO fetchAll as array with integer index

The method you have is fine. Though if you don't need the ID, why would you need to query it?

<?php
    $sth = $dbh->prepare("SELECT ctg FROM ctgtable");
    $sth->execute();
    /* Fetch all of the values in form of a numeric array */
    $result = $sth->fetchAll(PDO::FETCH_ARRAY);
    var_dump($result);
?>

Less constraints on the MySQL leads to less processing time, which eventually leads to better results.

How to remove unnamed indices in PDO request?

PDOStatement::fetch and PDOStatement:fetchAll methods both have a $fetch_style parameter controlling how the returned result looks like. Its default value is PDO::FETCH_BOTH which returns an array indexed by both column names and their numbers. If you want just the names, use PDO::FETCH_ASSOC. See the documentation for other possible fetch styles.

---

Related Topics