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 ............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 used foreach loop to view the data on this case. Can anyone please let me know how to retrieve the data from multiple tables?

 

 

 

 

 

 

 

[btherl]

What I can see from what you've posted is

 

Table teachers links to teacher_appointments on nic column

school_locations links to divisions on division_id column

 

But how does school_locations link to teachers and teacher_appointments?

[Mufleeh]

Hi,

 

This is my complete query...

 

$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]')))");

 

Thank you

 

[btherl]

Ok.  Using explicit join syntax, you could write that as:

 

SELECT *
FROM teachers t
JOIN teacher_appointements ta ON (ta.nic = t.nic)
JOIN school_locations sl ON (sl.census_id = ta.census_id)
JOIN divisions d ON (d.division_id = sl.division_id)
WHERE d.division_name = '{$_POST['division_name']}'

 

I have given each table a short alias.  Each "JOIN" is followed by an "ON", which tells Postgres how that table relates to the other tables.  Can you try this query and see if it does what you want?

[Mufleeh]

Thank you so much! Its great, it made the task easy.

 

I really want to know how to retrieve the data, I mean all the teachers from a single zone. Even this code seem to work only if there is only one division and one school in a given zone. I hope the output of the query is a multidimensional array isn't it?

 

I have done this using foreach loop...which works only if there is one field in every table as I said above. How can I make the foreach loop to get all the data?

 

 

Thanks

[btherl]

Please post your current query code and the foreach loop.

 

What goes wrong when there is more than one division and one school in the zone?

[Mufleeh]

Hi,

 

Thanks for the responses, I managed to fix it.

 

Query I made in the beginning is,

 

$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 if there is only one field in the division, and locations table. But when I rewrite it as below it works properly.

 

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

 

 

Thanks for your useful help.

 

Cheers!