Comparing dates to datetime timestamp

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
srefer
Forum Newbie
Posts: 6
Joined: Thu Mar 12, 2009 12:56 pm

Comparing dates to datetime timestamp

Post by srefer »

Hi,
I have a form that has a user enter in a start date and an end date. I want to query the database and retrieve any rows whose timestamp is between these two dates. However, my query below is not working. I think it's because my timestamp is a datetime data type and my php variables are strings. Is there another way to go about this? Thanks for any help!! My code is below:

<?php
$link = mysql_connect('localhost', 'root', 'root');
if (!$link){
die('Could not connect' . mysql_error());
}
echo 'Connected Successfully <br />';
$selectdb = mysql_select_db ('chfa', $link);
if(!$selectdb){
die('Could not select database' . mysql_error());
echo 'Did not select database';
}
echo 'Database Selected <br />';

$startMonth = $_GET["startMonth"];
$startDay = $_GET["startDay"];
$startYear = $_GET["startYear"];
$endMonth = $_GET["endMonth"];
$endDay = $_GET["endDay"];
$endYear = $_GET["endYear"];

$startDate = $startYear . "-" . $startMonth . "-" . $startDay . " 00:00:00";

echo $startDate."\n";

$endDate = $endYear . "-" . $endMonth . "-" . $endDay . " 00:00:00";

echo $endDate;

$result="SELECT * FROM task1 WHERE datetime BETWEEN $startDate AND $endDate";

echo "<table border='1'>
<tr>
<th>Name</th>
<th>Phone Number</th>
<th>Location</th>
<th>Department</th>
<th>Equipment</th>
<th>Is There a Work-Around?</th>
<th>Detailed Description</th>
<th>Ticket Status</th>
<th>Date Submitted</th>
</tr>";

while($row = mysql_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['phone'] . "</td>";
echo "<td>" . $row['location'] . "</td>";
echo "<td>" . $row['department'] . "</td>";
echo "<td>" . $row['probdescription'] . "</td>";
echo "<td>" . $row['workaround'] . "</td>";
echo "<td>" . $row['detaileddescription'] . "</td>";
echo "<td>" . $row['openclosedpending'] . "</td>";
echo "<td>" . $row['datetime'] . "</td>";
echo "</tr>";
}


mysql_close($link)
?>
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Comparing dates to datetime timestamp

Post by Bill H »

Try using sprintf("%d-%02d-%02d",$_GET["startYear"],$_GET["startMonth"],$_GET["startDay"]) to build your query instead of merely concatenating the vars. You may be getting single digit entries.
Post Reply