We help IT Professionals succeed at work.

Compare MySQL date with PHP date

MDauphinais1
MDauphinais1 asked
on
561 Views
Last Modified: 2013-12-12
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,"lastupdated");
$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?
Comment
Watch Question

Commented:
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

Author

Commented:
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.

Commented:
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 />";
}

Commented:
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"]}'");

Author

Commented:
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?

Commented:
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.

Author

Commented:
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  

Author

Commented:
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?
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
blue_hunterTechnical Lead
CERTIFIED EXPERT

Commented:
$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

}

Author

Commented:
Thanks funkjedi. That did the trick.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.