CODEWITHSUNDEEP
phpmysqlloops
walimbeakshay
walimbeakshay

Reputation: 97

Traversing from a mysql table using two loops

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.

Upvotes: 0

Views: 76

Answers (2)

user2560539
user2560539

Reputation:

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

Upvotes: 2

Amr Berag
Amr Berag

Reputation: 1110

Try making naming rule for your column so you can get the column name like this:

 $column_name = 'column'  .'-' .$j

and this will work with the current column names without the hyphen like this:

 $columns[$i][$j]  = 'column' . $j

In other words you just append the value of $j to the word 'column'.

-Edit: I think you can even calculate the column name in the front end of your application.

Upvotes: 0

Related Questions