[PHP] Dynamic Date Select and SQL
I have a problem I hope someone can help me with... Firstly I have an SQL Query with a WHERE dateMonthAndYear = $month1 Already this shows part of what I'm trying to do. The date format in the DB is -MM-DD HH:MM:SS. Now I need to dynamically generate a pulldown of months... The range of months in the pulldown are historical, so the last month would be the current month (which should also be the SELECTED option). The first month would be a fixed date, say Jan 2002. The value of the options would probably be like so: select name=month1 // --- Jump Menu option value=$PHP_SELF?month1=$current_month SELECTEDFeb 2002/option // --- Current Month (SELECTED) option value=$PHP_SELF?month1=$first_month$first_month_text/option // --- First Month in range option value=$PHP_SELF?month1=$subsequent_month1$subsequent_month1_text/option // --- Other months in range . // --- Other months in range option value=$PHP_SELF?month1=$current_month SELECTEDFeb 2002/option // --- Current Month option value=$PHP_SELF?month1=ALL/option // --- Empty value will show all /select I guess the question is, how do I dynamically create the select, based on the above spec (or make $first_month, the first month available in the SQL Query i.e. get the earliest month from the Query), then do the select using ONLY the month and year information. I can do most of the bits in between, just do know how to do the dynamic select for this particular application, or get the date into the SQL where ONLY the month and year are relevant. I am using Pear DB and need this to work on MySQL and Access through Pear so the SQL needs to be quite standard. The above code is not syntaxed properlyjust meant for readability. Any ideas are much appreciated as I don't even know where to start with this one. Thanks Simon H -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Dynamic Date Select and SQL
If I understand your question, you are looking for the MYSQL sql statement. Based upon your info, I've found something like this example: mysql describe a; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | mydate | datetime | YES | | NULL| | ++--+--+-+-+---+ 1 row in set (0.04 sec) mysql select * from a; +-+ | mydate | +-+ | 2002-01-01 12:00:00 | | 2002-02-01 12:00:00 | | 2002-03-01 12:00:00 | | 2002-04-01 12:00:00 | | 2002-05-01 12:00:00 | | 2002-06-01 12:00:00 | | 2002-07-01 12:00:00 | +-+ 7 rows in set (0.00 sec) mysql select year(mydate)*100+month(mydate) as sel,mydate, concat(left(monthname(mydate),3),' ',year(mydate)) as monyr from a where mydate now()+03; ++-+--+ | sel| mydate | monyr| ++-+--+ | 200201 | 2002-01-01 12:00:00 | Jan 2002 | | 200202 | 2002-02-01 12:00:00 | Feb 2002 | | 200203 | 2002-03-01 12:00:00 | Mar 2002 | | 200204 | 2002-04-01 12:00:00 | Apr 2002 | | 200205 | 2002-05-01 12:00:00 | May 2002 | ++-+--+ 5 rows in set (0.05 sec) -Original Message- From: Simon H [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 6:32 AM To: PHP General Subject: [PHP] Dynamic Date Select and SQL Sensitivity: Confidential I have a problem I hope someone can help me with... Firstly I have an SQL Query with a WHERE dateMonthAndYear = $month1 Already this shows part of what I'm trying to do. The date format in the DB is -MM-DD HH:MM:SS. Now I need to dynamically generate a pulldown of months... The range of months in the pulldown are historical, so the last month would be the current month (which should also be the SELECTED option). The first month would be a fixed date, say Jan 2002. The value of the options would probably be like so: select name=month1 // --- Jump Menu option value=$PHP_SELF?month1=$current_month SELECTEDFeb 2002/option // --- Current Month (SELECTED) option value=$PHP_SELF?month1=$first_month$first_month_text/option // --- First Month in range option value=$PHP_SELF?month1=$subsequent_month1$subsequent_month1_text/option // --- Other months in range . // --- Other months in range option value=$PHP_SELF?month1=$current_month SELECTEDFeb 2002/option // --- Current Month option value=$PHP_SELF?month1=ALL/option // --- Empty value will show all /select I guess the question is, how do I dynamically create the select, based on the above spec (or make $first_month, the first month available in the SQL Query i.e. get the earliest month from the Query), then do the select using ONLY the month and year information. I can do most of the bits in between, just do know how to do the dynamic select for this particular application, or get the date into the SQL where ONLY the month and year are relevant. I am using Pear DB and need this to work on MySQL and Access through Pear so the SQL needs to be quite standard. The above code is not syntaxed properlyjust meant for readability. Any ideas are much appreciated as I don't even know where to start with this one. Thanks Simon H -- 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] Dynamic Date Select and SQL
Thanks Rick But the SQL needs to work with MS Access AND MySQL through the PEAR DB Abstraction layer... I'm not saying it wont work with Access because I don't know yet...maybe it will, but there looks to be functions in there that may be MySQL specific. If I can do something like this it would be great...what I would like is to get the earliest month year from the DB table then fill in the gaps between then and now, month by month to populate the SELECT BOX Options. Thanks Simon H -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: 15 February 2002 16:06 To: 'Simon H'; PHP General Subject: RE: [PHP] Dynamic Date Select and SQL Sensitivity: Confidential If I understand your question, you are looking for the MYSQL sql statement. Based upon your info, I've found something like this example: mysql describe a; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | mydate | datetime | YES | | NULL| | ++--+--+-+-+---+ 1 row in set (0.04 sec) mysql select * from a; +-+ | mydate | +-+ | 2002-01-01 12:00:00 | | 2002-02-01 12:00:00 | | 2002-03-01 12:00:00 | | 2002-04-01 12:00:00 | | 2002-05-01 12:00:00 | | 2002-06-01 12:00:00 | | 2002-07-01 12:00:00 | +-+ 7 rows in set (0.00 sec) mysql select year(mydate)*100+month(mydate) as sel,mydate, concat(left(monthname(mydate),3),' ',year(mydate)) as monyr from a where mydate now()+03; ++-+--+ | sel| mydate | monyr| ++-+--+ | 200201 | 2002-01-01 12:00:00 | Jan 2002 | | 200202 | 2002-02-01 12:00:00 | Feb 2002 | | 200203 | 2002-03-01 12:00:00 | Mar 2002 | | 200204 | 2002-04-01 12:00:00 | Apr 2002 | | 200205 | 2002-05-01 12:00:00 | May 2002 | ++-+--+ 5 rows in set (0.05 sec) -Original Message- From: Simon H [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 6:32 AM To: PHP General Subject: [PHP] Dynamic Date Select and SQL Sensitivity: Confidential I have a problem I hope someone can help me with... Firstly I have an SQL Query with a WHERE dateMonthAndYear = $month1 Already this shows part of what I'm trying to do. The date format in the DB is -MM-DD HH:MM:SS. Now I need to dynamically generate a pulldown of months... The range of months in the pulldown are historical, so the last month would be the current month (which should also be the SELECTED option). The first month would be a fixed date, say Jan 2002. The value of the options would probably be like so: select name=month1// --- Jump Menu option value=$PHP_SELF?month1=$current_month SELECTEDFeb 2002/option // --- Current Month (SELECTED) option value=$PHP_SELF?month1=$first_month$first_month_text/option // --- First Month in range option value=$PHP_SELF?month1=$subsequent_month1$subsequent_month1_tex t/option // --- Other months in range . // --- Other months in range option value=$PHP_SELF?month1=$current_month SELECTEDFeb 2002/option // --- Current Month option value=$PHP_SELF?month1=ALL/option // --- Empty value will show all /select I guess the question is, how do I dynamically create the select, based on the above spec (or make $first_month, the first month available in the SQL Query i.e. get the earliest month from the Query), then do the select using ONLY the month and year information. I can do most of the bits in between, just do know how to do the dynamic select for this particular application, or get the date into the SQL where ONLY the month and year are relevant. I am using Pear DB and need this to work on MySQL and Access through Pear so the SQL needs to be quite standard. The above code is not syntaxed properlyjust meant for readability. Any ideas are much appreciated as I don't even know where to start with this one. Thanks Simon H -- 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
