How to get column names and values from a single-row resultset with PDO?

Question

I need to get all column names and values in the row where id = $id.

Column names should be echoed as title; values should be echoed as story

Something like this:

function story($id) {
    global $db;
    $sql = "select *  from users where id = :aid limit 1";
    $st = $db->prepare($sql);
    $st -> execute([":aid" => $id]);
    $row = $st->fetch();
    $sql = // select all column names;
    $columns = // array - result of $sql
    $story = "";
    foreach ($columns as $el) {
        $i = array_search($el, $columns);
        $val = $row[$i];
        $story .=
        "<div class='title'>" . $el . "</div>\n" .
        "<div class='story'>" . $val . "</div>\n";
    }
    echo $story;
}

Why are you using `array_search()`? Build the resultset that you want to work with. I recommend passing `$db` as a function parameter instead of declaring `global`. — mickmackusa, Sep 24 '18 at 04:56
@mickmackusa, because I need that index to get appropriate element in `row` array; — , Sep 24 '18 at 04:57
@mickmackusa, sorry, can't understand, where should I write that? — , Sep 24 '18 at 05:01
If you just do: `$row = $st->fetch(PDO::FETCH_ASSOC);`, you will get the row as an associative array. No need for anything else. [Here's the manual](http://php.net/manual/en/pdostatement.fetch.php) — M. Eriksson, Sep 24 '18 at 05:01
I feel like you table design may be "questionable" if you are using column names as part of your output. What kind of schema are we looking at here? — mickmackusa, Sep 24 '18 at 05:02

Alive to die - Anant · Accepted Answer · 2020-02-20T04:30:45.180

No need to do array_search(), do like below:-

function story($id) {
    global $db;
    $sql = "select *  from users where id = :aid limit 1";
    $st = $db->prepare($sql);
    $st -> execute([":aid" => $id]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    if(count($row)>=1){
      foreach ($row as $column => $value) {
        echo "<div class='title'>" . $column . "</div>\n" .
        echo "<div class='story'>" . $value. "</div>\n";
      }
    }
}

mickmackusa · Answer 2 · 2020-01-07T06:20:22.390

I recommend that you pass the db connection as a function parameter instead of using a global declaration.

fetch(PDO::FETCH_ASSOC) returns a one-dimensional associative array or false.

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

Using capitalization on your sql keywords can improve readability.

I have removed the LIMIT clause, but if your id column is the PRIMARY KEY, the limit won't matter.

function story($db, $id) {
    $st = $db->prepare("SELECT * FROM users WHERE id = :aid");
    $st->execute([":aid" => $id]);
    if (!$row = $st->fetch(PDO::FETCH_ASSOC)) {
        echo "no row found";
    }
    foreach ($row as $key => $val) {
        echo "<div class='title'>" . $key . "</div>\n";
        echo "<div class='story'>" . $val . "</div>\n";
    }
}

I do have a suspicion that your database table design may not be optimal. Typically you'll want a stable column structure that allows your row count to increase. If your table is expanding both vertically and horizontally, I wonder what your requirements are.

`global` scoped variables are not recommended versus passing the connection as a parameter into the custom function. Also, the `count()` on the row is not necessary. If `id` is a primary/unique key (and it usually is in properly designed tables), then `LIMIT 1` is not necessary. — mickmackusa, Jan 07 '20 at 06:17

score -2 · Answer 3 · answered Sep 24 '18 at 05:01

You're approaching it a bit wrong... When a $sql returns with mysqli as an associative array, it returns the column name as the key...

hence:

$columns = // array - result of $sql as an assoc array
$story = "";
foreach ($columns as $key => value) {
    $story .=
    "<div class='title'>" . $key . "</div>\n" .
    "<div class='story'>" . $value . "</div>\n";
}

How to get column names and values from a single-row resultset with PDO?

3 Answers3