Traversing from a mysql table using two loops

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.

So, vote to close as unclear and move on and let someone else figure it out. — Funk Forty Niner
– Funk Forty Niner, Commented Dec 28, 2017 at 18:29
@GordonLinoff edited my question, please read the last paragraph. — walimbeakshay
– walimbeakshay, Commented Dec 28, 2017 at 18:30
@Lawrence I swear if I had to do it, I would put a primary key in there and just fetch everything based on that. but I can not change it now. The project is live and I have to fetch this way only. — walimbeakshay
– walimbeakshay, Commented Dec 28, 2017 at 18:32

user2560539 · Accepted Answer · 2017-12-28 20:19:33Z

2

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

answered Dec 28, 2017 at 20:19

user2560539

Sign up to request clarification or add additional context in comments.

6 Comments

Amr Berag Over a year ago

Fantastic +1 and you deserve +1000

user2560539 Over a year ago

@AmrBerag i am glad this helped. This means that you probably have MySQL version that includes JSON functions. Take a better look to all of them dev.mysql.com/doc/refman/5.7/en/json-functions.html i think they will be most helpful for lot of things you might have in mind. I know they worked for me :)

Amr Berag Over a year ago

Can we do the opposite ?, I couldn't find it in docs, there is insert function but it's used in queries result as well.

user2560539 Over a year ago

@AmrBerag you mean if you can use JSON_OBJECT for INSERT yes. Example INSERT INTO mysqltable VALUES (JSON_OBJECT("TextSample", "Our mascot is a dolphin named \"Sakila\"."));

Amr Berag Over a year ago

I hope it will be added to MariaDB and that it's injection-proof

|

Amr Berag · Accepted Answer · 2017-12-28 19:18:01Z

0

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.

edited Dec 28, 2017 at 19:18

answered Dec 28, 2017 at 18:47

Amr Berag

1,1187 silver badges14 bronze badges

Collectives™ on Stack Overflow

Traversing from a mysql table using two loops

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related