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
