Re: [PHP] Help for code to 'sort list' please
On 02-Feb-2004 EastLothianDirectory wrote: Was just reading your previous reply and my months are indeed in text and therein lies the problem. snip Look at the MySQL functions FIELD() and/or FIND_IN_SET(). Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help for code to 'sort list' please
EastLothianDirectory wrote: I have the following sql query in my Php code: $sql = SELECT id, date_day, date_month, date_year, title, content FROM $table_name ORDER BY date_year desc; The rest of the php code is just a simple db connection with a bullet list and 'while' loop with an 'echo result' request. This shows a list of dates with latest year on top of list. I have Select/Option boxes to chose day,month,year etc to fill in Php form to update database. This is result of sql query: 2-August-2007 1-January-2007 30-December-2007 8-July-2006 29-February-2006 3-March-2006 16-February-2005 1-January-2004 Although it lists in year order descending (in fact what I asked it to do) I would like to learn to sort the months and days into desc order properly too. Can anyone suggest how I achieve this with php code. Thank you in advance Let the db do this. ORDER BY -- By-Tor.com It's all about the Rush http://www.by-tor.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help for code to 'sort list' please
Thank you for reply. I already have 'ORDER BY year desc'. But as I mentioned how do I get the months and day in order too on the same list. Thank you in advance Original Message From: John Nichel Date: Mon 2/2/04 19:31 To: EastLothianDirectory Cc: [EMAIL PROTECTED] Subject:Re: [PHP] Help for code to 'sort list' please EastLothianDirectory wrote: I have the following sql query in my Php code: $sql = SELECT id, date_day, date_month, date_year, title, content FROM $table_name ORDER BY date_year desc; The rest of the php code is just a simple db connection with a bullet list and 'while' loop with an 'echo result' request. This shows a list of dates with latest year on top of list. I have Select/Option boxes to chose day,month,year etc to fill in Php form to update database. This is result of sql query: 2-August-2007 1-January-2007 30-December-2007 8-July-2006 29-February-2006 3-March-2006 16-February-2005 1-January-2004 Although it lists in year order descending (in fact what I asked it to do) I would like to learn to sort the months and days into desc order properly too. Can anyone suggest how I achieve this with php code. Thank you in advance Let the db do this. ORDER BY -- By-Tor.com It's all about the Rush http://www.by-tor.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help for code to 'sort list' please
EastLothianDirectory wrote: Thank you for reply. I already have 'ORDER BY year desc'. But as I mentioned how do I get the months and day in order too on the same list. Thank you in advance snip Read the documentation for ORDER BY in MySQL. You can sort on multiple columns. Basically, you can 'ORDER BY year, month, day DESC' (my syntax may be off), and it order it by the year first, then like years will be ordered by month, etc. -- By-Tor.com It's all about the Rush http://www.by-tor.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help for code to 'sort list' please
Chris, It looks like your data in date_year is not in a format that supports a simple DESC sort. Look into getting the column formatted in -mm-dd order (see the mysql manual for this). Read the data in your column, format it, pump it back into the database, then change the column type to 'date'. Of course, you'll also need to examine any new inputs and get them correctly formated before they're added to the database. hugh - Original Message - From: EastLothianDirectory [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 02, 2004 11:18 AM Subject: [PHP] Help for code to 'sort list' please I have the following sql query in my Php code: $sql = SELECT id, date_day, date_month, date_year, title, content FROM $table_name ORDER BY date_year desc; The rest of the php code is just a simple db connection with a bullet list and 'while' loop with an 'echo result' request. This shows a list of dates with latest year on top of list. I have Select/Option boxes to chose day,month,year etc to fill in Php form to update database. This is result of sql query: 2-August-2007 1-January-2007 30-December-2007 8-July-2006 29-February-2006 3-March-2006 16-February-2005 1-January-2004 Although it lists in year order descending (in fact what I asked it to do) I would like to learn to sort the months and days into desc order properly too. Can anyone suggest how I achieve this with php code. Thank you in advance -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help for code to 'sort list' please
EastLothianDirectory wrote: I have the following sql query in my Php code: $sql = SELECT id, date_day, date_month, date_year, title, content FROM $table_name ORDER BY date_year desc; ... Although it lists in year order descending (in fact what I asked it to do) I would like to learn to sort the months and days into desc order properly too. Can anyone suggest how I achieve this with php code. I would do it in the SQL the same way as you have sorted on 'date_year' e.g.: $sql = SELECT id, date_day, date_month, date_year, title, content FROM $table_name ORDER BY date_year, date_month desc; this assumes you have a numerical value stored for 'date_month', otherwise you will have the months sorted alphabetically which probably does not help. (my gut feeling is that storing the names of the month in the database is probably not the best way to go about it - have you looked at datetime/timestamp fields?) BTW you can also mix DESC/ASC sorting (although this probably does not make sense to do when considering ordering by 'date' data) e.g.: $sql = SELECT id, date_day, date_month, date_year, title, content FROM $table_name ORDER BY date_year ASC, date_month desc; the lowdown on sorting in MySQL can be found here: http://www.mysql.com/doc/en/Sorting_rows.html more info on datetime fields in MySQL can be found here: http://www.mysql.com/doc/en/DATETIME.html and here: http://www.mysql.com/doc/en/Date_calculations.html --- alernatively familiarize yourself with PHPs array sorting functions - e.g. array_multi_sort() (I may have spelt that function incorrectly!), but I think that it will be alot more hassle to set up an multilevel array with a second/third array for mapping the month names (assuming the 'date_month' field contains text) to numbers for sorting purposes than using the DB beforehand. Thank you in advance -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help for code to 'sort list' please
Thank you for replying again but I have already been on the Mysql.com website and although I did ascertain the correct syntax ie ORDER BY date_day, date_month, date_year desc ORDER BY date_day, date_year, date_month desc ORDER BY date_month, date_day, date_year desc ORDER BY date_month, date_year, date_day desc ORDER BY date_year, date_day, date_month desc ORDER BY date_year, date_month, date_day desc None of them work mainly because the months get listed alphabetically and not IN ORDER. For example AUGUST is listed as the first month because it started with 'A' The year and day being integers were listed correctly. Now do you see the problem. This is why Mysql.com was no good so I came to PHP for help. Thank you in advance to anyone ?? that can help. Original Message From: John Nichel Date: Mon 2/2/04 19:43 To: [EMAIL PROTECTED] Subject:Re: [PHP] Help for code to 'sort list' please EastLothianDirectory wrote: Thank you for reply. I already have 'ORDER BY year desc'. But as I mentioned how do I get the months and day in order too on the same list. Thank you in advance snip Read the documentation for ORDER BY in MySQL. You can sort on multiple columns. Basically, you can 'ORDER BY year, month, day DESC' (my syntax may be off), and it order it by the year first, then like years will be ordered by month, etc. -- By-Tor.com It's all about the Rush http://www.by-tor.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help for code to 'sort list' please
refering to my previous mail: I really don't think its a good idea to store the months as names in the DB (I recommend a datetime field to store the complete date info). but if you must then you will have to define an array which you can use as a sort key e.g. array( 1 = 'January', 2 = 'February', ...etc... ); and use something like this is combination with a multisort array function to get the order you want - but the way I see it your better off changing the DB schema and using a few date related function is either MySQL or PHP to display the dates in a user friendly way (e.g. '10 January 2004'). EastLothianDirectory wrote: Thank you for replying again but I have already been on the Mysql.com website and although I did ascertain the correct syntax ie ... None of them work mainly because the months get listed alphabetically and not IN ORDER. For example AUGUST is listed as the first month because it started with 'A' The year and day being integers were listed correctly. Now do you see the problem. This is why Mysql.com was no good so I came to PHP for help. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help for code to 'sort list' please
Was just reading your previous reply and my months are indeed in text and therein lies the problem. I will have to completely rewrite the online form which will add a(mysql type) date to add it and all the fields to the DB. I will research the 'PHPs array sorting functions' that you mention. It might be quicker. ..Just thought that it WILL be quicker converting the months in the SELECT/OPTION boxes to integers then no need to do a complete rewrite although I should learn how to do the mysql date thing anyway. Thanks Jochem and also to Hugh for replying. Chris - Original Message From: Jochem Maas Date: Mon 2/2/04 20:47 To: [EMAIL PROTECTED] Subject:Re: [PHP] Help for code to 'sort list' please refering to my previous mail: I really don't think its a good idea to store the months as names in the DB (I recommend a datetime field to store the complete date info). but if you must then you will have to define an array which you can use as a sort key e.g. array( 1 = 'January', 2 = 'February', ...etc... ); and use something like this is combination with a multisort array function to get the order you want - but the way I see it your better off changing the DB schema and using a few date related function is either MySQL or PHP to display the dates in a user friendly way (e.g. '10 January 2004'). EastLothianDirectory wrote: Thank you for replying again but I have already been on the Mysql.com website and although I did ascertain the correct syntax ie ... None of them work mainly because the months get listed alphabetically and not IN ORDER. For example AUGUST is listed as the first month because it started with 'A' The year and day being integers were listed correctly. Now do you see the problem. This is why Mysql.com was no good so I came to PHP for help. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
