Re: Query question

You'd still benefit from converting your data into an actual date format in order to take full advantage of available features. hehe You guys are going to start thinking of me as a performance freak, but I think MySQL's date functions might provide better performance than string manipulation. On Oct 29, 2006, at 6:08 AM, Beauford wrote:

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@satyam.com.ar] Sent: October 29, 2006 4:30 AM To: Beauford; 'PHP' Subject: Re: [PHP] 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@netscan.ca> To: "'PHP'" <php-general@lists.php.net> Sent: Sunday, October 29, 2006 9:06 AM Subject: RE: [PHP] 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@satyam.com.ar] Sent: October 29, 2006 2:14 AM To: Beauford; 'PHP' Subject: Re: [PHP] 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@netscan.ca> To: "'PHP'" <php-general@lists.php.net> Sent: Sunday, October 29, 2006 7:05 AM Subject: RE: [PHP] 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@gmail.com] Sent: October 29, 2006 12:15 AM To: Beauford Cc: PHP Subject: Re: [PHP] 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@netscan.ca> 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@gmail.com] Sent: October 28, 2006 10:30 PM To: Ed Lazor Cc: Beauford; PHP Subject: Re: [PHP] 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@yahoo.com> 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