php code for checking current date is newer than date in database

Question

iam developing a webSite using php and mysql and am really beginner in this area.. Now iam stuck in a place where i have to check the current date is newer than the date in my database

so i have written a code like this but it's not working

$SQL = "UPDATE adverts SET active='0' WHERE enddate<NOW()";
$result = mysqli_query($con,$SQL);

in the above code 'advert' is my table name and 'enddate' is where the column containing date in database

can anybody please help me?

Why don't you obtain the data, and then compare it with your application? — aIKid, Oct 06 '13 at 10:44
@AmalMurali there is no error in my program... it's not giving the output that i expected — Sam, Oct 06 '13 at 10:50
`NOW()` will return something that looks like this: *10/6/2013 6:46:57 AM* so unless the data you are storing is exactly the same it wont work, and you appear to only be using date. Try using `CURDATE()` or convert your date storage to UNIX — cameronjonesweb, Oct 06 '13 at 10:51
What is the type of enddate? Sample data, and what you receive as result. Also what is the expected result? — Milan Zavišić, Oct 06 '13 at 10:58
@user1672694 - this is not correct and it is much better to use date types for dates. `NOW()` is a mysql function not a php function — Anthony, Oct 06 '13 at 11:19
@Anthony What is not correct? Of course it is an SQL function. I don't think you know what you are talking about — cameronjonesweb, Oct 07 '13 at 01:39
@user1672694 - "so unless the data you are storing is exactly the same it wont work" ... This is incorrect and you should chill out. — Anthony, Oct 07 '13 at 02:35
@Anthony In my experience it hasn't and you should probably take a leaf out of your own book before making yourself a hypocrite — cameronjonesweb, Oct 07 '13 at 03:13

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

As Anthony described in comments, it's a good idea to check if your query is working in PHPMyAdmin at all, by going to PHPMyAdmin -> SQL -> Run Query. This way you can distinguish if it's an MySQL Error or an PHP Error.

 UPDATE adverts SET active = 0 WHERE ( enddate < NOW() )

I've set active '0' to 0, simply because I believe it'll be an Integer field - secondly, there's a small but important difference in your enddate:

is it a date field or a datetime field? See below:

SELECT NOW(); // You will get 2010-12-09 17:10:18
SELECT CURDATE(); // You will get 2010-12-09

Source: MySQL Curdate() vs now()

You can use affected_rows() to see if you query did work, but just didn't meet any criteria

$sql = "UPDATE adverts SET active = 0 WHERE ( enddate < NOW() )";
$queried = mysqli_query( $con, $sql );
if ( mysqli_affected_rows( $con ) >= 1 ) {
     //We know some rows were effected.
}

edited May 23 '17 at 11:50

Community

1
1

answered Oct 06 '13 at 10:49

MackieeE

11,751
4
39
56

hi,i have run the query in databse and its working... but why it's not working with php – Sam Oct 06 '13 at 11:08
[Run any PHP errors](http://www.mjdigital.co.uk/blog/php-display-errors-at-runtime/), I doubt there will be any, because nothing is 'failing' but I think it's going to be something to do with enddate being a date field, not datetime. – MackieeE Oct 06 '13 at 11:24
What exactly you expect of this code to work? Just to update table, or you want to get updated rows in $result ? – Milan Zavišić Oct 06 '13 at 11:28
Also @MilanZavišić pointed out an important point, is it adverts or advert? – MackieeE Oct 06 '13 at 11:40

score -1 · Answer 2 · answered Oct 06 '13 at 10:43

-1

Did you tried

UPDATE adverts SET active='0' WHERE enddate<DATE(NOW())

also, is it adverts or advert?

Or you can try with CURDATE()

UPDATE adverts SET active='0' WHERE enddate<CURDATE()

answered Oct 06 '13 at 10:43

Milan Zavišić

301
1
9

php code for checking current date is newer than date in database

2 Answers2