Stepping through auto-increments one at a time?

[Jolten]

Hi all,

Need a little help please.

I've got a table which I insert data into (via a checkbox/submit):

Table:
id ( int(10) auto-increment PRIMARY)
field1
field2
field3 (curdate)
field4 (curdate + inrement)

Values are inserted via admin interaction and can be inserted at any time. Each row of data should only be available to a query once a certain condition is met.

So if I have a query which stated to only pull results if field 2 = (condition) it limits the results.

I'm query this data and displaying 1 record at a time based on the id field. I've got all this worked out.

This is where I need help.....

What I'd like to do is to add some condition to the query or PHP which states to increment the results by id value. Basically, today show the row with the id of 50 then tomorrow show the row with id 51, then the day after show row 52. Does this make any sense? I just want to step through the id fields using data as it's made available. Since the curdate field of the table will contain the date of whenever the information was inserted, I can't use the curdate field to control this.

Any ideas?

None of this is in a loop. It's a script which should always result in 1 result.

Thanks

[Dean C]

Hmm I kind of understand what you want but it'd help if you provided a better example

[Jolten]

Okay. I'm setting a featured image into a table.

This table gets queried and displays the image on the home page.

I want to be able to pick featured images at any time but I do not want the home page image updating immediately after I pick an image. I want to pick 5 images at once then have the query use each image for a day (24 hours).

Something like (and this is just complete guessing)

$dtm=date(Ymd);
$dt = "20050122"; //todays date
while ($results=mysql_fetch_object($query)){

if($dtm ==$dt){

display featured image

}

$dt++
}


BUT, I don't think I need a loop. I could be wrong.

Currently, without a loop, I've got the query working and displaying the last image added to the db table.

Here's what I've got:

$qrySQL="SELECT field0.*, field1.chrresult, field1.atoconid, field2.intcontid, field2.strimgname, field2.strusrid, field2.strimgtitle 
FROM field0, field1, field2 
WHERE field1.chrresult = 'y'  
AND field2.intcontid = field1.atoconid 
AND field2.strimgname = field0.strimgname 
ORDER BY field0.imgid DESC LIMIT 0,1";

$rs0=$DB_site->query($qrySQL);
$row0=mysql_fetch_object($rs0);
$strusrid=$row0->strusrid;
$strimgname =$row0->strimgname;
$fname="./path/$row0->strimgname";
$intcontid=$row0->intcontid;
$imgTitle=$row0->strimgtitle;
$imgID=$row0->atocontid;

field0.imgid is the auto-increment table I want to step through.

I may have had an epiphany. Could I set a small php loop to increment an integer every 24 hours, set a variable for this and then use that for the where clause in the query?

Not sure how I'd write the incrementing loop but that seems like it might work.

[Dean C]

Hmm make an intermediate table:

fimage:
- imageid
- datepicked

When you pick an image, insert it's id and dateline in there. Then on your homepage just have something like:

SELECT 
        imageid
FROM 
        fimage 
WHERE
        DATEDIFF(current_date, dateline) = 1
LIMIT 
       5

Untested ^^

[Jolten]

can you auto-increment a date field in mysql?

[Dean C]

Nope

[Jolten]

didn't think so but I did get it sorted

I changed the imgid to auto-increment from 20050122 and then set a variable for the current date ($dt=date(Ymd) in php and simply compared $dt to imgid in the query. This effectively auto-increments the date even though it's not a real date field in mysql.