MDauphinais1
asked on
Compare MySQL date with PHP date
I am trying to create a code that will take the date stored in a MySQL table and check to see if the current date is more than a week old. If a week or more exists between the two, then do this. If not, do that.
Here is what I have:
$feat = mysql_query("SELECT artistid, lastupdated FROM featuredartist LIMIT 1");
$featin=0;
$id = mysql_result($feat,$featin ,"artistid ");
$lastupdated = mysql_result($feat,$featin ,"lastupda ted");
$timediff = time() - $lastupdated;
if ($timediff < 1209600){
'it has been a week or more
} else {
'it has been less than a week
}
Can anyone tell me why this isn't working correctly?
Here is what I have:
$feat = mysql_query("SELECT artistid, lastupdated FROM featuredartist LIMIT 1");
$featin=0;
$id = mysql_result($feat,$featin
$lastupdated = mysql_result($feat,$featin
$timediff = time() - $lastupdated;
if ($timediff < 1209600){
'it has been a week or more
} else {
'it has been less than a week
}
Can anyone tell me why this isn't working correctly?
ASKER
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 />";
}
$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"]}'");
$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"]}'");
ASKER
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?
// 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.
// 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.
ASKER
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 ,"lastupda ted");
$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
$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
$lastupdated = mysql_result($feat,$featin
$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
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
$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
}
$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
}
ASKER
Thanks funkjedi. That did the trick.
SELECT artistid, lastupdated
FROM featuredartist
WHERE lastupdated < CURRENT_DATE - INTERVAL 7 DAY