How to get data from two mysql table in json using php

Question

$result=array();
$query = "SELECT * FROM $table";
$result = mysql_query($query, $conn);
while ($table = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
   $result[]=$table;

This code is used to retrieve data from one table my question is how to retrieve data from two tables in json which are related with foreign key i.e student{st_id,st_name,st_class} book{bk_id, author, ISBN,st_id}

if i want to retrieve the student records and all the books issued by that student in json format. How can get this thanks in advance

updaed my answer take a look an tell me how it pans out

Breezer
– Breezer

2010-12-07 05:48:03 +00:00
Commented Dec 7, 2010 at 5:48 — Breezer
– Breezer, Commented Dec 7, 2010 at 5:48

r92 · Accepted Answer · 2010-12-07 06:15:58Z

1

$query = 'SELECT students.*, books.* 
      FROM students
      LEFT JOIN books
      ON students.st_id = books.st_id';
$mysql_result = mysql_query($query);
$result = array();
while ($row = mysql_fetch_assoc($mysql_result)) {
   $result[] = $row;
}
return json_encode($result);

answered Dec 7, 2010 at 6:15

r92

2,8132 gold badges21 silver badges24 bronze badges

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

Comments

Breezer · Accepted Answer · 2010-12-07 07:24:40Z

1

You use the json_encode function

would look something like this

$result=array();
$query = "SELECT * FROM $table";
$result = mysql_query($query, $conn);
while ($table = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
   $result[]=json_encode($table);

or you could use a join how a join works can be read in this following link

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

might look something like this

$result=array();
$query = "SELECT * FROM table LEFT JOIN table2 ON table.st_id = table2.st_id";
$result = mysql_query($query, $conn);
while ($table = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
   $result[]=json_encode($table);

or if you have 2 arrays of data you can merge them by using array_merge and then json_encode it

edited Dec 7, 2010 at 7:24

answered Dec 7, 2010 at 5:44

Breezer

10.5k6 gold badges32 silver badges51 bronze badges

5 Comments

blogger Over a year ago

i already use this function but it for a single table my question is how can i retrieve data from two tables

Breezer Over a year ago

you do a left join i've posted alink that can be quite usefull take a look

blogger Over a year ago

well sir the join retrieve all the relevant records at once what i want to do is first retrieve the student of id 1 and the relevant books against that id, then student id 2 and relevant books of that id and so on.....

Breezer Over a year ago

updated my answer again :P but really I can't see why you don't wanna use a left join

prodigitalson Over a year ago

@Blogger: you only think you want to do that... use the join instead and then handle creating the proper structure form the data when you iterate over the results.

prodigitalson · Accepted Answer · 2010-12-07 15:34:58Z

0

As breezer mentioned you shoudl use a join here so you only hit the DB once. Ther eis absolutely no need to do multiple queries. As far as structuring the data i would do something like this:

$sutdents = array();

$query = "SELECT * FROM table LEFT JOIN table2 ON table.st_id = table2.st_id";
$result = mysql_query($query, $conn);

$bookFields = array_fill_keys(array(
  'bk_id', 
  'author', 
  'ISBN'
), null);

$studentFields = array_fill_keys(array(
  'st_id',
  'st_name',
  'st_class'
), null);

$students = array();

while ($sutdent = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
   $stId = $student['st_id'];
   if(isset($students[$stId]['books'])){
      $students[$stId]['books'][] = array_intersect_key($student, $bookFields);
   } else {
     $students[$stId] = array_intersect_key($student, $studentFields);
     $students[$stId]['books'] = array(array_intersect_key($student, $bookFields));
   }
}

return json_encode($students);

obviously this format is different but i prefer things nested in logical structures. You could however do exactly what you saked like so:

$query = "SELECT * FROM table LEFT JOIN table2 ON table.st_id = table2.st_id";
$result = mysql_query($query, $conn);

$bookFields = array_fill_keys(array(
  'bk_id', 
  'author', 
  'ISBN'
), null);

$studentFields = array_fill_keys(array(
  'st_id',
  'st_name',
  'st_class'
), null);

$students = array();
$books = array();

while ($sutdent = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
   $stId = $student['st_id'];

   if(!isset($students[$stId]){
     $students[$stId] = array('student' => array_intersect_key(
       $student, 
       $studentFields
     ));
   }

   if(!isset($books[$stId])){
      $books[$stId] = array(); 
   }

   $books[$stId][] = array('book' => array(array_intersect_key($student, $bookFields));
}

// convert these from assoc to numeric arrays so the come as arrays in json
$books = array_values($books);
$students = array_values($students);

// final hash looks like {students: [{student: {}}, {student: {}}], books: [{book: {}}, {book: {}}]}
return json_encode(array('students' => $students, 'books' => $books));

edited Dec 7, 2010 at 15:34

answered Dec 7, 2010 at 6:48

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

3 Comments

blogger Over a year ago

what if i use 2 queries instead of one

prodigitalson Over a year ago

Probably typos (i just corrected 4 of them - should be array_fill_keys not array_fill_key), there may be others, not sure. if you want to post the error messages ill take a look.

prodigitalson Over a year ago

@blogger: Please explain why you want to use 2 queries - which isnt actually two queries but 1+(the number of students) queries.

Collectives™ on Stack Overflow

How to get data from two mysql table in json using php

3 Answers 3

Comments

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related