how to extract mysql data into json using php

Question

i have retrieved mysql data from one table in json using the following script

$table_first = 'abc';
$query = "SELECT * FROM $table_first";
$resouter = mysql_query($query, $conn);
$set = array();
$total_records = mysql_numrows($resouter);
if($total_records>= 1){
 while ($link = mysql_fetch_array($resouter, MYSQL_ASSOC)){
 $set[] = $link;
 }
}
echo json_encode($set);

how can i retrieved data from two other tables in which there is a foreign key of this table in both of those tables. OR simply how can i retrieved data from 3 mysql tables in php.

Answer 1

I believe the best way to go here is using a JOIN or just something like this:

$sql = "SELECT 
 tabl1.*, table2.*, tabl3.* FROM table1, table2, table3 
 WHERE 
 table1.fk1 = table2.id AND
 table1.fk2 = table2.id";
//Do the whole selection process...

If you make the queries separately, you'll be forcing 3 queries onto your database and will end in a performance hit that you dont need. So, the idea is load all the data from the DB using joins or similar that and then encode the results. Is faster and you'll leave the merging work to MySQL

Hope I can help

• hunter: thanks guy but this query retrieved the whole record at once and what i want to do is that it should print only the relevant records i.e for id 1 of table 1 there are 5 relevant records in table 2 and three records of table 3. First it should print id 1 of table 1 than all the 5 records of table 2 and of table 3.than for id 2 of table 1 it should be same like this
• hunter: so should i use three queries but how can i manage all the three queries and send to json_encode.

Answer 2

You can get all data firstly. Then merge the data array. Finally use json_encode to change the data format.

Answer 3

There is a foreign key of this table in both so you can use "join" to retrieve values from other tables.

Answer 4

Suppose that there are two tables as State(st_id,st_name) and City(ct_id,ct_name,state_id). Now, primary key are st_id & ct_id respectively of tables State & City.

Connection between this two table can be establish by joining State.st_id and City.state_id.

Now, coming to your problem to retrieve data from two table State & City, we can make sql query like following,

$sql="select s.*, c.* from State s, City c
 where s.st_id=c.state_id ";

Using above query you can fetch data from database and convert into json format and can send it to android system. here is a good article http://blog.sptechnolab.com/2011/02/10/android/android-connecting-to-mysql-using-php/. i hope you like it.

Answer 5

I believe your code roughly will look like this:

$query = "SELECT 
 A.column1 AS First_1
 A.column2 AS First_2
 B.column2 AS Second
 C.column3 AS Third
 FROM table1 A, table2 B, table3 C 
 WHERE 
 A.fk1 = B.id AND
 B.fk2 = C.id";

where a column is a relevant record you want to show. Meanwhile, AS will act as a key name in JSON.