events system

[MSUK1]

i have an events script that displays upcoming events,

 

and i just add the event in a simple form, stores in database and shows on my control panel, and the clients control panel ( only clients events )

 

firstly what is the best way to sort by date? i enter the date into a text field like 12-04-11 but this when sorted doesnt appear in upcoming first?

 

then secondly how cani have it so, past events no longer show up so, if date = > today do not show?

[nethnet]

I always recommend storing dates in MySQL as DATETIME, or just DATE.  Lots of people just end up doing it as VARCHAR, but DATETIME is the best way to go.  This gives way to all of MySQL's built in features for handling them when extracting data, as well as (albeit not as useful) inputting data.

 

Then, when you go to do a SELECT statement, you can just ORDER BY your DATETIME field.  ASC would put oldest first, while DESC would put newest first.

 

SELECT * FROM `table` ORDER BY `date` DESC LIMIT 5

 

That would display the 5 latest events.

 

As far as hiding past events, you could use the handy UNIX_TIMESTAMP() function built into MySQL and just add a bit more to your SELECT statement.

 

SELECT *, UNIX_TIMESTAMP(`date`) AS `unixdate` FROM `table` WHERE `unixdate` > NOW() ORDER BY `unixdate` DESC LIMIT 5

[PFMaBiSmAd]

Your date format is ambiguous. That could either mean  April 11th of next year, December 4th of this year, or April 12th of this year. To a computer trying to sort it, it means April 11th of next year, because the 12, being the left-most digits, is the most significant field (the year part of a date) and the 11, being the right-most digits, is the least significant field (the day part of the date.)

 

A mysql DATE format is YYYY-MM-DD for several reasons, the most important being that it can be directly compared using greater-than/less-than comparisons, which also means that it can directly be sorted.

 

You need to use a DATE data type to hold your event dates in the database. You can convert your existing format into a YYYY-MM-DD value either using php code or you can use the mysql STR_TO_DATE() function directly in your queries. You can format a YYYY-MM-DD value back into your format when you retrieve the value using the mysql DATE_FORMAT() function directly in your queries.

[PFMaBiSmAd]

@nethnet, your description was fine up to the point of mentioning UNIX_TIMESTAMP(). Your query won't work because the NOW() function is a DATETIME value, not a unix timestamp.

 

You can compare a DATE data type with CURDATE() directly. You would compare a DATETIME data type with NOW()

[nethnet]

Thanks for pointing that out.  Reading back, I'm not even sure why I brought that up in the first place.

 

I guess that's a sign that it's bed time!

[MSUK1]

haha great feed back here guys! bed time, UK its lunch time

 

ok, a lot to digest here,

 

1) stop using varchar as data type.

 

using this data type will enable the SORT function to work the way i want?

 

do i now enter days YYYY-MM-DD into the database? and when running the select query just run as normal? but im confused as to how i get YYYY-MM-DD into DD-MM-YYYY ?

 

sorry for being fickle, and thanks very much for the help too

[dcro2]

For example if you use a DATE field, this would select events occurring starting today:

SELECT DATE_FORMAT(date, '%d-%m-%Y') AS date FROM events WHERE date >= CURDATE()

[MSUK1]

Thanks a lot guys, will Comment you in the code