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.

Recommended Answers

All 3 Replies

that title's a bit deceptive huh?

here is your standard join query:
(by the way, try to get away from doing "select *", just gets slopy after a while)

select * from table1 
inner join table2 on 
    table1.fieldname = table2.fieldname
inner join table3 on
    table2.fieldname = table3.fieldname

that is just one example where there are thousands of possibilities.

this will retrieve all the data at once
i dont want to retrieve data at once i.e using join query
but want to retrieve the relevant records of particular id cause my first table are relate to other with foreign key.
e.g the id 1 in table 1 have 5 relevant records in table2 and three records in table3 what i want to do is print the id 1 record of table1 than iterate in table2 and print all the relevant records than iterate in table3 and print the relevant records and so on for id 2 of table1.

tip: for table 2 the query should be

$query="select table2.somefields from table2 where table2.foreign_key=table1.primaray_key";

 include 'config.php';

 $query = "SELECT a.fee_id,a.fee_name,a.fee_amount,b.paid,b.balance FROM feelist a INNER JOIN generalfee b WHERE a.fee_class='FS 2'";
$result = mysql_query($query);

$json[] = $row;

echo json_encode($json);

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.