Date Range query

[AliHurworth]

Hi, can anyone help with formatting a date range query in MySQL?
It's just a form to enter two dates, and a second page to run the query and show the output.

The form looks like so:

<form id="getdate" action="tracking_result.php" method="post">
  	<table>
    	<tr>
        	<td>
            <input type="radio" value="single" checked="checked" name="type"/>
            </td>
            <td>
            Single date
            </td>
            <td>
            	<input type="text" id="date" name="date">
            </td>
         </tr>
         <tr>
         <td>
            <input type="radio" value="range" name="type"/>
            </td>
        	<td>
            Date Range
            </td>
            <td>
            <select type="text" name="smonth">
                <option value="1">January</option>
                --etc--
                <option value="12">December</option>
            </select>
            <input type="text" name="sday" size="5">
            </select>
            <select type="text" name="syear">
            	<option value="2011">2011</option>
                <option value="2012">2012</option>
            </select>

            <br />
            <select type="text" name="fmonth">
                <option value="1">January</option>
                --etc--
                <option value="12">December</option>
            </select>
            <input name="fday" type="text" size="5" value="1-31"/>
            </select>
            <select type="text" name="fyear">
            	<option value="2011">2011</option>
                <option value="2012">2012</option>
            </select>

            </td>
         </tr>
         <tr>
        </table>
     <input type="submit" value="Find data" />
     <input type="reset" value="Reset form" />
  </form>

and the PHP on the result page:

<?php
  	//data from form
	$type=$_POST['type'];
  	$date=$_POST['date'];
	$startDay=$_POST['sday'];
	$startMonth=$_POST['smonth'];
	$startYear=$_POST['syear'];
	$finDay=$_POST['fday'];
	$finMonth=$_POST['fmonth'];
	$finYear=$_POST['fyear'];
	
	//create date range dates
	$startRange = $startYear."-".$startMonth."-".$startDay;
	$endRange = $finYear."-".$finMonth."-".$finDay;
	
	//and the same for humans
	$startDisplay = $startDay."-".$startMonth."-".$startYear;
	$endDisplay = $finDay."-".$finMonth."-".$finYear;
	
	//Connect to MySQL
  	$conn = mysql_connect (etc);
	mysql_select_db ("etc", $conn);
	
	if ($type=='range')	{$query=mysql_query("SELECT * FROM sessions WHERE date BETWEEN $startRange AND $endRange");
	echo "Sessions between ".$startDisplay." and ".$endDisplay;
	} else if ($type=='single')	{
		$query=mysql_query("SELECT * FROM sessions WHERE date BETWEEN '2011-01-01' AND '2011-02-01'");
		echo "Sessions on ".$date;
	};
	$result=mysql_num_rows($query);
	echo "<br />The number of sessions is: ".$result;
  ?>

So my difficulty is that the second option (type=single) works fine, but I can't substitute the variables.
Any clues?

[pritaeas]

You forgot the single quotes around your dates.

[AliHurworth]

Okay, (thanks priritaes) - have updated and recoded to include the quotes.
Now works for the single date (type==single), but not (type==range), where it is still not counting the rows.

if ($type=='range')	{$query=mysql_query("SELECT * FROM sessions WHERE date = '$date'");
	echo "Sessions between ".$startDisplay." and ".$endDisplay;
	} else if ($type=='single')	{
		$query=mysql_query("SELECT * FROM sessions WHERE (date BETWEEN '$startRange' AND '$endRange')");
		echo "Sessions on ".$date;
	};
	$result=mysql_num_rows($query);
	echo "<br />The number of sessions is: ".$result;

[pritaeas]

You switched the queries.

[AliHurworth]

Your right. This is how it should look:

if 	($type=='range')	{
		$query=mysql_query("SELECT * FROM sessions WHERE (date BETWEEN '$startRange' AND '$endRange')");
	echo "Sessions between ".$startDisplay." and ".$endDisplay;
	} else if 
		($type=='single')	{
		$query=mysql_query("SELECT * FROM sessions WHERE date = '$date'");
		echo "Sessions on ".$date;
	};
	$result=mysql_num_rows($query);
	echo "<br />The number of sessions is: ".$result;

[pritaeas]

I think it should be:

SELECT * FROM sessions WHERE (date BETWEEN CAST('$startRange' AS DATE) AND CAST('$endRange' AS DATE))

From the manual: "For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE."

[AliHurworth]

Now I'm getting a "mysql_num_rows(): supplied argument is not a valid MySQL result resource" error.

if 	($type=='range')	{
		$query=mysql_query("SELECT * FROM sessions WHERE (DATE BETWEEN CAST ('$startRange' AS DATE) AND CAST ('$endRange' AS DATE)");
	echo "Sessions between ".$startDisplay." and ".$endDisplay;
	} else if 
		($type=='single')	{
		$query=mysql_query("SELECT * FROM sessions WHERE date = '$date'");
		echo "Sessions on ".$date;
	};
	$result=mysql_num_rows($query);
	echo "<br />The number of sessions is: ".$result;

[smantscheff]

Your query misses a closing bracket )
For debugging purposes add a routine which shows the mysql error messages if a query has no result. Use the functions mysql_errno() and mysql_error().

[AliHurworth]

Your query misses a closing bracket )
For debugging purposes add a routine which shows the mysql error messages if a query has no result. Use the functions mysql_errno() and mysql_error().

I am getting a result now, thanks for your suggestion.

I'm going to post the full PHP block, as I think I may be forming the query wrong - the error message is saying that mysql_num_rows is an invalid resource.

<?php
  	//data from form
	$type=$_POST['type'];
  	$date=$_POST['date'];
	$startDay=$_POST['sday'];
	$startMonth=$_POST['smonth'];
	$startYear=$_POST['syear'];
	$finDay=$_POST['fday'];
	$finMonth=$_POST['fmonth'];
	$finYear=$_POST['fyear'];
	
	
	
	//create date range dates
	$startRange = $startYear."-".$startMonth."-".$startDay;
	$endRange = $finYear."-".$finMonth."-".$finDay;
	
	//and the same for humans
	$startDisplay = $startDay."-".$startMonth."-".$startYear;
	$endDisplay = $finDay."-".$finMonth."-".$finYear;
	
	//Connect to MySQL
  	$conn = mysql_connect ("localhost", "TTX", "xxxx");
	mysql_select_db ("TTX", $conn);
	
	/*test values
	$startRange = "2011-01-01";
	$endRange = "2011-06-01";
	echo $startRange."<br />";
	echo $endRange;
	*/
	
	if 	($type=='range')	{
		$query=mysql_query("SELECT * FROM sessions WHERE (DATE BETWEEN CAST ('$startRange' AS DATE) AND CAST ('$endRange' AS DATE))");
	echo "Sessions between ".$startDisplay." and ".$endDisplay;
	} else if ($type=='single')	{
		$query=mysql_query("SELECT * FROM sessions WHERE date = '$date'");
		echo "Sessions on ".$date;
	};
	//$result=mysql_num_rows($query);
	$result = mysql_query($query,$conn);
	//echo mysql_num_rows($result);
	echo "<br />The number of sessions is: ".mysql_num_rows($result);
  ?>

I'm not even sure if I'm doing it the right way, actually: all I'm doing right now is counting rows returned, so maybe I'd be better using COUNT?

[smantscheff]

I do not see any debugging code in your sample. Add it and come back with the error message for mysql_query. If mysql_num_rows operates on an invalid resource, the error must have happened earlier in your code. I do not see any obvious flaws, though.
If you only want the number of rows, then the COUNT function is more efficient than applying mysql_num_rows on the result.

[AliHurworth]

Overninght, I've altered it as follows:

if 	($type=='range')	{
			$query="SELECT COUNT(*) FROM sessions WHERE (DATE BETWEEN CAST ('$startRange' AS DATE) AND CAST ('$endRange' AS DATE))";
			$result = mysql_query($query, $conn);
			echo $result;
			echo "Sessions between ".$startRange." and ".$endRange;
			$rows=mysql_num_rows($result);
			echo "<br />The number of sessions is: ".$rows;
		} else if ($type=='single')	{
			$query="SELECT COUNT(*) FROM sessions WHERE date='$date'";
			$result = mysql_query($query, $conn);
			echo $result;
			$rows=mysql_num_rows($result);
			echo "Sessions on ".$date;
			echo "<br />The number of sessions is: ".$rows;
		};

I'm not really sure where there is scope for more error reporting - the two difficulties are that the single type is not picking up the right number of sessions, and that the range is still showing the wrong kind of resource.
Can you tell me what Resource id#3 means?

Thanks for your help!

[AliHurworth]

Adding an error report on range as follows:

if 	($type=='range')	{
			$query="SELECT COUNT(*) FROM sessions WHERE (DATE BETWEEN CAST ('$startRange' AS DATE) AND CAST ('$endRange' AS DATE))";
			$result = mysql_query($query, $conn);
			[B]if($result==false){
			user_error("Query failed: " . mysql_error() . "<br />\n$query"); [/B]
			}
			echo "Sessions between ".$startRange." and ".$endRange;
			$rows=mysql_num_rows($result);
			echo "<br />The number of sessions is: ".$rows;
		} else if ($type=='single')	{
			$query="SELECT COUNT(*) FROM sessions WHERE date='$date'";
			$result = mysql_query($query, $conn);
			echo $result;
			$rows=mysql_num_rows($result);
			echo "Sessions on ".$date;
			echo "<br />The number of sessions is: ".$rows;
		};	
  ?>

Suggests a syntax error with the query. I can't find much guidance about comparing dates - any ideas?

[pritaeas]

Your DATE column is causing this. Try backticks around it.

[AliHurworth]

Thanks to all for the help - this is what worked (including the error-reporting loop).

if 	($type=='range')	{
			$query="SELECT id FROM sessions WHERE DATE >= '$startRange' AND DATE <= '$endRange'";
			$result = mysql_query($query, $conn);
			if($result==false){
			user_error("Query failed: " . mysql_error() . "<br />\n$query"); 
			}
			echo "Sessions between ".$startRange." and ".$endRange;
			$rows=mysql_num_rows($result);
			echo "<br />The number of sessions is: ".$rows;
		} else if ($type=='single')	{
			$query="SELECT id FROM sessions WHERE date='$date'";
			$result = mysql_query($query, $conn);
			echo $result;
			$rows=mysql_num_rows($result);
			echo "Sessions on ".$date;
			echo "<br />The number of sessions is: ".$rows;
		};