0

I'm new using json, mysql and php. I want to retrieve data from my mysql database into json and send it to highcharts.

I have some data that is stored in different tables.

I want to acheive this result:

[[name1,value1],[name2,value2],...]

I'm using this code in my data.php file:

$result = mysql_query("SELECT name, value FROM table1, table2");


$rows = array();
while($r = mysql_fetch_array($result)) {
$row[0] = $r[0];
$row[1] = $r[1];
array_push($rows,$row);
}

 print json_encode($rows, JSON_NUMERIC_CHECK);

The output of this code gives this results:

[[name1,value1],[name2,value2],[name1,value2],[name2,value1],... ]  

so instead of having each name with its value I get each name with all the values.

How can I fix this?

EDIT:

sorry for the ambiguity here a summury:

     table 1            table 2 
name    other data  value   other data 
name 1  data 1      value 1    data 1
name 2  data 2      value 2    data 2
name 3  data 3      value 3    data 3
name 4  data 4      value 4    data 4
name 5  data 5      value 5    data 5
name 6  data 6      value 6    data 6
name 7  data 7      value 7    data 7
name 8  data 8      value 8    data 8
name 9  data 9      value 9    data 9
name 10 data 10     value 10    data 10
name 11 data 11     value 11    data 11
name 12 data 12     value 12    data 12
name 13 data 13     value 13    data 13


obtained json           desired json    

name 1  value 1         name 1  value 1
name 1  value 2         name 2  value 2
name 1  value 3         name 3  value 3
name 1  value 4         name 4  value 4
name 1  value 5         name 5  value 5
name 1  value 6         name 6  value 6
name 1  value 7         name 7  value 7
name 1  value 8         name 8  value 8
name 1  value 9         name 9  value 9
name 1  value 10        name 10 value 10
name 1  value 11        name 11 value 11
name 1  value 12        name 12 value 12
name 1  value 13        name 13 value 13
name 2  value 1             
name 2  value 2             
name 2  value 3             
name 2  value 4             
name 2  value 5             
  .       .
  .       .
name 13 value 13    
4
  • Can you show us an example dataset? Commented Oct 10, 2014 at 0:51
  • 1
    Notice: There is no more support for mysql_* functions, they are officially deprecated, no longer maintained and will be removed in the future. You should update your code with PDO or MySQLi to ensure the functionality of your project in the future. Commented Oct 10, 2014 at 0:51
  • 1
    ok...i'll try to update it for mysqli later!! Commented Oct 10, 2014 at 0:54
  • Your question is ambiguous, could you provide SQL schemas and a sample SQL return? Commented Oct 10, 2014 at 1:01

1 Answer 1

2

I'm going with the assumption your code above is correct, the way you typed it in production. Then the issue probably lies with your sql query (assumption is that name is from table1 and value is from table2)

SELECT name, value FROM table1, table2

In the above instance, you are not joining them at all. Ideally, when you're outputting two tables, you are joining them at some point:

e.g.

SELECT name , value FROM table1 , table2 WHERE table1.col1 = table2.col2
Sign up to request clarification or add additional context in comments.

5 Comments

yes sir; your assumption is correct 'name is from table1 and value is from table2'...i'm sorry if i couldn't be more clear. should i add ' WHERE table1.col1 = table2.col2 ' to get the joining ?
@RedOne - Yes, that's what you probably have to do.
it gave me an empty array sir, all i got is : []. $result = mysql_query("SELECT name, value FROM table1, table2 WHERE table1.col1=table2.col2"); i'm missing something!!
The join typically takes place where they have a common identifier between them. e.g. if table1 has a main_id that references table2's column called main_id, that's what you should match. It's difficult to give you a complete answer without seeing your schema.
i Got it work thank you.yes in fact the two tables has an id that relates between them so $result = mysql_query("SELECT name, value FROM table1, table2 WHERE table1.id=table2.id"); did the trick....thank you again!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.