使用两个循环遍历mysql表

Question

I have following table in MySQL

|Column1|Column2|Column3|
-------------------------
|Data1  |Data2  |Data3  |
|Data4  |Data5  |Data6  |
|Data7  |Data8  |Data9  |
|Data10 |Data11 |Data12 |
-------------------------

Now in this table, I am fetching column names as well all the 12 cells using PHP

here is the loop that I am working on.

    for($i=0, $j=0; $i<$loopMax; $i++,$j++){//i run rows, j run columns
    if($j>=$column_count){
             $j=0;
    }
    $columns[$i][$j] = mysql_result($query_exec, $j, 'COLUMN_NAME');
    $response[$i][$columns[$i][$j]] = @$result[$j];
    }
     print_r($response);

$maxLoop is the value of a number of columns multiplied by the number of rows. that way I get the total number of cells I need to fetch. I have to send this response in a json string so getting this cell values along with its respective column names is important because that's what is going to help the UI end recognize where to put which value. there is no primary key to the table.

What I want to do is, fetch each of this cell value and the column name it belongs to and put it in an array. And send that array as jason string.

Answer 1

In case you have MySQL version 5.7.8 and above installed on your server, then you could use native JSON functions in your query.

SELECT JSON_OBJECT('Column1', `Column1`, 'Column2', `Column2`, 'Column3', `Column3`);

The result would be something like...

{ "Column1":"Data1", "Column2":"Data2", "Column3":"Data3" },
...
...
{ "Column1":"Data10", "Column2":"Data11", "Column3":"Data12" }