From: rDubya <ryan.dubya@xxxxxxxxx>
Thanks for the help so far guys!!
Not helping though. I have the date contained in the database as timestamp
(YYYY-MM-DD HH:MM:SS).
Do you really need to pull events from the database which are not in your
range of interest? This will only slow down your processing time. Instead,
you could be looping over valid events and not using your incorrect
check_date function.
If you insist upon using a check_date function on the PHP side (which you
claimed to have worked in the past), on the format YYYY-MM-DD HH:MM:SS where
the first Y is at index 0, then substr($mysql_timestamp, 4, 2) is not the
month "MM", it is "-M". You need 5,2. Your other offsets are also wrong.
Rather than debugging your check_date function, you should just pull the
info you actually need from the database (even to the point of not selecting
*, and instead naming specific fields if you really don't need all fields),
and remove your check_date function. Then your while loop will loop over
only valid events.
Also, although this doesn't affect the running of your code, your variable
names show a misunderstanding of what parameters you are passing and
receiving.
$sql = "SELECT...";
$resultResource = mysql_query($sql);// or just $result = mysql_query($sql);
if($result != false){
while($event_data = mysql_fetch_assoc($result)) {
//Do something with a valid event instead of checking if it is valid
now on the PHP side.
}
}
Too bad you are not on MySQL 5, because the SQL query could have been even
closer to the phrasing you posted to the list "it is between now and three
weeks from now", similar to Mike's reply as:
$sql = "SELECT * FROM `EVENTS`
WHERE `event_city` = '" . $city . "'
AND `theEventDate` BETWEEN NOW() AND DATE_ADD( NOW(), INTERVAL 3 WEEK)
ORDER BY `theEventDate` ASC";
But for your MySQL version, WEEK is unavailable and you need INTERVAL 21 DAY
(watch the unit versus the expr; DAY not DAYS -- WEEK not WEEKS)
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-add
http://dev.mysql.com/doc/refman/4.1/en/comparison-operators.html
But if you are not familiar with BETWEEN or DATE_ADD, then the way I posted
is another way to skin this cat.
$sql = "SELECT * FROM `EVENTS`
WHERE `event_city` = '" . $city . "'
AND TO_DAYS( `theEventDate` ) >= TO_DAYS( NOW() )
AND TO_DAYS( `theEventDate` ) <= TO_DAYS( NOW() ) + 21
ORDER BY `theEventDate` ASC";
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_to-days
And while not trusting your indexing, rewrite short_date as:
function short_date ($mysql_timestamp) {
return date("D M j", $mysql_timestamp);
}
or this is probably a 1-liner in the while loop:
while...//I'm already using valid events due to my query
... date("D M j", $event_data['theEventDate']) ...
_________________________________________________________________
Test your celebrity IQ. Play Red Carpet Reveal and earn great prizes!
http://club.live.com/red_carpet_reveal.aspx?icid=redcarpet_hotmailtextlink2
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
