Ordering MySQL Files by Date

cdherold

hey php fans,

I need to know how to retrieve MySQL files in order by date ... not the date that the information is entered, but a user specified date .... the date of the conference.

Let's say that users are entering the date of future conferences.

What format do I need the user to enter the date of the conference in to be able to fetch the file from MySQL in an ordered list?

And how do I call that ordered list?

thanks,

cdherold

scottd

If you enter the date into Mysql using the format yyyy-mm-dd then you will be able to sort the records by this field simply by going ORDER BY field_name.

cheers,
Scott ~D

reeferd

Alternatively, you can create a 2 char long input text box to enter day, a dropdown for month and a dropdown for year, and then you can organize these inputs into YYYY-MM-DD for compatability with mysql...

Hope this helps...?

[deleted]

And then you can add a routine to check if it's a valid date.

I think having people type the date is much faster for the user.

cdherold

how would you write a routine to check the validity of the date?

cdherold

reeferd

Think about it!
If the user is to write the whole date in manually, it will most certainly open for a whole lot of "error-typing" and mis-spelling.

I am convinced that the best solution is to force the user to type the date in the correct format... Or, at least, dividing the input for the user.

Remember, you don`t HAVE to use dropdown.
But you should divide the input into separat input-boxes.

[YYYY]-[MM]-[DD] [hh]:[mm]:[ss]

Then you can reconstruct the input boxes to suit the most common way of inputting dates.
Because that is different from country to country:
[YYYY]-[DD]-[MM]
or
[MM]-[DD]-[YYYY]

Another thingy:
If you go for one input box, then you`ll propably have to write lotsa code to reconstuct the date into the correct format...

[deleted]

Actually no. All you have to do is tell the user to input in the correct format.

If you go for a format that is common, people won't have too much trouble with it.

Once you have told users to use yyyy-mm-dd,
it's completely peanuts to turn that into a "real" date.

But I agree it would be good to have seperate input boxes, along with some javascript to move from box to box and to check if any illegal date has been entered.

As for bow to check it's a real date, just feed it to a function like checkdate()

Anon

You can also include the time as well in the format:
YYY-MM-DD H:M:S

and it will sort it for you.

What I would like to ask though is how can I change the order form decending to ascending and vise versa?

I've tried this but it's just a case of getting the correct syntax

SELECT * FROM temp ORDER BY date <decending>?

cheers for any help.

petrb

SELECT * FROM temp ORDER BY date DESC

--ph

Anon

ahh, thankyou!

i've noticed that mysql returns the date in the format of
YYYY-MM-DD HH:MM:SS
even if I were to insert the date data as
YYYY-MM-DD-HH-MM-SS

I think this could cause a problem for me unless there is another way to get around it.
You see when I retrieve the date from the database I wish to split the numbers into individual values using:

list ($year, $month, etc) = explode (
(", ", $date);

which I then apply 'mktime' to convert to unix timestamp.

but if mysql returns both '-' and ':' how can I split them into individual values?
I know there must be a way, but i'm only a PHP beginner.

Thankyou very much for any help.
Much appreciated!

petrb

You said it twice. Look at the split() function.

--ph

Anon

Thankyou, your help is much appreciated 🙂