PHP check database date field with a php date

Question

Basically i'm doing a simple wedding planner and i am trying to check whether the date that the user has inserted as available or whether the venue is booked. Basically, it's falling over with my SQL query. Initially i am setting the time:

$time = mktime(0, 0, 0, $month, $day, $year);

Then i have an sql query where i am checking it against an id variable that is previously set and the date that is passed through.

$sql2 = "SELECT * FROM venue_booking WHERE date_booked = ".$time." AND venue_id =".$id;

In this case the date in the database is 2015-01-01 and the date i'm passing through is 2015-01-01, I am checking this in an if statement if the amount of rows returned from the database is greater than 0 then echo booked, else echo available.

Even if the output is meant to say booked it still says available. Is there an issue with the way i am checking the time against mysql's date.

date_booked - This is a MySQL date (2014-01-01)

Anybody have any ideas?

what is the datatype of your field date_booked. it looked that your db data type is datetime but you are passing integer. — Junaid Atique
– Junaid Atique, Commented May 12, 2014 at 18:43

Community · Accepted Answer · 2017-05-23 11:50:23Z

Your current query is missing quotes around your date string so it wouldn't work as it is.

But to answer your question, just pass a valid date string in YYYY-MM-DD format and your query would work:

$date = $_POST['date'];
// Put date validation code here. I.e. make sure it is in YYYY-MM-DD 
// format, etc. Might as well escape it, too since you aren't using 
// prepared statements.
$sql2 = "SELECT * FROM venue_booking WHERE date_booked = '".$date."' AND venue_id =".$id;

I should also mention that you should probably switch to using prepared statements as it will make using user-provided data in queries safer.

Here is a possibly useful example of date validation. If you need to convert the date from one format to another, this will show you how.

Marc B · Accepted Answer · 2014-05-12 18:43:39Z

0

mktime() returns a PHP timestamp, which is a unix timestamp, which is "seconds since the epoch", aka Jan 1/1970. You cannot directly compare that against a mysql date field. You'd literally be doing

WHERE '2014-01-01' = 12345678

Try

$ts = date('Y-m-d h:i:s', mktime(0,0,0,$month, $day, $year));

$sql = "SELECT ... date_booked = '$ts'";

answered May 12, 2014 at 18:43

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

PHP check database date field with a php date

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related