Jump to content

Retrieving data from multi tables


Mufleeh

Recommended Posts

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?

 

 

 

 

 

 

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

 

 

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.