I have a SQL Query that is meant to get a value from one table then UPDATE the date based in another.

For instance:

I have a table called 'Products', it has a colunm called 'JobLength'. JobLength has three values '1 MONTH, 14 DAYS and 7 DAYS'.

I have another table called 'JobPost' which has a colunm called 'JobExpires'.

The idea is that I would get the JobLength value from 'Products' then change the 'JobExpires' colunm values.

$q = "UPDATE JobAdvert SET JobExpires = IF(JobExpires > NOW(), ADDDATE(JobExpires, INTERVAL '$JobLength'), ADDDATE(NOW(), INTERVAL '$JobLength')) WHERE JobAdvert.JobAdvertID='$JobAdvertID'";
$r = mysqli_real_escape_string($dbc, $q);

Is that right or is there another way of doing it, this code is in part of my IPN ?


Member Avatar


Why not just store your data in unix datestamp?

You can add increment (in seconds format) and datestamps quite easily. This will give the expiry date.

expiry date = advert date + increment

time left = expiry date - now

Although, you may be looking at something more robust.