Showing retrieved data from multi tables

[Mufleeh]

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 have tables like 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 when there is only one zone, one division and one school. I used foreach loop to view the data on this case and the problem is when there are many schools for a given division.

My foreach loop looks like below...

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>";
 
}

Can anyone please let me know how to show the data from multiple tables?

[John Doe]

Mufleeh,

The SQL statement you want is something similar to this...

SELECT * FROM 
teachers t, schools s, division d, zones z
WHERE 
t.schoolID = s.ID
AND
s.divisionID = d.ID
AND
d.zoneID = z.ID
AND
z.name = 'zone1'

If you use this format, you'll need to change the '*' to a list of the fields that you want returned, otherwise you'll get all fields from all tables!
eg. SELECT t.nic, t.name_initials, t.name_full, t.sex FROM ... etc.


The above SQL is based on the following test table structures, but I think you've already got the idea of linking ID fields between tables...

-- --------------------------------------------------------
 
--
-- Table structure for table `zones`
--
 
CREATE TABLE IF NOT EXISTS `zones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `zone` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `zone` (`zone`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
 
--
-- Dumping data for table `zones`
--
 
INSERT INTO `zones` (`id`, `zone`) VALUES(1, 'zone1');
INSERT INTO `zones` (`id`, `zone`) VALUES(2, 'zone2');
INSERT INTO `zones` (`id`, `zone`) VALUES(3, 'zone3');
 
-- --------------------------------------------------------
 
--
-- Table structure for table `division`
--
 
CREATE TABLE IF NOT EXISTS `division` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `zoneID` int(11) NOT NULL,
  `division` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `zoneID` (`zoneID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
 
--
-- Dumping data for table `division`
--
 
INSERT INTO `division` (`id`, `zoneID`, `division`) VALUES(1, 1, 'div1');
INSERT INTO `division` (`id`, `zoneID`, `division`) VALUES(2, 1, 'div2');
INSERT INTO `division` (`id`, `zoneID`, `division`) VALUES(3, 1, 'div3');
INSERT INTO `division` (`id`, `zoneID`, `division`) VALUES(4, 2, 'div4');
INSERT INTO `division` (`id`, `zoneID`, `division`) VALUES(5, 2, 'div5');
INSERT INTO `division` (`id`, `zoneID`, `division`) VALUES(6, 3, 'div6');
 
-- --------------------------------------------------------
 
--
-- Table structure for table `schools`
--
 
CREATE TABLE IF NOT EXISTS `schools` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `divisionID` int(11) NOT NULL,
  `school` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `divisionID` (`divisionID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
 
--
-- Dumping data for table `schools`
--
 
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(1, 1, 'school1');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(2, 1, 'school2');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(3, 2, 'school3');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(4, 2, 'school4');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(5, 3, 'school5');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(6, 3, 'school6');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(7, 4, 'school7');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(8, 4, 'school8');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(9, 5, 'school9');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(10, 5, 'school10');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(11, 6, 'school11');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(12, 6, 'school12');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(13, 7, 'school13');
INSERT INTO `schools` (`id`, `divisionID`, `school`) VALUES(14, 7, 'school14');
 
-- --------------------------------------------------------
 
--
-- Table structure for table `teachers`
--
 
CREATE TABLE IF NOT EXISTS `teachers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `schoolID` int(11) NOT NULL,
  `teacher` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `schoolID` (`schoolID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;
 
--
-- Dumping data for table `teachers`
--
 
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(1, 1, 'teacher1');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(2, 1, 'teacher2');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(3, 2, 'teacher3');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(4, 2, 'teacher4');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(5, 3, 'teacher5');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(6, 3, 'teacher6');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(7, 4, 'teacher7');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(8, 4, 'teacher8');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(9, 5, 'teacher9');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(10, 5, 'teacher10');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(11, 6, 'teacher11');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(12, 6, 'teacher12');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(13, 7, 'teacher13');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(14, 7, 'teacher14');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(15, 8, 'teacher15');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(16, 8, 'teacher16');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(17, 9, 'teacher17');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(18, 9, 'teacher18');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(19, 10, 'teacher19');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(20, 10, 'teacher20');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(21, 11, 'teacher21');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(22, 11, 'teacher22');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(23, 12, 'teacher23');
INSERT INTO `teachers` (`id`, `schoolID`, `teacher`) VALUES(24, 12, 'teacher24');

[John Doe]

Actually, not knowing the data structures of your tables, you may be able to achieve what you want just by changing the '=' comparison to an 'IN' comparison, like so...

SELECT * 
FROM teachers
WHERE schoolID IN  
(
SELECT id FROM schools s WHERE s.divisionID IN
(
SELECT id FROM division d WHERE d.zoneID IN 
(
SELECT id FROM zones z WHERE z.zone =  'zone1')
)
)

giving you...

$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 IN (SELECT division_id FROM divisions WHERE divisions.division_name='$_POST[division_name]')))");

The difference is that IN allows many results to be returned, whereas '=' is expecting only one result to compare against.

[Mufleeh]

Good morning John Doe,

It is really really great. Thank you very much!