PHP | PDO get all dynamic column name and Its value

Question

I have a stored procedure. I'm calling through PDO. when I run the procedure in Phpmyadmin. It return output like below.

Problem

I don't know how many columns will stored procedure returns. I need column name and its Value name.

My Attempt here

$sql = "call surveyreport (28);";   
$stmt = $pdo->query($sql);
$a=array();
do {
$rows = $stmt->fetchAll(PDO::FETCH_NUM);
if ($rows) {
    array_push($a, $rows);
}
} while ($stmt->nextRowset());

it returns

Array ( [0] => Array ( [0] => Array ( [0] => 1068 [1] => SATHIYA MOORTHI [2] => Yes [3] => ) [1] => Array ( [0] => 5000 [1] => Ben Praveen [2] => Yes [3] => ) ) )

How Can I get Column name? and Its value. Thank You.

If you fetch associative instead of numeric, you'd have the indexes as the column-names. I'd also like to say that having column names as `Is thisuseful ?` is probably going to cause more headache than good. `usefulness` could be a better name (without any long names, or that has spaces and signs in it) — Qirel, Apr 19 '17 at 08:01
`PDO::FETCH_NUM` => `PDO::FETCH_ASSOC`. And the column-names should be simplified, like I said, have a read at http://stackoverflow.com/questions/7899200/is-there-a-naming-convention-for-mysql — Qirel, Apr 19 '17 at 08:05
@Qirel, Thank You. I got array with all the column name and Value. How Can I split data from that array. Array looks like below `Array ( [0] => Array ( [0] => Array ( [EmpId] => 1068 [Name] => SATHIYA MOORTHI [Is thisuseful ?] => Yes [what did you learn from this?] => ) [1] => Array ( [EmpId] => 5000 [Name] => Ben Praveen [Is thisuseful ?] => Yes [what did you learn from this?] => ) ) )` — Maria Jeysingh Anbu, Apr 19 '17 at 08:08
Depends on what you want to do with it and how you need it to be structured. Perhaps by using `extract()`? But that's another question really. — Qirel, Apr 19 '17 at 08:11

score 1 · Answer 1 · answered Feb 15 '18 at 11:44

Assuming:

$sql = "CALL surveyreport (28);";   
$stmt = $pdo->query($sql);
$survery=array();
do {
    if($rows = $stmt->fetchAll(PDO::FETCH_ASSOC)){
        array_push($a, $rows);
    }
} while ($stmt->nextRowset());

...will generate:

$a=[
    [
        [
         'EmpId'=>1068,
         'Name'=>'SATHIYA MOORTHI',
         'Is thisuseful ?'=>'Yes',
         'what did you learn from this?'=>''
        ]
    ],
    [
        [
         'EmpId'=>5000,
         'Name'=>'Ben Praveen',
         'Is thisuseful ?'=>'Yes',
         'what did you learn from this?'=>''
        ]
    ]
];

...you can build a simple table with this: (Demo)

echo "<table border=\"1\" cellpadding=\"4px\" style=\"white-space:nowrap;\">";
    echo "<tr><th>",implode('</th><th>',array_keys(current(current($a)))),"</th></tr>";
    foreach($a as $surveyreports){
        foreach($surveyreports as $rows){
            echo "<tr><td>",implode('</td><td>',$rows),"</td></tr>";
        }
    }
echo "<table>";

Outputting this:

<table border="1" cellpadding="4px" style="white-space:nowrap;">
    <tr>
        <th>EmpId</th><th>Name</th><th>Is thisuseful ?</th><th>what did you learn from this?</th>
    </tr>
    <tr>
        <td>1068</td><td>SATHIYA MOORTHI</td><td>Yes</td><td></td>
    </tr>
    <tr>
        <td>5000</td><td>Ben Praveen</td><td>Yes</td><td></td>
    </tr>
<table>

Rendering like this:

PHP | PDO get all dynamic column name and Its value

1 Answers1