[RESOLVED] mysql_num_rows To Get Years?

Channel5

Hi all,

I'm trying to get the number of years from my datebase.

I know I could work out a few loops statements to figure this out, but I'm wondering if there's much simpler way to get the the data.

Is there a mysql command line to search how many years are stored within 100s of rows (even the year count itself may be something like 2)?

bradgrafelman

I don't quite understand what you're asking; could you perhaps show us your DB structure and what it is you're expecting to get?

Channel5

Here's the code I came up with:

$year = date('Y');
while($value == false){
     $result = mysql_query("SELECT * FROM table
     WHERE DATE_FORMAT(date, '%Y')='$year'")
     or die(mysql_error());
     if($row = mysql_fetch_array($result)){
          $year = $year - 1;
     }
     else{
          $value = true;
     }
}
$firstYear = $year + 1;
$yearNum = date('Y') - $firstYear;

Essentially, I was wondering if there was function build into mysql/php that accomplishes the same thing

bradgrafelman

I'm still not sure I understand what you're trying to do. I can see that there's no need for a loop at all in PHP and that whatever you're after will probably be easily handled by the DB... I just can't figure out what you want.

Can you explain in words what that code is supposed to do? From what I can understand, $yearNum will either be 1 if no rows were found in the DB, else it will be 1 - x, where x represents the number of rows in the DB that have dates within this year.

Does that sound right?

Channel5

Basically, I want the oldest year from the DB.

I want to get the oldest year so I can compare it with the present year and come up with the total number of years within the DB.

But since the administrator can make entries manually, I can't just pull the first id and use that because there's chance it may be wrong.

Here's an example of the table in case I'm still unclear.

id: 1
date: 2009-04-17

id: 2
date: 2008-12-23

id: 3
date: 2004-09-31

The output I want is the eariliest year, so in this case, id:3.

Yeah, I'm stating the obvious now.

But is there a simple command that would get the oldest year without having to run my loop?

bradgrafelman

To get the oldest year, run this query:

SELECT YEAR(date) as oldestYear FROM table ORDER BY YEAR(date) ASC LIMIT 1

If you want the number of distinct years in the table, try something like:

$query = 'SELECT 1 FROM table GROUP BY YEAR(date)';
$exec = mysql_query($query);
$numYears = mysql_num_rows($exec);

Channel5

Perfect! Just what I was looking for.

Thanks a million.