Reputation: 1
$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
Upvotes: 0
Views: 8118
Reputation: 60413
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));
Upvotes: 0
Reputation: 10490
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
Upvotes: 1
Reputation: 2813
$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);
Upvotes: 1