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
