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:remery@excel.com] > 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:simon.hamilton@ntlworld.com] > 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