By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,018 Members | 1,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,018 IT Pros & Developers. It's quick & easy.

MySQL sort by date

P: n/a
I have a datebase of events. I have a form that adds to it, and I have a
form that removes from it (with checkboxes). And I have a php file that
will view all the events, either all of them or just the events happening
after "today." These events are added randomly, and I want my php file that
views the events to display the results in order of when they are happening.
Any suggestions? I have seen a CREATE VIEW query that looks promising. Is
that a good way to do it, just to create an on-the-fly table that is sorted
and display the reults from that one?

Thanks,

Paul
Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"SELECT * FROM table_name ORDER BY time_field;"

of course this supposes that your "time_field" is in the form of a
mysql-readable time format. if it isnt, you may have to convert it with
mysql/php's time/date functions.

- JP

"Paul Brown" <gt*****@mail.gatech.edu> wrote in message
news:c6**********@news-int.gatech.edu...
I have a datebase of events. I have a form that adds to it, and I have a
form that removes from it (with checkboxes). And I have a php file that
will view all the events, either all of them or just the events happening
after "today." These events are added randomly, and I want my php file that views the events to display the results in order of when they are happening. Any suggestions? I have seen a CREATE VIEW query that looks promising. Is that a good way to do it, just to create an on-the-fly table that is sorted and display the reults from that one?

Thanks,

Paul

Jul 17 '05 #2

P: n/a
I noticed that Message-ID: <6Xahc.24291$hw5.38560@attbi_s53> from
kingofkolt contained the following:
"SELECT * FROM table_name ORDER BY time_field;"

of course this supposes that your "time_field" is in the form of a
mysql-readable time format. if it isnt, you may have to convert it with
mysql/php's time/date functions.


Indeed.

I have a database where dates are stored in MySql date format. To get
the next event I do

$result = mysql_query("SELECT UNIX_TIMESTAMP(date) AS unixdate FROM
tblDates WHERE date > now() ORDER BY 'unixdate'" ,$db);
$myrow = mysql_fetch_array($result);
$num_rows = mysql_num_rows($result);
$RealDate = date("F jS, Y", $myrow["unixdate"]);

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #3

P: n/a
In message <c6**********@news-int.gatech.edu>, Paul Brown
<gt*****@mail.gatech.edu> writes
I have a datebase of events. I have a form that adds to it, and I have a
form that removes from it (with checkboxes). And I have a php file that
will view all the events, either all of them or just the events happening
after "today." These events are added randomly, and I want my php file that
views the events to display the results in order of when they are happening.
Any suggestions? I have seen a CREATE VIEW query that looks promising. Is
that a good way to do it, just to create an on-the-fly table that is sorted
and display the reults from that one?
If you just have a DATE in each row, an AUTOINCREMENT column would give
you a way of being quite sure which order they were added in.

You are getting a random result I believe as when a row is deleted from
a table, the space is not 'close up' but simply marked for re-use and if
there is no ORDER BY on the select clause it is presumably scanning the
table and presenting the results in the order it gets them in. At least
this happens with Informix. ;-)
Thanks,

Paul


--
Five Cats
Email to: cats_spam at uk2 dot net
Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.