How to compare two datetime in mysql query using php

Question

I want to compare two datetime variable witch one of them is current server time and one of them is stored in mysql database i am running this simple piece of code and it wont work and says it is a syntax error i am wondering what can i do

    <?php include("includes/student_session.php");?>
<?php confirm_logged_in();?>
<?php include("includes/connection.php");?>
<?php 
        date_default_timezone_set("");
        $now_date=date("Y-m-d H:i:s");

    $query1="select exam_id from exam where {$now_date} > start_date";
    $result=mysqli_query($cnn,$query1);
    if($result){

    $row=mysqli_fetch_array($result);
    echo $exam_id. "exists";

    }else
    {echo mysqli_error($cnn);}

?>

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'query1' at line 1 — Morteza Azmoodehafshar
– Morteza Azmoodehafshar, Commented Sep 17, 2014 at 7:58

0x47686F7374 · Accepted Answer · 2014-09-17 07:57:57Z

3

In this code:

$query1="select exam_id from exam where {$now_date} > start_date";
$result=mysqli_query($cnn,@query1); // <-

You are using @query1, this should be $query1.

answered Sep 17, 2014 at 7:57

0x47686F7374

4442 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Huafu Over a year ago

yup that's it, additionally you have to be sure to have the mysql server using the same timezone as your apache/php one. The best is to configure in mysqld.ini and in php.ini the timezone so that it is using UTC

Morteza Azmoodehafshar Over a year ago

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '12:30:21' at line 1 @phantom

0x47686F7374 Over a year ago

You should be using timestamps to compare date/time values. As stated in the answer by Raheel, you should be using start_date > '{$now_date}' (or <, no quotes req. if you're using timestamps) instead of {$now_date} > start_date.

Raheel · Accepted Answer · 2014-09-17 08:00:52Z

1

$query1="select exam_id from exam where {$now_date} > start_date";
$result=mysqli_query($cnn,@query1);

I think the error is because you are using @ insteal of $. Also your query seems to be wrong. You should compare values with fields change it to

   $query1="select exam_id from exam where start_date > '{$now_date}'";
   $result=mysqli_query($cnn,$query1);

answered Sep 17, 2014 at 8:00

Raheel

9,06410 gold badges65 silver badges108 bronze badges

Comments

YuS · Accepted Answer · 2014-09-17 08:11:47Z

1

Even if correct the misstype of @query1 the query will not work like this. $now_date is a string and you have to pass it to the query like a string. Even better if you format it with mysql's functions, not with php's one as well as compare time stored in database with database's own time (then you can skip any timezone problems). So, your query, for example, could be like this:

SELECT exam_id FROM exam WHERE DATEDIFF(NOW(), DATE_FORMAT(start_date,'%Y-%m-%d %T')) > 0;

answered Sep 17, 2014 at 8:11

YuS

2,0531 gold badge15 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to compare two datetime in mysql query using php

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related