Running a SELECT query inside foreach loop

[aliks0905]

I'm creating a calendar which retrieves a list of dates specified in a file and compares that to the dates in the database that are already scheduled. The problem is I am not very well educated in PHP and MySQL therefore not sure why the following code only returns the correct number of rows for the first date, yet does not for any of the subsequent dates.

 

As a side note, in this code we're being connected to the database many times, however I've also tried connecting to the database outside the loop - which didn't make any changes.

 

Any help greatly appreciated!

 

		<?php 
	//file to retrieve dates
	$filename = "datelist/aprildates.txt";
	$fd = fopen ($filename, "r");
	$contents = fread ($fd,filesize ($filename));
	fclose ($fd); 

	$delimiter = ";";
	$splitcontents = explode($delimiter, $contents);

	//commented out temporarily - echo "<select name='reservedate'>";
		foreach ( $splitcontents as $date )
		{
			//connect to db
			$link = mysql_connect("localhost", "root", "") or die(mysql_error());
			mysql_select_db ("slots") or die(mysql_error()); 
			$query = mysql_query("SELECT * FROM slotinfo WHERE date = '$date'", $link) or die(mysql_error());
			$numrows = mysql_num_rows($query);

			//testing to see query results
			echo $date." ";
			echo $numrows."<br/>";

			//if less than 5 dates in db, then echo the option to select that date
			if ($numrows > 5) { 
			} else {
				$date2 = date('l, M d, Y', strtotime($date));
				//commented out temporarily -  echo "<option value='$date'>$date2</option>\n";

			}
		mysql_close($link);
		$link = null;
		}
	//commented out temporarily - echo "</select>";

	?>

[JustLikeIcarus]

What do you get if you just run this?

 

$filename = "datelist/aprildates.txt";
$fd = fopen ($filename, "r");
$contents = fread ($fd,filesize ($filename));
fclose ($fd); 
$delimiter = ";";
$splitcontents = explode($delimiter, $contents);
foreach ( $splitcontents as $date )
{
  echo $date . "</br>";
}

[aliks0905]

I get the dates to come out just fine. The problem is the first mysql_num_rows returns the correct number of rows for that date, however, the subsequent rows return 0 rows where in reality there is more than 0.

[JustLikeIcarus]

Run this and make sure that all of queries are coming out correct.

 

$filename = "datelist/aprildates.txt";
$fd = fopen ($filename, "r");
$contents = fread ($fd,filesize ($filename));
fclose ($fd); 
$delimiter = ";";
$splitcontents = explode($delimiter, $contents);
foreach ( $splitcontents as $date )
{
  echo "SELECT * FROM slotinfo WHERE date = '$date'";
echo "<br />";
}

[aliks0905]

The results I receive are:

 

The dates for the $date field are correct except for the very last one with no date.

 

Select a date: SELECT * FROM slotinfo WHERE date = '2010-04-02'
SELECT * FROM slotinfo WHERE date = ' 2010-04-05'
SELECT * FROM slotinfo WHERE date = ' 2010-04-07'
SELECT * FROM slotinfo WHERE date = ' 2010-04-09'
SELECT * FROM slotinfo WHERE date = ' 2010-04-12'
SELECT * FROM slotinfo WHERE date = ' 2010-04-14'
SELECT * FROM slotinfo WHERE date = ' 2010-04-16'
SELECT * FROM slotinfo WHERE date = ' 2010-04-19'
SELECT * FROM slotinfo WHERE date = ' 2010-04-21'
SELECT * FROM slotinfo WHERE date = ' 2010-04-23'
SELECT * FROM slotinfo WHERE date = ' 2010-04-26'
SELECT * FROM slotinfo WHERE date = ' 2010-04-28'
SELECT * FROM slotinfo WHERE date = ' 2010-04-30'
SELECT * FROM slotinfo WHERE date = ''

[JustLikeIcarus]

Ohh you gotta remove those spaces in front of the dates  ' 2010-04-19' from the second query down.

[aliks0905]

wow!! thanks a lot! a small trim() function fixed everything!