Hello everyone,

I'm trying to write a script in PHP that will pull an image and a URL from a database. Both will be tied in to a sequential number serving as the primary key for the table.

What I'd like to do is to have the PHP generated HTML reference a new image and URL every 24 hours, based on the sequential number. Every day it would increment up one from the previous day's number.

Any ideas how to do that? I'm fairly new to PHP and MySQL, and I just now got the hang of retrieving data from the database. Hints, clues, snippets or anything would be fine, since I do need to learn this on my own.

Dave

Create a table called `settings` with a `settings_id` and `value` column. Then, at 12am, run a cron job to increment that by 1 if there are enough rows in the other table, and pull the id from there.

Create a table called `settings` with a `settings_id` and `value` column. Then, at 12am, run a cron job to increment that by 1 if there are enough rows in the other table, and pull the id from there.

Your query needs to know that if the 'value' does not exist in the images database, that it needs to go to the next highest number. (just in case an image gets erased down the road, you could end up with nothing getting displayed)

Thanks Mike and clrockwell! I tried to reply last night to your VERY fast response Mike, but I encountered some sort of glitch. You've both given me some good ideas to follow. Much appreciated!

Dave

I suggest creating a database with 'id','image_url','time' and then run a query that will pull the next image and update the 'time' field to the current time using time()

In order to view a different image in 24 hours, simply run a select query that will check the current time against the current image and if the current time is 86400 larger than the 'time' field, choose the next image.

maybe have ID (tinyint), image(varchar), and active(bool)

if the ids are sequential, you could have the script change the next one to active and throw it on a cronjob

All you have to do is setup a table with the images and url, and a primary id, and a active column, every 24 hours a cron will run, then will increment the id, making sure its there, and setting it as active. Everytime the script runs, it just calls the table for the row that is active.

So you have a table, with columns like:

id -- an integer that is the primary key, which is sequential
img -- location of an image
url -- the link for this image

One possible approach that doesn't involve another table, is to use some SQL:


SELECT `img`, `url` FROM `mytable` WHERE `id` = DATE_FORMAT(CURDATE(),'%e');


Assuming your rows start at 1, this will pull the "current" row for today. There are some issues with this solution though:

1. It will cycle the first 31 rows on your table, since possible range for date is 1 - 31
2. If you have more than 31 rows, then it will never 'step out' of the date range.
3. It will never show row 0.