Queries within foreach loop

[Mufleeh]

Hi all,

 

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

[AyKay47]

from this snippet of code it's hard to say, there are a few things that I see here

 

1. the use of $this should be used inside of a class definition, if this code is inside of a class, please show the class as well.

 

2. Please show the custom functions.

 

3. I would recommend using COUNT(*) rather than COUNT(col) in most cases, much more efficient

[Mufleeh]

Hi,

 

Thanks for your response, yes its inside a class I'll use $this where its possible. Below is the entire function I am working with. Also I'll change the count(nic) to count(*). If you see any errors in my function please let me know, also please note that the code works well up to foreach($divisions as $div)

 

 

function graphMathsTeacherbyDivision()
		{

					//echo '<p><b>Select the Zone to Generate the Graph for Mathematics Teacher Distribution:</b></p>';
					//Query for all Zones
					$query = $this->dbh->prepare("SELECT * FROM zones");				
					$query->execute();		
					$this->allZones = $query->fetchall();
					$query = NULL;


					?>
                        
						<!--form inside HTML-->
                            <p><b>Select the Zone to Generate the Graph for Mathematics Teacher Distribution:</b></p>
                                                        
                            <form method="post" id="graphMathsTeacherbyDivision" action="<?php echo $_SERVER['SCRIPT_NAME']?>">
                                                        
                            <select name="selectZone">

                                                
                            <?php foreach($this->allZones as $zone)
                            {
                             echo "<option";
                             echo " value='$zone[zone_id]' ";
                             echo ">";
                             echo $zone['zone_id'].": ".$zone['zone_name'];
                             echo "</option>";
                             }?>
                             					
                             </select>
                                              
                             <input type="submit" name='zone_id' value="Generate Graph"/>
                                                            
                             </form>

$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();
               
              //Below this it seems to be an issue
               [color=red]foreach($divisions as $div)[/color]
               {      

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

}

[AyKay47]

as long as $divisions is an array, your foreach loop should work.

What errors do you receive when running this script?

[Maq]

Mufleeh, in the future, please place

OR

tags around your code.

[Mufleeh]

Hi,

 

I noted it and I'll do in in future.

 

Below is the error message I am receiving now!

 

 

PDOStatement::errorInfo(): Array ( [0] => 00000 [1] => 7 [2] => ERROR: operator does not exist: character varying = integer LINE 1: ...ELECT census_id FROM view_schools 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: ...ELECT census_id FROM view_schools 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: ...ELECT census_id FROM view_schools 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: ...ELECT census_id FROM view_schools WHERE division_id = 010117 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. )