Problems with While loop

[kaezic]

I have a page that is pulling dynamically pulling data from a database to create a table for the user.

My first while loop pulls dates correctly. My second step is to pull names from a database and list the names in the first column.
My third step is another while loop in the third and proceeding columns to execute another query and list the results based off of the results from the first loop.

The problem I am having is that I am only able to obtain the first row of data, while there should be at least 40 rows of data.

Any help would be greatly appreciated.

Here is the code:

echo "
<table align=\"center\" border=\"1\">
<tr>
<td colspan=\"2\"><font size=\"1\">$drdm</font></td>";

		//print the column headers for the days that where entered
		$i = 0;
		while ($i <= $num_dd):	
			$new_day = $b_date + $i;
			$new_date = date("m/d/Y", mktime (0,0,0,$b_month,$new_day,$b_year));	
			echo "<td><font size=\"1\">$new_date</font></td>";
			$i++;
		endwhile;

	echo "</tr>";

	$db = mysql_select_db("regular_dispatch",$connection) or die ("Couldn't select database.");

	$sql = "select distinct(drnumber) as drnumber, drname from dsrecords where dspdate 
			between '$firstqdate' and '$lastqdate' and drdm=\"atw\" order by drnumber, dspdate";

	$sql_result = mysql_query($sql, $connection) or die ("Couldn't get drivers and data.... line 56");

	while ($row = mysql_fetch_array($sql_result)) {

	$drnum=$row["drnumber"];
	$drname=$row["drname"];

echo "
	<Tr>
		<td><font size=\"1\">$drnum</font></td>
		<td><font size=\"1\">$drname</font></td>";

		//print the empty cells for each day by driver

		for ($i = 0; $i <= $num_dd; $i++)
		{
			$new_day = $b_date + $i;
			$new_date2 = date("Y-m-d", mktime (0,0,0,$b_month,$new_day,$b_year));
			echo "<td>&nbsp;";

				$db = mysql_select_db("regular_dispatch",$connection) or die ("Couldn't select database.");

				$sql = "select route, dspdate from dsrecords where dspdate='$new_date2' and 
							drnumber='$drnum' ";

				$sql_result = mysql_query($sql, $connection) or die ("Couldn't get drivers and data.... line 56");

				while ($row = mysql_fetch_array($sql_result)) {

				$route=$row["route"];
				$dspdate=$row["dspdate"];

				echo "<font size=\"1\">$route - $dspdate</font></td>";
				}			
		}
		/*$i = 0;
		while ($i <= $num_dd):	
			$new_day = $b_date + $i;
			$new_date2 = date("Y-m-d", mktime (0,0,0,$b_month,$new_day,$b_year));
			echo "<td>&nbsp;";

				$db = mysql_select_db("regular_dispatch",$connection) or die ("Couldn't select database.");

				$sql = "select route, dspdate from dsrecords where dspdate='$new_date2' and 
							drnumber='$drnum' ";

				$sql_result = mysql_query($sql, $connection) or die ("Couldn't get drivers and data.... line 56");

				while ($row = mysql_fetch_array($sql_result)) {

				$route=$row["route"];
				$dspdate=$row["dspdate"];

				echo "<font size=\"1\">$route - $dspdate</font></td>";
				}					
			$i++;
			endwhile;*/

	echo "
	</tr>";
}
echo"
</table>";

[tomhath]

The reason you are only getting one row is because you reused $sql_result to store the resource in both the inner and outer while loops. Use a different variable for each of your mysql_query calls. I'd use different variables for $row also.

Other things that look odd to me:

• You don't need to call mysql_select_db each time through the various loops. Once at the beginning is plenty.

• "select distinct(drnumber) as drnumber, drname from dsrecords ..."
  should be "select distinct drnumber, drname from dsrecords ..."

• Your "or die(...)" reports line 56 in three different places. two of them are probably not line 56 😉

[kaezic]

Thank you for the "very apparent" mistake. Wow, you look at code a few times and you miss the obvious. This forum is Great. Thanks again.