Search between two dates

[garyneedham7202]

Hi Can anyone please help?

 

I want to search between two dates from a date opened I have the following code but sadly is not working

 

 

 

 

$sql = "SELECT * FROM Log WHERE Date_Opened = '" BETWEEN '" & Date_1 & "' AND '" & Date_2 & "'" ORDER BY DateOpened DESC ";

 

if (empty($_GET['Date_Opened']))

$sql = "SELECT * FROM Date_opened WHERE 1 = 2 ";

$res = mysql_query($sql);

 

 

 

 

<form>

Start Date:<input name="Date_1" type="Date" value="<%=Date_1%>" size="15" />

End Date:<input name="Date_2" type="Date" value="<%=Date_2%>" size="15" />

<input name="Submit" type="submit" />

</form>

[mweldan]

assumed the only field you want to query is Date_Opened,

i think something wrong with your sql query, it probably something like this:

 

select * from log where date_opened > $date_1 and date_opened < $date_2 order by date_opened desc; 

[AyKay47]

Please post the actual relevant code. The above can't be the actual code you are using.

[Psycho]

$query = "SELECT *
   FROM Log
   WHERE Date_Opened BETWEEN '{$Date_1}' AND '{$Date_2}'
   ORDER BY DateOpened DESC ";

[garyneedham7202]

Thanks I have changed to the following but still will not run the search

 

 

$sql = "select * from log where DateOpened > $date_1 and DateOpened < $date_2 order by DateOpened desc; ";

if (empty($_GET['DateOpened']))

$sql = "SELECT * FROM DateOpened WHERE 1 = 2 ";

$res = mysql_query($sql);

 

 

 

<form>

<span class="ContentStyle">Start date </span><span class="style4"> </span>  

<input name="date_1" type="date" value="<%=date_1%>" size="15" />

<span class="ContentStyle">End date </span><span class="style4"> </span>  

<input name="date_2" type="date" value="<%=date_2%>" size="15" />

<input name="submit" type="submit" value="Search">

<span></span>

</form>

 

 

 

<?php

while ($r = mysql_fetch_assoc($res)) {

?>

[mweldan]

sorry i think my example was wrong, please see Psycho reply instead.

 

other than that, the date format for search query should be same with what you store into database.

[Barand]

$sql = "SELECT * FROM DateOpened WHERE 1 = 2 ";

 

Utter waste of time

 

 

What formats are DateOpened, $date_1 and $date_2?

Edited February 20, 2013 by Barand

[Psycho]

What does " . . . will not run the search" mean? Are you getting errors? Are you even checking for errors (and the answer is no)? Why are you using > and < comparisons when you asked for help and were given examples to use the BETWEEN operator? Is the date field int he database an appropriate "type" for a date value: e.g. date, datetime, or timestamp? What are the values of $date_1 and $date_2?

 

Also, you would always get NO results if $_GET['DateOpened'] is empty based on that logic because you have the where clause as

 WHERE 1 = 2

What is the purpose of that? If you don't want to run the query - then don't run it. Don't create a query that you know will never return results.

[Psycho]

<?php

$query = "SELECT *
	  FROM log
	  WHERE DateOpened BETWEEN $date_1 AND $date_2
	  ORDER DateOpened DESC";
$result = mysql_query($query);

if(!$result)
{
   //There was a database error
   echo "Query: $query<br>Error: " . mysql_error();
}
elseif(!mysql_num_rows($result)
{
   echo "There were no results";
}
else
{
   while($row = mysql_fetch_assoc($result))
   {
    //Do something with the data

   }
}

?>

[garyneedham7202]

Thanks for the feedback I'm getting this result

 

 

Server error

The website encountered an error while retrieving http://www./search_between_dates.php. It may be down for maintenance or configured incorrectly.

Here are some suggestions:

• Reload this webpage later.
  

HTTP Error 500 (Internal Server Error): An unexpected condition was encountered while the server was attempting to fulfill the request.

 

The text below is a copy of the complete page

 

 

 

 

 

<?php

include("includes/db.config.php");

 

 

 

if(checkuser()){

 

 

 

$query = "SELECT *

FROM log

WHERE DateOpened BETWEEN $date_1 AND $date_2

ORDER DateOpened DESC";

$result = mysql_query($query);

 

if(!$result)

{

//There was a database error

echo "Query: $query<br>Error: " . mysql_error();

}

elseif(!mysql_num_rows($result)

{

echo "There were no results";

}

else

{

while($row = mysql_fetch_assoc($result))

{

echo "results"; //Do something with the data

 

}

}

 

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd">

<html>

<!-- DW6 -->

<head>

<title>Test</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<meta name="Robots" content="all">

<meta name="Revisit-After" content="3 days">

<meta name="keywords" content="Automation">

<meta name="description" content="Automation">

<meta name="Author" content="Gary Needham">

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<link rel="stylesheet" href="css/test.css" type="text/css">

<!--Start config -->

<?php

$include_top_right_links = true;

$include_header = true;

$include_banner = true;

$include_top_links = false;

$include_top_links_data = true;

$include_left_box_spacer = true;

$include_right_box_spacer = false;

$include_left_box = false;

$include_right_box = false;

$include_footer = true;

$include_left_box_blank= true;

?>

<!--End config -->

<!--Start top right Div -->

<?php

if ($include_top_right_links

&& file_exists("ocado/add_top_right_links.php")) {

include("ocado/add_top_right_links.php");

}

?>

<!--End top right links Div -->

<!--Start Header Div -->

<?php

if ($include_header

&& file_exists("ocado/add_header.php")) {

include("ocado/add_header.php");

}

?>

<!--End Header Div -->

<!--Start spacer Div -->

<?php

if ($include_banner

&& file_exists("ocado/add_banner.php")) {

include("ocado/add_banner.php");

}

?>

<!--Send spacers Div -->

<!--Start Top links Div -->

<?php

if ($include_top_links

&& file_exists("ocado/add_links_top.php")) {

include("ocado/add_links_top.php");

}

?>

<!--End Top links Div -->

<!--Start Top links data Div -->

<?php

if ($include_top_links_data

&& file_exists("ocado/add_links_top_data.php")) {

include("ocado/add_links_top_data.php");

}

?>

<!--End Top links data Div -->

<!--Start Left links Spacer Div -->

<?php

if ($include_left_box_spacer

&& file_exists("ocado/add_box_left_spacer.php")) {

include("ocado/add_box_left_spacer.php");

}

?>

<!--End Left links Spacer Div -->

<!--Start Left box Div -->

 

<?php

if ($include_left_box

&& file_exists("/ocado/add_box_left.php")) {

include("/ocado/add_box_left.php");

}

?>

<!--End Left box Div -->

<!--Start Left box Blank Div -->

 

<?php

if ($include_left_box_blank

&& file_exists("ocado/add_left_links_blank.php")) {

include("ocado/add_left_links_blank.php");

}

?>

 

<!--End Left box Blank Div -->

</head>

<body>

<div id="body"class="body_text">

 

<table width="661" border="0">

<tr>

<td colspan="2"><form>

<span class="ContentStyle">Start date </span><span class="style4"> </span>  

<input name="date_1" type="date" value="<%=date_1%>" size="15" />

<span class="ContentStyle">End date </span><span class="style4"> </span>  

<input name="date_2" type="date" value="<%=date_2%>" size="15" />

<input name="submit" type="submit" value="Search">

<span></span>

</form></td>

</tr>

 

<tr bgcolor="#EAEAEA">

<td> </td>

<td><div align="center"><a href="ocado_edit_my_log.php?id=<?=$r['id']?>">Edit</a> |

<a href="singlelog.php?id=<?=$r['id']?>">View / Print </a></div></td>

<tr>

<td width="533"><strong>PLC</strong></td>

<td width="118"> </td>

</tr>

<tr>

<td><a href="singlelog.php?id=<?=$r['id']?>"><?=$r['Description']?></a></td>

<td> </td>

</tr>

<tr>

<td><strong>Raised by </strong></td>

<td> </td>

</tr>

<tr>

<td><?=$r['Raised_by']?></td>

<td> </td>

</tr>

<tr>

<td><strong>Block</strong></td>

<td> </td>

</tr>

<tr>

<td><?=$r['Block']?></td>

<td> </td>

</tr>

<tr>

<td><strong>Date Raised</strong></td>

<td> </td>

</tr>

<tr>

<td><?=$r['DateOpened']?></td>

<td> </td>

</tr>

<tr>

<td><strong>Date closed </strong></td>

<td> </td>

</tr>

<tr>

<td><?=$r['DateClosed']?></td>

<td> </td>

</tr>

<tr>

<td><strong>Description of problem </strong></td>

<td> </td>

</tr>

<tr>

<td><a href="singlelog.php?id=<?=$r['id']?>">

<?=$r['Description']?>

</a></td>

<td> </td>

</tr>

<tr>

<td><strong>Equipment</strong></td>

<td> </td>

</tr>

<tr>

<td><?=$r['Equipment']?></td>

<td> </td>

</tr>

</tr>

<tr bgcolor="#000040">

<td height="1" colspan="2"></td>

</tr>

 

</table>

 

 

 

 

 

 

 

</div>

</body>

<!--Start Right links Spacer Div -->

<?php

if ($include_right_box_spacer

&& file_exists("ocado/add_box_right_spacer.php")) {

include("ocado/add_box_right_spacer.php");

}

?>

<!--End Right links Spacer Div -->

 

<!--Start Right links Div -->

<?php

if ($include_right_box

&& file_exists("ocado/add_box_right.php")) {

include("ocado/add_box_right.php");

}

?>

<!--End Right links Div -->

 

<!--Start Footer Div -->

<?php

if ($include_footer

&& file_exists("ocado/add_footer.php")) {

include("ocado/add_footer.php");

}

?>

<!--End Footer Div -->

<?php

 

}else{

header("Location:asd.php");

}

?>

 

</html>

[Psycho]

That error is either a problem with the web server or their could be some significant error in the script that is causing the web server to choke. But,the script is a complete mess - so I'm not going to fix it.

 

1. Why do you define a bunch of variables as TRUE and then have if() condition checks later to output content. Create a separate script to output the content based upon those variables. Then, for your pages, define the variables and include that script.

 

2. You have a lot of code after the closing body tag (</body>) which outputs more content to the page.

 

3. At the very end you have this

header("Location:asd.php");

 

You cannot send headers to the page after you have sent any content to the browser. This may be why you are getting the error above.

Edited February 21, 2013 by Psycho