Hi Everyone:

Well, I'm back with yet another question about PHP + MySQL. This time its getting a variable to work in a WHERE clause. The database holds comic page filenames and the date they are to be released on the website. I want the artist to be able to upload and register (in the database) as many pages as he wants. The filenames arre formatted with the date of release, like this - 20090718.png - this is the day of release lets say. I convert this to a unix timestamp so I can manipulate and format it in varios programmatical ways later. This SQL works in my code quite easily. I have a script to INSERT now I need to get it back in various ways.



Even though the whole comic might be listed in the database, comics are released twice a week. I want all comics equal to or less than the current date to be available. Future comics stay hidden until there time. Users can step forwards and backwards through the VALID list of comics. I tried this SQL and it didn't work.



I've tried $today with and without the single quotes - single quotes around variables in my INSERT statement worked... The value of $today is also a timestamp for the current day. If a filename has this as its release date then that is a valid comic and can be displayed. Again, I want the resultset to include the current day's timestamp value and below.

If this sounds confusing I can try to elaborate on it in more detail but really it comes down to using PHP variables in my WHERE clause. Also, is this the proper way to get the results I want. I use the data in the table to display the correct comic page to users on the site and they can step through the pages sequentially.

Any and all help is greatly appreciated. The books I have mention every other kind of situation but fall short of a really good hands-on about PHP and MySQL - the little things you have to know to pull off what you want to do. Thanks in advance.

Try MySQL's UNIX_TIMESTAMP function, it should yeild the results you're after.

Like:



That's providing the timestamp is stored as an integer (which it should be!), I've always been a fan of using datetime columns rather than pseudo timestamp columns, can convert them to timestamps easily enough with UNIX_TIMESTAMP and php's http://www.php.net/strtotime.

http://dev.mysql.com/doc/refman/5.1/en/dat..._unix-timestamp

Hope that's what you were after.

Well yes, but the test script still wont return the expected recordset. If fact, it returns nothing. Let me try to explain better Bread.

First, the table contains three fields; 'comic_id', 'comic_filename' and 'comic_date'. Here's an example of the data in the table:

1 -- 20090701.png -- 1246420800
2 -- 20090703.png -- 1246593600
3 -- 20090707.png -- 1246939200
4 -- 20090714.png -- 1246939200
5 -- 20090731.png -- 1249012800

The 'comic_date' field is generated by extracting the filename, for example, the filename '20090703.png' is converted into this string, '2009-07-03' then fed into the PHP strtotime() function which returns, '1246593600' and this is stored as an integer in the table, btw.

I want, as part of my test script, to exclude everything but dates that are <= the current date, which should return everything but this record:

5 -- 20090731.png -- 1249012800

This way no comic pages are released before the intended time no matter how many comic records are in the table. I used PHP time() to generate the timestamp for the current day. So in my SQL I tested this way:

sql = "SELECT comic_filename, comic_date FROM comic ORDER BY comic_date DESC WHERE comic_date <= '$today'";

Wouldn't this work the same as the MySQL UNIX_TIMESTAMP(NOW)) because they are both timestamp integers. $today = time(); ... ?

Also, even if, in this case, I need to use UNIX_TIMESTAMP(NOW)), how do I include PHP variables in this WHERE clause?

Btw, here's the 'test_comic.php' code? This was a script to help me get a better understanding of the concepts involved.



The two require_once() statements are simply my database credentials and the connect and select code. I know I'll only get one row in the print_r but I think the first record returned in DESC order should be the '20090714.png' record if it has successfully excluded the '20090731.png' record. It still is not working.

I hope this makes things clearer about what I want to do and how I have so far attempted to do it. Any help, as usual, is greatly appreciated. Thanks in advance.

Btw, The database table 'comic' is built from another script which seems to be working as expected. I still also wonder why my variable $today isn't working as expected.

Hi Everybody:

Well, finally after so much frustration a single line in a forum post googled from the internet provided the answer. Isn't that how it always goes - after banging your head on the proverbial wall for hours - BAM - it finally becomes clear. Here's the query that works:

$sql = "SELECT comic_filename, comic_date FROM comic WHERE comic_date <= '$today' ORDER BY comic_date DESC";

A simple transposition of the clauses - WOW - so there it is.

Thanks Bread for your help and everybody that read the thread. Hopefully, this will help someone like me searching for the answer to an obscure problem with broken code. I guess in the end it makes you a better programmer but I sure do have a headache...