Retrieving data from multi tables

[Mufleeh]

Hi all,

 

I am new to PHP as well as to this forum. Thanks in advance for you support.

 

My problem is to retrieve data from multi tables. I mean contains Zones, Division, Schools and Teachers. In a single Zone there can be many divisions and in a single division there can be many schools. I need to make a query to find out all the teachers in a particular zone.

 

I made the query like below,

 

$query = $this->dbh->prepare("SELECT * FROM teachers,school_locations where teachers.nic=(SELECT nic FROM teacher_appointments where teacher_appointments.census_id =(SELECT census_id FROM school_locations where school_locations.division_id=(SELECT division_id FROM divisions WHERE divisions.division_name='$_POST[division_name]')))");

 

this works fine where there is only one zone, one division and one school, i mean when the field in the tables are one.

 

I used foreach loop to view the data on this case. Can anyone please let me know how to display the data when there are many fields like many schools for a single division and many divisions for a single zone etc.?

 

below the foreach loop I used,

 

foreach ($teachers AS $row)

{

echo "<tr>";

$i = $i + 1;

echo "<td>$i.</td>

 

<td>" .$row[division_id]."</td>

<td>" .$row[nic]."</td>

<td>" .$row[name_initials]."</td>

<td>" .$row[name_full]."</td>

<td>".  $row[date_of_birth]."</td>

<td>".  $row[sex]."</td>

<td>".  $row[address_permanent]."</td>

<td>".  $row[zone_id]."</td>";

}

 

[TeNDoLLA]

is kinda hard to start guessing since do not know your database structure. Also this might be more of a MySQL question than php? Maybe something like this?

 

SELECT DISTINCT t.* FROM teachers t
JOIN schools s ON s.teacher_id = t.teacher_id
JOIN divisions d ON d.school_id = s.school_id
JOIN zones z ON z.division_id = d.zone_id
WHERE d.divisions_name = $division_name_from_user_input