Compare MySQL date with PHP date

I believe this is what your looking for, returns all the artist that haven't been updated in a week.

SELECT artistid, lastupdated
FROM featuredartist
WHERE lastupdated < CURRENT_DATE - INTERVAL 7 DAY

Not quite. There is only 1 row in this table. This table stores the artist ID and lastupdated date for the featured artist section on the web site. Every 7 days I have code that will change the artist ID stored in the table. But if it has been less than 7 days, then don't change anything and display the current ID.

Maybe I should give you an example. If you did something like the following it would print out all the artists that haven't updated in the past week.


$result = mysql_query("SELECT artistid, lastupdated FROM featuredartist WHERE lastupdated < CURRENT_DATE - INTERVAL 7 DAY ORDER BY lastupdated DESC");

if (mysql_num_rows($result) == 0) {
    die("All artist have updated in the past week.")
}

echo "The following artist have not updated in the past week:<br />";

while ($row = mysql_fetch_assoc($result)) {
    echo "artist id: " . $row["artistid"] . " last updated: " . $row["lastupdated"] ."<br />";
}

K then what your looking for is more like this

$result = mysql_query("SELECT artistid, lastupdated FROM featuredartist WHERE lastupdated < CURRENT_DATE - INTERVAL 7 DAY LIMIT 1");

// no need to update
if (mysql_num_rows($result) == 0) { exit; }

// change the artist id
$result = mysql_query("UPDATE SET artistid = 'Dr Dre', lastupdated = NOW() WHERE artistid = '{$row["artistid"]}'");

So this part of your code:

// change the artist id
$result = mysql_query("UPDATE SET artistid = 'Dr Dre', lastupdated = NOW() WHERE artistid = '{$row["artistid"]}'");

Will only be run if the above section doesn't exit, meaning 0 records were found?

I don't need the  } else {   stuff?

K lets try it a less confusing

// check if the artist was updated a week or more ago
$result = mysql_query("SELECT artistid, lastupdated FROM featuredartist WHERE lastupdated < CURRENT_DATE - INTERVAL 7 DAY LIMIT 1");

// the artist hasn't been updated in a week so update it now
if (mysql_num_rows($result) > 0)
{
      // we get the artistid so we can update the database
      $row = mysql_fetch_assoc($result)

      // we update the database with a new artist and set the lastupdate to the current date and time
      $result = mysql_query("UPDATE SET artistid = 'Dr Dre', lastupdated = NOW() WHERE artistid = '{$row["artistid"]}'");
}


Hopefully thats a little clearer.

This is what I have as a test....

$feat = mysql_query("SELECT artistid, lastupdated FROM featuredartist WHERE lastupdated < CURRENT_DATE - INTERVAL 7 DAY LIMIT 1");

// no need to update
if (mysql_num_rows($feat) == 0) {
$featin=0;
$id = mysql_result($feat,$featin,"artistid");
$lastupdated = mysql_result($feat,$featin,"lastupdated");
$featuredartist = $id;
exit; }

// change the artist id
$featuredartist = 2;


The part under change artist ID would actually be a larger code that randomly picks a new number but for testing I just put the number 2.

When I ran the above code I got this error:

PHP Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 18 in D:\Websites\www.emphaticradio.com\bands\config.php on line 19 PHP Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 18 in D:\Websites\www.emphaticradio.com\bands\config.php on line 20  

Wait, is that query only returning the data if is was updated more than a week ago or less? If it was updated less than a week ago, I want the data from the table, otherwise, the table row results should be 0... right?

$feat = mysql_query("SELECT artistid, unix_timestamp(lastupdated) as lastupdated FROM featuredartist LIMIT 1");
$row = mysql_fetch_array($feat, MYSQL_ASSOC);
$lastupdated = $row["lastupdated"];
$currentTime = time();
$oneweeklater = strtotime("+1 week ".date("Y-m-d H:i:s", $lastupdated));

if ($currentTime < $oneweeklater){

'it has been less than a week

} else {

'it has been a week or more

}

Thanks funkjedi. That did the trick.