Dynamic Variables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
NaOH
Forum Newbie
Posts: 5
Joined: Sat Dec 22, 2007 8:31 pm

Dynamic Variables

Post 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: )
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Dynamic Variables

Post by requinix »

You can just write a query to do it for you.

How are you storing the dates in the database?
NaOH
Forum Newbie
Posts: 5
Joined: Sat Dec 22, 2007 8:31 pm

Re: Dynamic Variables

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Dynamic Variables

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Dynamic Variables

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Dynamic Variables

Post 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")
jayashree
Forum Newbie
Posts: 6
Joined: Fri Mar 06, 2009 5:44 am

Re: Dynamic Variables

Post by jayashree »

SELECT COUNT(1) AS `pictures`, YEAR(date_field) AS `year` FROM table GROUP BY `year`
Last edited by jayashree on Sun Mar 15, 2009 11:42 pm, edited 1 time in total.
NaOH
Forum Newbie
Posts: 5
Joined: Sat Dec 22, 2007 8:31 pm

Re: Dynamic Variables

Post 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
NaOH
Forum Newbie
Posts: 5
Joined: Sat Dec 22, 2007 8:31 pm

Re: Dynamic Variables

Post 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 />";
}
 
?>
Post Reply