CODEWITHSUNDEEP
phppdoresultsetassociative
user7461846
user7461846

Reputation:

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

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

Upvotes: 1

Views: 3257

Answers (3)

Death-is-the-real-truth
Death-is-the-real-truth

Reputation: 72289

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

Upvotes: 3

mickmackusa
mickmackusa

Reputation: 47864

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.

Upvotes: 1

Guy Louzon
Guy Louzon

Reputation: 1203

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

Upvotes: -2

Related Questions