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()+00000300000000; > +--------+---------------------+----------+ > | 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 "YYYY-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" SELECTED>Feb > 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" SELECTED>Feb > 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 > properly....just 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
