MySQL sort by date

[Paul Brown]

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

[kingofkolt]

Re: MySQL sort by date

"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" <gtg868h@mail.g atech.edu> wrote in message
news:c63dgu$gl3 $1@news-int.gatech.edu. ..[color=blue]
> 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[/color]
that[color=blue]
> views the events to display the results in order of when they are[/color]
happening.[color=blue]
> Any suggestions? I have seen a CREATE VIEW query that looks promising.[/color]
Is[color=blue]
> that a good way to do it, just to create an on-the-fly table that is[/color]
sorted[color=blue]
> and display the reults from that one?
>
> Thanks,
>
> Paul
>
>[/color]

[Geoff Berrow]

Re: MySQL sort by date

I noticed that Message-ID: <6Xahc.24291$hw 5.38560@attbi_s 53> from
kingofkolt contained the following:
[color=blue]
>"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.[/color]

Indeed.

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

$result = mysql_query("SE LECT UNIX_TIMESTAMP( date) AS unixdate FROM
tblDates WHERE date > now() ORDER BY 'unixdate'" ,$db);
$myrow = mysql_fetch_arr ay($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/

[Five Cats]

Re: MySQL sort by date

In message <c63dgu$gl3$1@n ews-int.gatech.edu> , Paul Brown
<gtg868h@mail.g atech.edu> writes[color=blue]
>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?[/color]

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. ;-)[color=blue]
>
>Thanks,
>
>Paul
>
>[/color]

--
Five Cats
Email to: cats_spam at uk2 dot net