Re: Query question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@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


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux