Queries within foreach loop

Mufleeh

I want the count of math teachers for a given zone. A single zone can have several divisions and and a single division can have many schools and a school can have many math teachers. Below is my query and it doesn't work as I expected. Can you please try to fins if I have made any errors?

$selectZone = $_POST['selectZone']; // get the data from the user

           $query = $this->dbh->prepare("SELECT division_id FROM view_zone_division_district_province WHERE zone_id='$selectZone'");
           $query->execute();
           $divisions = $query->fetchall();


           foreach($divisions as $div)
           {      

           $query = $this->dbh->prepare("SELECT COUNT(nic) FROM teachers_info WHERE (subject_name = 'Mathematics' AND school_id IN(SELECT school_id FROM schools WHERE division_id = $div))");

           $query->execute();
           $count = $query->fetchall();   ]

           echo "<tr>";
           $i = $i + 1;
           echo "<td>$i.</td>
           <td>" .$count."</td>";
           echo "</tr>";

}

Regards,
Mufleeh

jorgepinho

Hi,

Are you having any PHP errors ?

If you test those SQL commands in the DB server, are the results OK ?

Mufleeh

Hi,

Thanks for your response. I do not receive any errors and also it seems working up to the foreach($divisions as $div) loop. Only then after the query is not working. Isn't there any errors noticed in my code?

Regards,
Mufleeh

Mufleeh

jorgepinho;10984170 wrote:
Hi,

Are you having any PHP errors ?

If you test those SQL commands in the DB server, are the results OK ?

Hi,

I am receiving the below error now. As it says do you have to do any type casts?

PDOStatement::errorInfo(): Array ( [0] => 00000 [1] => 7 [2] => ERROR: operator does not exist: character varying = integer LINE 1: ...T census_id FROM school_locations WHERE division_id = 010114 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. ) PDOStatement::errorInfo(): Array ( [0] => 00000 [1] => 7 [2] => ERROR: operator does not exist: character varying = integer LINE 1: ...T census_id FROM school_locations WHERE division_id = 010115 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. ) PDOStatement::errorInfo(): Array ( [0] => 00000 [1] => 7 [2] => ERROR: operator does not exist: character varying = integer LINE 1: ...T census_id FROM school_locations WHERE division_id = 010116 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. ) PDOStatement::errorInfo(): Array ( [0] => 00000 [1] => 7 [2] => ERROR: operator does not exist: character varying = integer LINE 1: ...T census_id FROM school_locations WHERE division_id = 010117 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. )

laserlight

I wonder if you can combine this into a single query, maybe something like:

SELECT divisions.division_id, COUNT(teachers_info.nic)
    FROM view_zone_division_district_province AS divisions
        JOIN schools ON divisions.division_id=schools.division_id
        JOIN teachers_info ON schools.school_id=teachers_info.school_id
    WHERE teachers_info.subject_name = 'Mathematics'
    GROUP BY divisions.division_id

Mufleeh

laserlight;10984188 wrote:

I wonder if you can combine this into a single query, maybe something like:

SELECT divisions.division_id, COUNT(teachers_info.nic)
    FROM view_zone_division_district_province AS divisions
        JOIN schools ON divisions.division_id=schools.division_id
        JOIN teachers_info ON schools.school_id=teachers_info.school_id
    WHERE teachers_info.subject_name = 'Mathematics'
    GROUP BY divisions.division_id

Hi,

Thanks for the response, but can you please tell me what's 'divisions' that you have used here? are referring to a table? Also can you please let me know how can I replace me coding with this?

laserlight

Mufleeh wrote:
Thanks for the response, but can you please tell me what's 'divisions' that you have used here? are referring to a table?

As you can see from the SQL statement, it is an alias for view_zone_division_district_province.

Mufleeh wrote:
Also can you please let me know how can I replace me coding with this?

If it works - and I don't know if it works since I typically have to test my SQL statements that involve aggregation to get them right - you will get a result set consisting of division ids and the corresponding counts. You can then loop over it and display. That said, I noticed that I missed a divisions.zone_id='$selectZone' in the WHERE clause.

Actually, if you do not need to display the individual counts of maths teachers in each division, i.e., you only want the total number of maths teachers in the given zone, and assuming that teachers teach in no more than 1 school, the query could become:

SELECT COUNT(*)
    FROM view_zone_division_district_province AS divisions
        JOIN schools ON divisions.division_id=schools.division_id
        JOIN teachers_info ON schools.school_id=teachers_info.school_id
    WHERE teachers_info.subject_name = 'Mathematics'
        AND divisions.zone_id='$selectZone'

Mufleeh

laserlight;10984198 wrote:
As you can see from the SQL statement, it is an alias for view_zone_division_district_province.

If it works - and I don't know if it works since I typically have to test my SQL statements that involve aggregation to get them right - you will get a result set consisting of division ids and the corresponding counts. You can then loop over it and display. That said, I noticed that I missed a divisions.zone_id='$selectZone' in the WHERE clause.

Actually, if you do not need to display the individual counts of maths teachers in each division, i.e., you only want the total number of maths teachers in the given zone, and assuming that teachers teach in no more than 1 school, the query could become:
SELECT COUNT(*)
    FROM view_zone_division_district_province AS divisions
        JOIN schools ON divisions.division_id=schools.division_id
        JOIN teachers_info ON schools.school_id=teachers_info.school_id
    WHERE teachers_info.subject_name = 'Mathematics'
        AND divisions.zone_id='$selectZone'

Hi,

I have mistaken if I said you that I no need the individual count for a division, I need it too because I need to plot a graph for division vs math teachers. But a teacher is assigned only for 1 school. If thats the case can we do that with this?

Regards,
Mufleeh