Hi,
I've developed a simple search engine for a site I'm working on. Its
purpose is to display community events based on the users input criteria.
The problem is, if I use the DATE_FORMAT command to alter the appearance of
the events dates and times - then MySQL begins at the top of the list using
the exact order of dates and times, rather than listening to the initial
query and taking only the relevant dates and times.
However, if I leave out the DATE_FORMAT and TIME_FORMAT commands, it works
perfectly, just without the nice friendly display. Any thoughts?
Here is a sample of my script:
if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;
}
$query = "select * from comcal2001 where ".$searchtype." like
'%".$searchterm."%'";
$result = mysql_query($query, $db);
$resultdate = mysql_query ("SELECT DATE_FORMAT(EventStartDate, '%W %M
%D %Y') AS EventStartDate FROM comcal2001");
$resulttime = mysql_query ("SELECT TIME_FORMAT(EventStartTime, '%r')
AS EventStartTime FROM comcal2001");
if (!$result)
echo "Error running query: $query<br>".mysql_error($db);
else
$num_results = mysql_num_rows($result);
$num_rows = mysql_numrows($result);
echo "<p>Number of Events found: ".$num_rows."</p>";
for ($i=0; $i < $num_rows; $i++)
{
$row = mysql_fetch_array($result);
echo "<p><b>".($i+1).". Event: ";
echo ($row["EventName"]);
echo "</b><br>Month: ";
echo ($row["EventMonth"]);
echo "<br>Location: ";
echo ($row["EventLocation"]);
echo "<br>City: ";
echo ($row["EventCity"]);
echo ", ";
echo ($row["EventState"]);
echo "<br>Time: ";
$rowtime = mysql_fetch_array($resulttime);
echo ($row["EventStartTime"]);
echo "<br>Date: ";
$rowdate = mysql_fetch_array($resultdate);
echo ($row["EventStartDate"]);
echo "<br>Description: ";
echo ($row["EventDescription"]);
echo "<br>Cost: ";
echo ($row["EventCost"]);
echo "</p>";
}
?>
Thanks
-Tim
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
