Page 1 of 1

Dynamic Variables

Posted: Wed Mar 11, 2009 1:48 am
by NaOH
Hi there,

Suppose I have a database full of pictures, each of which has a date taken associated. Suppose then I want to generate a listing of the number of pictures that were taken in each year, but want it to be dynamic insofar as I want it to return listings for every year that there are pictures from, without knowing ahead of time how many years there actually are.

How can I do this? I figure that I need to have some variables created on the fly, to represent each year, containing a value equal to the number of pictures for that year (this part, the number of pictures in a year, I've already figured out in the context of where this all needs to take place: a big while loop).

Thanks! If anyone needs code snippets or the like, I can provide them, but I figure this shouldn't be so difficult to figure out from my description alone (though I obviously confess to not being able to cipher it out myself. :oops: )

Re: Dynamic Variables

Posted: Wed Mar 11, 2009 2:47 am
by requinix
You can just write a query to do it for you.

How are you storing the dates in the database?

Re: Dynamic Variables

Posted: Wed Mar 11, 2009 12:55 pm
by NaOH
The date/times come out of the EXIF data in the JPGs and are stored as varchar(64)'s in the format YYYY:MM:DD HH:MM:SS.

Re: Dynamic Variables

Posted: Wed Mar 11, 2009 1:22 pm
by requinix
Not ideal but it will still work.

Code: Select all

SELECT COUNT(1) AS `pictures`, YEAR(date_field) AS `year` FROM table GROUP BY `year`
Change pictures, date_field, year, and table to whatever names you need/want.

If suggest creating another field in the table dedicated just to the year. It'll be faster to use that column than a function call.

Re: Dynamic Variables

Posted: Wed Mar 11, 2009 2:27 pm
by pickle
I'm not sure YEAR() will work on the date column, as it's not strictly a date or datetime column.

~NaOH I'd suggest re-typing that column as datetime.

Re: Dynamic Variables

Posted: Wed Mar 11, 2009 3:53 pm
by requinix
pickle wrote:I'm not sure YEAR() will work on the date column, as it's not strictly a date or datetime column.
It works in MySQL 5.0.51.

Code: Select all

DATE("2009:03:11 12:34:56")

Re: Dynamic Variables

Posted: Wed Mar 11, 2009 11:33 pm
by jayashree
SELECT COUNT(1) AS `pictures`, YEAR(date_field) AS `year` FROM table GROUP BY `year`

Re: Dynamic Variables

Posted: Thu Mar 12, 2009 1:08 pm
by NaOH
Okay, so I just need a bit more help. Excuse my ignorance of a lot of this; I don't have a lot of experience with manipulating SQL with PHP...

So the query I've got now is "SELECT COUNT(1), YEAR(EXIF_date_taken) AS `year` FROM `plogger_pictures` GROUP BY `year`" which, when I run it in phpMySQL results in

Code: Select all

COUNT(1)  year
33        2007
80        2008
40        2009
Now, how can I get it to spit this out as "Total pictures from 200x: y"? Just for testing purposes I've tried just echoing the results of this query in PHP, but all I get is "Resource id #37".

Thanks! :D

Re: Dynamic Variables

Posted: Thu Mar 12, 2009 4:48 pm
by NaOH
Nevermind, all. Got her solved:

Code: Select all

<?php
 
$d = "SELECT COUNT(1), YEAR(EXIF_date_taken) AS `year` FROM `plogger_pictures` GROUP BY `year`";
            $resultd = mysql_query($d) or die("Could not execute query : $d." . mysql_error());
 
while ($row=mysql_fetch_array($resultd)) {
echo "Pictures from " . $row["year"] . ": " . $row["COUNT(1)"];
echo "<br />";
}
 
?>