Do I need a database for movies?

[ddrueding]

Heh. I recieved so much help so quickly I actually felt a bit guilty. All I've been doing since is reading and re-reading the code so I know what it actually does and why it is done that way.

Once I have that down, I get to play with taking stuff from arrays and sticking it into the database. A whole new world of pain.

 

[Tannin]

Maybe you can help me with this one, Dave.

How do I pass a constant to SQL?

This is straightforward:

SELECT * FROM movies WHERE `date` < '$year'

But what if $year is not a variable, but the PHP constant YEAR? Obviously, I can do this:

$temp = YEAR;

and then SELECT * FROM movies WHERE `date` < '$temp', but that's a crappy way to code stuff. Similarly, I could hard-code the value of the constant YEAR (e.g., "2005") into the SQL query, but that is an even crappier way to do things.

I assume it's just a matter of finding the right syntax with which to pass mysql the PHP constant - but the documentation for this general area is truly dreadful: most of it falls into the black hole of not being covered properly in the PHP manual because it's a mysql thing, and not being covered in the mysql manual because it's a php thing.

 

[Handruin]

I guess it depends what Dave is trying to do with a date. Is it something to be passed in from a user, or is it supposed to be from the current date. The current date is selectable from within MySQL if that's what you need.

SELECT CURDATE();

 

[Tannin]

Sorry Doug, I was unclear in my post.

In the example above, `date` represents a field in the mysql "movies" database named "date", but it could just as easily have been something else. The actual fragment of code that prompted my question was this:

$minyear = MINYEAR;
result = query( "SELECT `image`, `title` FROM main WHERE `image` < '$image' AND `year` >= '$minyear' ORDER BY `image` DESC LIMIT 1");

This is part of the navigation bar code for my new site. Image names are derived from EXIF date/time and sort in cronological order without needing any extra coding. Usually, the routine just selects the image prior to the current image. However, if we are already on the oldest regular image (dated 2003), then that will roll us back into the special-purpose images which we do NOT want to display in this context (these are all dated earlier than 2003).

All of this folds into a "go backwards" navigation button which links to the older image and also provides the title of that image via CSS flyover. On reaching the earliest image, however, the button morphs into a "loop around" icon and links elsewhere (to an index page for the newest images, as a matter of detail). Also, there are times when the button does other things which are not relevant here as that code comes later.

It all works just fine: it just bugs me that I don't know how to pass MYSQL the constant "MINYEAR" directly, only via kludge (a) (use a temp variable, in this case "$minyear = MINYEAR"), or kludge (b) (hard-code the value of MINYEAR, which is 2003).

Clear as mud?

 

[Tannin]

Oh, I should have explained that "query()" above is just a function I wrote that makes a DB link (if required), calls mysql_query, and does some error checking.

 

[Handruin]

Where is you MINYEAR const being set? You could always throw some type of sub query into the mix if you need to derive the date of the earliest image you need to select from. Not knowing the rest of your database structure, it's hard to gather if that is even worth pursuing.

Something like:

result = query( 
"SELECT `image`, `title` " .
"FROM main " .
"WHERE `image` < '$image' " .
"AND `year` >=  " .
"(SELECT MIN(date) " .  //add in the date field here
"       FROM imageTable " .  //add in whatever your image table might be if this applies
"       LIMIT 1)" .
"ORDER BY `image` " .
"DESC LIMIT 1");

Not sure if that helps things or makes them worse for you...

 

[Tannin]

Worse, actually, though it's a neat idea, which I might get some value from at another time, but for this particular task, all I need is to pass mysql a query containing a PHP constant.

Well, I don't need to do that, Kludge (a) above works just fine, it's simply that I don't like it because it's a kludge. In this example it only introduces one unnecessary local variable and one extra line of code, but there will doubtless be other examples in the future and it would be good to know what the proper way to do this is.

To answer your question, the constant MINYEAR is hard coded in an include file, alongside maybe 20 other constants. I think it's a good idea to use constants wherever possible in php. For one thing, they have global scope so you don't have to fiddle about passing them to your functions or issuing global $xyz" all the time. For another thing, you can't accidentally assign a different value to them. And finally, they are good for your if/then logic because they can help catch errors where you mistake an "=" for an "==".

So I try to list as many constant values as possible all in the one place where they ae easy to find and change. This MINYEAR example is perhaps a poor one, as I'm unlikely to ever change it, but the matching pair for it, MAXYEAR, is particularly useful. I have no idea how many line of my code reference MAXYEAR - at a guess, 10 or 20, in maybe 5 or 6 different files. But when I started adding the latest (2008 ) pictures a week or two ago, I didn't need to know that stuff, all I had to do was change one line in my include file (and make a new thumbnail picture because some of the links and indexes use graphical "click-the-picture" linking). If I'd written "2007" instead of MAXYEAR in my code, I'd have had to hunt through the entire codebase looking for instances.

 

[Handruin]

constants are a good thing for all the reasons you've said. You could try making your own implementation of it that might help reduce the extra line of code by doing something like this in the future:

$YEARS = array("MINYEAR"=>2003, "MAXYEAR"=>2007);

result = query( "SELECT `image`, `title` FROM main WHERE `image` < '$image' AND `year` >= '$YEARS["MINYEAR"]' ORDER BY `image` DESC LIMIT 1");

I know that it isn't as safe as a constant, but it might be more flexible.

Also, have you tried the following in your code when using your constant?

result = query( "SELECT `image`, `title` FROM main WHERE `image` < '$image' AND `year` >= 'constant("MINYEAR")' ORDER BY `image` DESC LIMIT 1");

 

[ddrueding]

Wow. Good stuff. And no, Tannin, I wouldn't have been able to help with this yet. I will at some point, but not yet. Thanks for the info Handruin.

 

[Handruin]

I was looking around to do some work and testing of php mysql stuff unrelated to your movie project and found this neat virtual appliance built as a nice development box in case you were ever looking for something like it (runs Gentoo/apache/php/mysql). That appliance is also listed on vmware's appliance market place if you need more assurance.

 

[ddrueding]

I was looking around to do some work and testing of php mysql stuff unrelated to your movie project and found this neat virtual appliance built as a nice development box in case you were ever looking for something like it (runs Gentoo/apache/php/mysql). That appliance is also listed on vmware's appliance market place if you need more assurance.

That is neat, but it seems he's done some customization and tweaking that is outside my linux skills. No matter, I have my own VM that I've been using for this project