date problems

I'm having a problem with dates in php and mysql. I run a site that
promotes dated events and concerts and has the information for each
stored in a mysql database with the timestamp field.

Here is the function that checks the date of the event to ensure it is
between now and three weeks from now (only events in this time period
are displayed on a page, with all events being displayed on another
page)

function check_date ($mysql_timestamp, $days) {

$timestamp = mktime(0, 0, 0, substr($mysql_timestamp, 4, 2),
substr($mysql_timestamp, 6, 2), substr($mysql_timestamp, 0, 4));
$event_day = date("z", $timestamp);
$event_year = date("Y", $timestamp);
$actual_day = date("z");
$actual_year = date("Y");

while ($event_year > $actual_year) {
$event_day = $event_day + 365;
$event_year--;
}

if (($event_day - $actual_day) <= $days && $event_day >=
$actual_day) { return TRUE; }

else { return FALSE; }
}


Then, to display the events that fill this criteria, there is this
code for the date:

function short_date ($mysql_timestamp) {
$stimestamp = mktime(0, 0, 0, substr($mysql_timestamp, 4, 2),
substr($mysql_timestamp, 6, 2), substr($mysql_timestamp, 0, 4));
$sformatted_date = date("D M j", $stimestamp);
return $sformatted_date;
}


My problem is that I have events dated for Sep 2007 and on, and yet
they all come up as being on Dec 7 to 9, 2006.. any ideas?

rDubya

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: date problems

>From: rDubya
>My problem is that I have events dated for Sep 2007 and on, and yet
>they all come up as being on Dec 7 to 9, 2006.. any ideas?
>
>rDubya

How about having MySQL only return the events you are interested in?

SELECT yourEventFields FROM theTable
WHERE
TO_DAYS( theEventDate ) >= TO_DAYS( NOW() )
AND
TO_DAYS( theEventDate ) <= TO_DAYS( NOW() ) + 21

I like theEventDate to be in the format YYYY-MM-DD

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_to-days

____________________________________________________________ _____
A place for moms to take a break!
http://www.reallivemoms.com?ocid=TXT_TAGHM&loc=us

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: date problems

It's much better to use add_date instead of to_days since mysql isn't
smart enough to do it for you.
Such as:
SELECT yourEventFields FROM theTable
WHERE theEventDate BETWEEN now() AND date_add(now(), INTERVAL 21 DAYS;

This way mysql will calc the now() and date_add and will essentially
convert them to static values. If there's an index on theEventDate it
will be used.

Mike...

Instruct ICC wrote:
>> From: rDubya
>> My problem is that I have events dated for Sep 2007 and on, and yet
>> they all come up as being on Dec 7 to 9, 2006.. any ideas?
>>
>> rDubya
>
> How about having MySQL only return the events you are interested in?
>
> SELECT yourEventFields FROM theTable
> WHERE
> TO_DAYS( theEventDate ) >= TO_DAYS( NOW() )
> AND
> TO_DAYS( theEventDate ) <= TO_DAYS( NOW() ) + 21
>
> I like theEventDate to be in the format YYYY-MM-DD
>
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_to-days
>
>
> ____________________________________________________________ _____
> A place for moms to take a break!
> http://www.reallivemoms.com?ocid=TXT_TAGHM&loc=us
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: date problems

Argh, make sure you add the closing paren for the date_add since I
forgot it.

Mike...

Mike Gohlke wrote:
> It's much better to use add_date instead of to_days since mysql isn't
> smart enough to do it for you.
> Such as:
> SELECT yourEventFields FROM theTable
> WHERE theEventDate BETWEEN now() AND date_add(now(), INTERVAL 21 DAYS;
>
> This way mysql will calc the now() and date_add and will essentially
> convert them to static values. If there's an index on theEventDate it
> will be used.
>
> Mike...
>
> Instruct ICC wrote:
>>> From: rDubya
>>> My problem is that I have events dated for Sep 2007 and on, and yet
>>> they all come up as being on Dec 7 to 9, 2006.. any ideas?
>>>
>>> rDubya
>>
>> How about having MySQL only return the events you are interested in?
>>
>> SELECT yourEventFields FROM theTable
>> WHERE
>> TO_DAYS( theEventDate ) >= TO_DAYS( NOW() )
>> AND
>> TO_DAYS( theEventDate ) <= TO_DAYS( NOW() ) + 21
>>
>> I like theEventDate to be in the format YYYY-MM-DD
>>
>> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_to-days
>>
>>
>> ____________________________________________________________ _____
>> A place for moms to take a break!
>> http://www.reallivemoms.com?ocid=TXT_TAGHM&loc=us
>>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: date problems

------=_Part_2073_7329785.1189116339597
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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). The problem is that not only is it not displaying
events, but if I alter my code so that it displays ALL events, it shows the
events for the last year, and those upcoming, as all being in the first
couple weeks of December, 2006.

Here is the script in action: http://www.clubandpub.ca/lobby/?city=1

The events SHOULD display on the right column, under where it says events.
If you click to the GUIDE page, the calendar should be highlighted on the
dates where there are events, and the events in the coming weeks (as well as
concerts) should display below that (under their respective headings).

This DID work, but I recently switched hosting companies as the one I was
with has become increasingly unreliable after the company changed hands. My
new server uses PHP 5 server, with MySQL 4.1 (I'm not 100% sure what the old
was.. I think it was PHP 4, and while I think it was the same MySQL version,
it could have been an earlier one).

Cheers!

rDubya

also, here is the code that actually displays the events:


$db = mysql_connect("xx", "user", "pw) or error(mysql_error);
mysql_select_db("database", $db) or error(mysql_error);

$eventquery = mysql_query("SELECT * FROM `EVENTS` WHERE `event_city` =
'$city' ORDER BY `date` ASC", $db);

while($event_data = mysql_fetch_assoc($eventquery)) {

if (check_date($event_data["date"], 21) == TRUE) {
?>


it then displays the event information of the events within that time period
(pulled from the database, with the date being displayed using the
short_date function posted earlier)





On 9/6/07, Mike Gohlke wrote:
>
> Argh, make sure you add the closing paren for the date_add since I
> forgot it.
>
> Mike...
>
> Mike Gohlke wrote:
> > It's much better to use add_date instead of to_days since mysql isn't
> > smart enough to do it for you.
> > Such as:
> > SELECT yourEventFields FROM theTable
> > WHERE theEventDate BETWEEN now() AND date_add(now(), INTERVAL 21 DAYS;
> >
> > This way mysql will calc the now() and date_add and will essentially
> > convert them to static values. If there's an index on theEventDate it
> > will be used.
> >
> > Mike...
> >
> > Instruct ICC wrote:
> >>> From: rDubya
> >>> My problem is that I have events dated for Sep 2007 and on, and yet
> >>> they all come up as being on Dec 7 to 9, 2006.. any ideas?
> >>>
> >>> rDubya
> >>
> >> How about having MySQL only return the events you are interested in?
> >>
> >> SELECT yourEventFields FROM theTable
> >> WHERE
> >> TO_DAYS( theEventDate ) >= TO_DAYS( NOW() )
> >> AND
> >> TO_DAYS( theEventDate ) <= TO_DAYS( NOW() ) + 21
> >>
> >> I like theEventDate to be in the format YYYY-MM-DD
> >>
> >>
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_to-days
> >>
> >>
> >> ____________________________________________________________ _____
> >> A place for moms to take a break!
> >> http://www.reallivemoms.com?ocid=TXT_TAGHM&loc=us
> >>
> >
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

------=_Part_2073_7329785.1189116339597--

Re: date problems

>From: rDubya
>
>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-functio ns.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-functio ns.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_h otmailtextlink2

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: date problems

>From: "Instruct ICC"
>And while not trusting your indexing, rewrite short_date as:
My short_date rewrite was also wrong. So it looks like you will have to
learn those offsets for this function if you do it on the PHP side. But you
could also do it on the MySQL side.

____________________________________________________________ _____
Get a FREE small business Web site and more from Microsoft® Office Live!
http://clk.atdmt.com/MRT/go/aub0930003811mrt/direct/01/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: date problems

"This DID work, but I recently switched hosting companies..."

Is the new server in a different country with a different date format
/ time zone?

Just a thought ;-\

On 9/7/07, Instruct ICC wrote:
> >From: "Instruct ICC"
> >And while not trusting your indexing, rewrite short_date as:
> My short_date rewrite was also wrong. So it looks like you will have to
> learn those offsets for this function if you do it on the PHP side. But you
> could also do it on the MySQL side.
>
> ____________________________________________________________ _____
> Get a FREE small business Web site and more from Microsoft(r) Office Live!
> http://clk.atdmt.com/MRT/go/aub0930003811mrt/direct/01/
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--
Graham

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: date problems

WOW!! Thanks for all the help guys!! And Instruct ICC.. you're
solution for pulling the events did work.. but.. it turns out that
the solution was actually much simpler than I thought:

The old mysql database (once again, not sure what version) stored the
date as YYYYMMDDHHMMSS. The new database stores the date as
YYYY-MM-DD HH:MM:SS. All I had to do was adjust my code to pull only
the values and none of the delimeters (ie. "-", " ", and ":").
DUH!!!!!!!

But once again, thanks you guys for all the help!!!!


rDubya


On 9/7/07, Instruct ICC wrote:
> >From: "Instruct ICC"
> >And while not trusting your indexing, rewrite short_date as:
> My short_date rewrite was also wrong. So it looks like you will have to
> learn those offsets for this function if you do it on the PHP side. But you
> could also do it on the MySQL side.
>
> ____________________________________________________________ _____
> Get a FREE small business Web site and more from Microsoft(r) Office Live!
> http://clk.atdmt.com/MRT/go/aub0930003811mrt/direct/01/
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: date problems

you can mimic the old behaviour like so:
select TS, 1*TS as OldStyle;
(TS being your timestamp filed)

TS | OldStyle
2007-09-03 15:03:02 | 20070903150302


rDubya wrote:
>
> WOW!! Thanks for all the help guys!! And Instruct ICC.. you're
> solution for pulling the events did work.. but.. it turns out that
> the solution was actually much simpler than I thought:
>
> The old mysql database (once again, not sure what version) stored the
> date as YYYYMMDDHHMMSS. The new database stores the date as
> YYYY-MM-DD HH:MM:SS. All I had to do was adjust my code to pull only
> the values and none of the delimeters (ie. "-", " ", and ":").
> DUH!!!!!!!
>
> But once again, thanks you guys for all the help!!!!
>
>
> rDubya
>
>
> On 9/7/07, Instruct ICC wrote:
>> >From: "Instruct ICC"
>> >And while not trusting your indexing, rewrite short_date as:
>> My short_date rewrite was also wrong. So it looks like you will have to
>> learn those offsets for this function if you do it on the PHP side. But
>> you
>> could also do it on the MySQL side.
>>
>> ____________________________________________________________ _____
>> Get a FREE small business Web site and more from Microsoft(r) Office
>> Live!
>> http://clk.atdmt.com/MRT/go/aub0930003811mrt/direct/01/
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>

--
View this message in context: http://www.nabble.com/date-problems-tf4393599.html#a12634947
Sent from the Php - Database mailing list archive at Nabble.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php