I don't see this as a particular problem as the data will always be the same. Now under other circumstances in another database, I can see what you are saying. -----Original Message----- From: Satyam [mailto:Satyam@xxxxxxxxxxxxx] Sent: October 29, 2006 4:30 AM To: Beauford; 'PHP' Subject: Re: Query question I said that you have a problem,not that you caused it, and my observation might (hopefully) help newbies in the list. Satyam ----- Original Message ----- From: "Beauford" <php-user@xxxxxxxxxx> To: "'PHP'" <php-general@xxxxxxxxxxxxx> Sent: Sunday, October 29, 2006 9:06 AM Subject: RE: Query question > This is how I get the data and I have no control over the actual layout of > the database. So I have to work with what I have. > > Robert: > > LOL, I don't know either. The format is - 01/01/2006. When I first did it > I > used 7, which should be right, but I ended up getting /2002 /2003, etc. So > I > went to 8 and all was well. Beats me..... > > > B > > -----Original Message----- > From: Satyam [mailto:Satyam@xxxxxxxxxxxxx] > Sent: October 29, 2006 2:14 AM > To: Beauford; 'PHP' > Subject: Re: Query question > > If that works then you have a problem: you are storing dates as a plain > string ( varchar or whatever) instead of a native date/time datatype, > which > precludes the use of a large number of native SQL date/time functions, > such > as year() which should suit you nicely in this case. Eventually, you will > bump into something more elaborate which you won't be able to do on the > SQL > side just with string functions and that will force you to bring whole > tables into PHP to do more extensive processing. > > Satyam > > ----- Original Message ----- > From: "Beauford" <php-user@xxxxxxxxxx> > To: "'PHP'" <php-general@xxxxxxxxxxxxx> > Sent: Sunday, October 29, 2006 7:05 AM > Subject: RE: Query question > > >> This is what I finally figured out, which works just perfectly. >> >> select count(date) as count, substring(date,8) as year from stats group >> by >> year; >> >> Thanks to all for the input. >> >> -----Original Message----- >> From: Joe Wollard [mailto:joe.wollard@xxxxxxxxx] >> Sent: October 29, 2006 12:15 AM >> To: Beauford >> Cc: PHP >> Subject: Re: Query question >> >> Look into the MySQL YEAR() function to extract the year from a specific >> date. >> Start here: >> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html >> >> >> >> >> On 10/28/06, Beauford <php-user@xxxxxxxxxx> wrote: >>> >>> I posted this here as I figured I would need to manipulate this using >>> PHP. >>> The code below doesn't quite work because the date is in the format of >>> 05/05/2006. So I am getting totals for each date, not each year. I >>> need the totals for each year, regardless of the day or month. This is >>> why I figured I'd need to use PHP to maybe put it in an array first or >>> something. >>> >>> Thanks >>> >>> -----Original Message----- >>> From: Joe Wollard [mailto:joe.wollard@xxxxxxxxx] >>> Sent: October 28, 2006 10:30 PM >>> To: Ed Lazor >>> Cc: Beauford; PHP >>> Subject: Re: Query question >>> >>> Agreed, this should go to a MySQL list. But in the spirit of helping I >>> think the following should give you a good starting point. >>> >>> SELECT `year`, COUNT(`year`) AS `count` FROM `tbl` GROUP BY `year` ASC >>> >>> >>> On 10/28/06, Ed Lazor <edlazor@xxxxxxxxx> wrote: >>> > >>> > Use the mysql list :) >>> > >>> > >>> > On Oct 28, 2006, at 3:01 PM, Beauford wrote: >>> > >>> > > Hi, >>> > > >>> > > I have a MySQL database with a date field and a bunch of other >>> > > fields. The date field is in the format - 01/01/2006. What I want >>> > > to do is query the database and create a table that shows just the >>> > > year and how many instances of the year there is. I have been >>> > > taxing my brain for a simple solution, but just not getting it. >>> > > Any suggestions? >>> > > >>> > > Thanks >>> > > >>> > > Example output. >>> > > >>> > > Year Count >>> > > >>> > > 2002 5 >>> > > 2003 8 >>> > > 2004 9 >>> > > 2005 15 >>> > > 2006 22 >>> > > >>> > > ps - I get this information sent to me and I can't change any of >>> > > the data. I just enter it in the db and then hopefully do the >>> > > query on it. >>> > > >>> > > >>> > >>> > -- >>> > 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 >>> >>> >> >> -- >> 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 > > -- > 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
