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:[EMAIL PROTECTED]
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" <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]
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" <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
