PHP Searching a database between two date ranges

[Giarc86]

I have a MySQL database which I am retrieving the data from and displaying in HTML.
Underneath the results I want to be able to search for records where the dates are between X and Y.

The user will input dates From: and To: and click Submit and only those records corresponding to those dates will display.

How can I go about this? I have been Googling but can't find anything helping exactly. Or it is confusing me, my PHP knowledge isn't that great.
Does anyone have any links?

Thanks

 

[stricken]

get a javascript datepicker like this one : http://www.javascriptkit.com/script/script2/tengcalendar.shtml

then when you post, you will get the input in your $_REQUEST[] array (or $_POST or $_GET dep. on method).. i just use $_REQUEST cos it covers both.

Then craft your query... $sql = "SELECT * FROM table WHERE datecol < '".$_REQUEST['date_from']."' AND datecol > '".$_REQUEST['date_to']."'; ";


Something like that. Google is your fwend. ; )

 

[Other Pineapple Smurf]

get a javascript datepicker like this one : http://www.javascriptkit.com/script/script2/tengcalendar.shtml

then when you post, you will get the input in your $_REQUEST[] array (or $_POST or $_GET dep. on method).. i just use $_REQUEST cos it covers both.

Then craft your query... $sql = "SELECT * FROM table WHERE datecol < '".$_REQUEST['date_from']."' AND datecol > '".$_REQUEST['date_to']."'; ";


Something like that. Google is your fwend. ; )

$sql = "SELECT * FROM table WHERE datecol =< '".$_REQUEST['date_from']."' AND datecol >= '".$_REQUEST['date_to']."'; ";

 

[pookfuzz]

Just remember wrap any GET/POST vars in mysql_real_escape_string unless you want script kiddies to make you sad.

 

[Giarc86]

Thanks for that guys, that does make sense. I just can't get it to work now though. Am I doing it correctly?

demo.htm

<form name="datesearch" method="post" action="search.php">
Form: <input id="demo1" name="datefrom" type="text" size="25">
<a href="javascript:NewCal('demo1','ddmmyyyy')">
<img src="cal.gif" width="16" height="16" border="0" alt="Pick a date"></a>
To: <input id="demo1" name="dateto" type="text" size="25">
<a href="javascript:NewCal('demo1','ddmmyyyy')">
<img src="cal.gif" width="16" height="16" border="0" alt="Pick a date"></a>
<input type="submit" value="Submit" >
</form>

search.php

<?php
require "config.php";            

$page_name="search.php"; 
$start=$_GET['start'];
if(strlen($start) > 0 and !is_numeric($start)){
echo "Data Error";
exit;
}


$eu = ($start - 0); 
$limit = 4;                                
$this1 = $eu + $limit; 
$back = $eu - $limit; 
$next = $eu + $limit; 



$query2="SELECT * FROM records WHERE mydate =< '".$_GET['datefrom']."' AND mydate >= '".$_GET['dateto']."'; ";
$result2=mysql_query($query2);
echo mysql_error();
$nume=mysql_num_rows($result2);


$query=" SELECT * FROM records WHERE mydate =< '".$_GET['datefrom']."' AND mydate => '".$_GET['dateto']."' limit $eu, $limit ";
$result=mysql_query($query);
echo mysql_error();


while($rows = mysql_fetch_array($result))
{
if($bgcolor=='#CCCCCC'){$bgcolor='#CCCCCC';}
else{$bgcolor='#CCCCCC';}
?>

<table width="600" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
            <tr>
            <td><table width="600" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
            <tr>
            <td width="90">Plate No.</td>
            <td width="6">:</td>
            <td width="282"><?php echo $rows['firstlevel']; ?></td>
            </tr>
            <tr>
            <td>Machine No.</td>
            <td>:</td>
            <td><?php echo $rows['secondlevel']; ?></td>
            </tr>
            <tr>
            <td valign="top">Chase No.</td>
            <td valign="top">:</td>
            <td><?php echo $rows['thirdlevel']; ?></td>
            </tr>
            <tr>
            <td valign="top">Meter </td>
            <td valign="top">:</td>
            <td><?php echo $rows['mydate']; ?></td>
            <tr>
            <td valign="top">Problem </td>
            <td valign="top">:</td>
            <td><?php echo $rows[name']; ?></td>
            <tr>
            <td valign="top">Remarks </td>
            <td valign="top">:</td>
            <td><?php echo $rows['comments']; ?></td>
            </tr>
            </table></td>
            </tr>
            </table><br />
<?php
echo "</tr>";
}
echo "</table>";



if($nume > $limit ){ // Let us display bottom links if sufficient records are there for paging


echo "<table align = 'center' width='50%'><tr><td  align='left' width='30%'>";

if($back >=0) { 
print "<a href='$page_name?start=$back'><font face='Verdana' size='2'>PREV</font></a>"; 
} 

echo "</td><td align=center width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $eu){
echo " <a href='$page_name?start=$i'><font face='Verdana' size='2'>$l</font></a> ";
}
else { echo "<font face='Verdana' size='4'><b>$l</b></font>";}        /// Current page is not displayed as link and given font color red
$l=$l+1;
}


echo "</td><td  align='right' width='30%'>";

if($this1 < $nume) { 
print "<a href='$page_name?start=$next'><font face='Verdana' size='2'>NEXT</font></a>";} 
echo "</td></tr></table>";

}// end of if checking sufficient records are there to display bottom navigational link. 
?>

Thanks again

 

[GreGorGy]

$sql = "SELECT * FROM table WHERE datecol =< '".$_REQUEST['date_from']."' AND datecol >= '".$_REQUEST['date_to']."'; ";

Not to be a pedant but since that is the nature of this correction, between by definition excludes boundaries. A number between 1 and 4 is 2 or 4. A number from 1 to 4 is 1,2,3, or 4...

The point of course is taken. <= is > <. WTF?

 

[Other Pineapple Smurf]

Not to be a pedant but since that is the nature of this correction, between by definition excludes boundaries. A number between 1 and 4 is 2 or 4. A number from 1 to 4 is 1,2,3, or 4...

The point of course is taken. <= is > <. WTF?

How long you being coding?

Also your WHERE clause is so wrong you did not see me taking the p1ss out of you with addition and highlighting. Your original WHERE clause was selecting everything except the rows the OP wanted.

 

[greggpb]

Not to be a pedant but since that is the nature of this correction, between by definition excludes boundaries. A number between 1 and 4 is 2 or 4. A number from 1 to 4 is 1,2,3, or 4...

The point of course is taken. <= is > <. WTF?

I noticed that the fields used in the filter where "to" and "from", so the OP used both BETWEEN and FROM in the spec. BA's, sheez they never know what they want.

 

[GreGorGy]

How long you being coding?

Also your WHERE clause is so wrong you did not see me taking the p1ss out of you with addition and highlighting. Your original WHERE clause was selecting everything except the rows the OP wanted.

Touché
Since the 80s. And it was not my clause, it was stricken's. And the correction remains wrong (EDIT: was that your intention?).

$sql = "SELECT * FROM table WHERE datecol =< '".$_REQUEST['date_from']."' AND datecol >= '".$_REQUEST['date_to']."'; ";

If I read the above correctly,
* datecol is less than/equal to date_from means date is before or on
* datecol is greater than/equal to date_to means date is after or on

That above sql will select everything except the range required. What is wrong with:

$sql = "SELECT * FROM `Table` WHERE `DateCol` BETWEEN '".$_REQUEST['from']."' AND '".$_REQUEST['to']."'; ";