Usage of mysql_num_rows inside a While loop

Question

I have a query selecting a group of matching elements, and then a If condition ckecking if one (or more) of those matching elements are valid, depending on what they contain on a couple of DATE fields. This works correctly:

$allClasses=mysql_query("SELECT * FROM class_conf WHERE cl_fk_te_id='$te_id' && cl_status='1'");
$identifiedClassId="";

 while($registros = mysql_fetch_array($allClasses)){

     $start_date=$registros['cl_startDate'];
     $end_date=$registros['cl_endDate'];

     $start_year=substr($start_date, 6, 4);
     $end_year=substr($end_date, 6, 4);
     $start_month=substr($start_date, 3, 2);
     $end_month=substr($end_date, 3, 2);
     $start_day=substr($start_date, 0, 2);
     $end_day=substr($end_date, 0, 2);

     if(($start_year  <=  $year) && ($end_year  >=  $year) && ($start_month  <=  $month) && ($end_month  >=  $month) && ($start_day  <=  $day) && ($end_day  >=  $day)) {

            $identifiedClassId=$registros['cl_id'];
     }

 }

Now I need to use something like mysql_num_rows in order to check the total amount of elemnts matching this If condition, and I do not know what query to apply on mysql_num_rows, as this query (mysql_num_rows($allClasses)) would result in the amout of matching elements before applying the If.

How can I obtain the total amount of elements matching the If condition?

I have tried using after the while loop this: $number=mysql_num_rows($identifiedClassId); but making an echo $number; just gives me a mysql error ( mysql_num_rows() expects parameter 1 to be resource, string given) — Biomehanika, May 27 '14 at 08:46
$number=mysql_num_rows($identifiedClassId); needs to be immediately after the 'mysql_query' statement and before the 'while' statement. — Ryan Vincent, May 27 '14 at 08:56
I have tried placing $number=mysql_num_rows($identifiedClassId); right after the mysql_query statement and before the While as you say, also inside the While loop, also inside the IF condition, also after the loop... and everything gives back the mysql error "mysql_num_rows() expects parameter 1 to be resource, string given" — Biomehanika, May 27 '14 at 09:09
Updated code with a count. if it doesn't do what you want we need to clarify exactly what you want to see as regard the '$registros['cl_id']' values. It isn't difficult to count them. — Ryan Vincent, May 27 '14 at 10:06

Ryan Vincent · Accepted Answer · 2014-05-27T09:57:52.173

Here is the code that should be used: It is untested. It sets the count of the number of rows returned by the query, into, $totalElements.

Added the count of all assignments to the $identifiedClassId which i suspect will not be correct.

$allClasses=mysql_query("SELECT * FROM class_conf WHERE cl_fk_te_id='$te_id' && cl_status='1'");
$totalElements = mysql_num_rows($allClasses); // count of the elements 
$identifiedClassId="";
$countIdentifiedClassId = 0;

 while($registros = mysql_fetch_array($allClasses)){

     $start_date=$registros['cl_startDate'];
     $end_date=$registros['cl_endDate'];

     $start_year=substr($start_date, 6, 4);
     $end_year=substr($end_date, 6, 4);
     $start_month=substr($start_date, 3, 2);
     $end_month=substr($end_date, 3, 2);
     $start_day=substr($start_date, 0, 2);
     $end_day=substr($end_date, 0, 2);

     if(($start_year  <=  $year) && ($end_year  >=  $year) && ($start_month  <=  $month) && ($end_month  >=  $month) && ($start_day  <=  $day) && ($end_day  >=  $day)) {

            $identifiedClassId=$registros['cl_id'];
            $countIdentifiedClassId++;
     }
 }

Whay I need is to know the total amout of elements matching THE IF CONDITION, in this case $totalElements would result on the number of matches on $allClasses, but not on $identifiedClassId :( — Biomehanika, May 27 '14 at 09:37

Usage of mysql_num_rows inside a While loop

1 Answers1