Re: [PHP] sort by date
Thanks Richard. That did the trick. Now to the DB fixing business... -Will Richard Lynch [EMAIL PROTECTED] kirjoitti viestissä:[EMAIL PROTECTED] On Thu, May 5, 2005 10:42 pm, William Stokes said: I made a mistake and stored date information to DB as varchar values (dd.mm.yyy). When I read the DB is it still possible to sort the data by date with SQL query (ORDER BY date ASC)? Or is it nessessary to have the date information to be stored as a date in the DB? Will it work or is the output going to be sorted randomly? As noted, your best solution is to fix the database. A short-term hack would be to: order by substring(your_field, 6, 4), substring(your_Field, 3, 2), substring(your_field, 1, 2) Read the MySQL manual to check the actual numbers for substring in MySQL -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sort by date
On Thu, May 5, 2005 10:42 pm, William Stokes said: I made a mistake and stored date information to DB as varchar values (dd.mm.yyy). When I read the DB is it still possible to sort the data by date with SQL query (ORDER BY date ASC)? Or is it nessessary to have the date information to be stored as a date in the DB? Will it work or is the output going to be sorted randomly? As noted, your best solution is to fix the database. A short-term hack would be to: order by substring(your_field, 6, 4), substring(your_Field, 3, 2), substring(your_field, 1, 2) Read the MySQL manual to check the actual numbers for substring in MySQL -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sort by date
William Stokes wrote: Hello, I made a mistake and stored date information to DB as varchar values (dd.mm.yyy). When I read the DB is it still possible to sort the data by date with SQL query (ORDER BY date ASC)? Or is it nessessary to have the date information to be stored as a date in the DB? Will it work or is the output going to be sorted randomly? It would be sorted by day first, then month, then year. Add another column (DATE type) to the table and update it from the current column using SUBSTRING() function. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sort by date
On Friday 06 May 2005 08:42, William Stokes wrote: Hello, I made a mistake and stored date information to DB as varchar values (dd.mm.yyy). When I read the DB is it still possible to sort the data by date with SQL query (ORDER BY date ASC)? Or is it nessessary to have the date information to be stored as a date in the DB? Will it work or is the output going to be sorted randomly? All RDBMS have date formatting functions. Use them. Thanks -Will -- Cyberly yours, Petar Nedyalkov Devoted Orbitel Fan :-) PGP ID: 7AE45436 PGP Public Key: http://bu.orbitel.bg/pgp/bu.asc PGP Fingerprint: 7923 8D52 B145 02E8 6F63 8BDA 2D3F 7C0B 7AE4 5436 pgpLxsPbcV9bg.pgp Description: PGP signature
Re: [PHP] sort by date
OK. I found that out from MySQL manual. I just don't know how to insert the date formatting function to the query: I quessI need to put STR_TO_DATE() in to the following SQL query: SELECT event_id,name,date,time,place,type,info FROM test_table WHERE (group = '$group') AND (type = 'Game' OR type = 'training') ORDER BY date ASC I dont know how? Thanks a lot -Will -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sort by date
On Friday 06 May 2005 11:17, William Stokes wrote: OK. I found that out from MySQL manual. I just don't know how to insert the date formatting function to the query: I quessI need to put STR_TO_DATE() in to the following SQL query: What about DATE_FORMAT()? SELECT event_id,name,date,time,place,type,info FROM test_table WHERE (group = '$group') AND (type = 'Game' OR type = 'training') ORDER BY date ASC I dont know how? Thanks a lot -Will -- Cyberly yours, Petar Nedyalkov Devoted Orbitel Fan :-) PGP ID: 7AE45436 PGP Public Key: http://bu.orbitel.bg/pgp/bu.asc PGP Fingerprint: 7923 8D52 B145 02E8 6F63 8BDA 2D3F 7C0B 7AE4 5436 pgpogR2tuhWB3.pgp Description: PGP signature
Re: [PHP] sort by date
William Stokes wrote: OK. I found that out from MySQL manual. I just don't know how to insert the date formatting function to the query: I quessI need to put STR_TO_DATE() in to the following SQL query: SELECT event_id,name,date,time,place,type,info FROM test_table WHERE (group = '$group') AND (type = 'Game' OR type = 'training') ORDER BY date ASC I dont know how? something like; select my_date as STR_TO_DATE(date)... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
