MySQL and PHP query date = $date problems

Question

I'm having a simple problem with a query comparing a date in a table to a date input from a user. (give me a list of all people in the table with birthdate "MM/DD/YYYY").

When entering a new person into a db I want to verify that that person is not already in the db. So using "firstname" "lastname" and "date of birth" the table is queried and if there is a match, a window pops up and says "are you sure you want to do this - looks like the person is already entered".

There is NO PROBLEM with "firstname" "lastname" (I've got that working nicely) so now I want to add "date of birth" to the query. As a start, I removed "firstname" and "lastname" and am only using "date of birth".

As a "proof of concept" the following MySQL query works fine on the MySQL Workbench:

SET @testdate = "1934-06-06";
SELECT localid, firstname, lastname, dob FROM administrative WHERE dob = @testdate;

The "dob" column in the table is a "date" format not "datetime".

So I now switch over to php and write this as a test:

$frontenddob = "06/06/1934";
$dob = date("Y-m-d", strtotime($frontenddob));

echo "--$frontenddob--<br>"; //gives 06/06/1934
echo "--$dob--<br><br>";     //gives --1934-06-06-- ("--" added to "see" extra spaces)
echo "$dob"; echo "<br>";    //gives 1934-06-06

$host = "xx";
$user = "xx";
$password = "xx";
$dbname = "xx";

$cxn = mysqli_connect($host,$user,$password,$dbname);
if (mysqli_connect_errno()) {echo "No connection" . mysqli_connect_error();}

$query = " SELECT * FROM administrative WHERE dob = $dob ORDER BY lastname ASC  ";

The user types in a date string in the format mm/dd/yyyy.

It is converted to date format by the date("Y-m-d", strtotime($frontenddob));.

The "echos" show that the conversion is correct - the "echo $dob" gives me 1934-06-06.

The query works fine with <=, >=, <, > and I've tried everything else =, ==, ===, >=$date AND <=$date, and a raft of others - all unsuccessful.

The format in the table for dob (date of birth) is "date" not "datetime".
The user input string date format is converted to MySQL date format YYYY-MM-DD.
The query works with <, >, <=, >=.

Where am I going wrong?

I thank you in advance.

you never mention if it is not giving record or giving error, but as answer by @nil'z you miss single quote around $dob. — Sumit Gupta
– Sumit Gupta, Commented Sep 17, 2013 at 12:12
Use var_dump to debug variables etc. instead of echo "--$dob--<br><br>"; //gives --1934-06-06-- ("--" added to "see" extra spaces)... — Glavić
– Glavić, Commented Sep 17, 2013 at 12:21
@Sumit Gupta - Thanks for your answer! I convert the array to json and echo the json - all I got was "[]". Problem is solved by adding single quotes as noted by you and suggested by Nil'z. Thanks again! — TimSPQR
– TimSPQR, Commented Sep 17, 2013 at 12:24
I'll play around with "var_dump" today - never used it. Being a noob, I often don't trust myself and like to "see" a string to make sure I haven't screwed something up. Thanks for your response! — TimSPQR
– TimSPQR, Commented Sep 17, 2013 at 12:26
@TimSPQR print_r is nice function like var_dump, and both are good at seeing what variable hold. — Sumit Gupta
– Sumit Gupta, Commented Sep 18, 2013 at 5:54

Amal · Accepted Answer · 2013-09-17 12:14:36Z

5

Try:

$query = " SELECT * FROM administrative WHERE dob = '$dob' ORDER BY lastname ASC  ";
                                                    ^    ^

Note the single quote around the $dob.

edited Sep 17, 2013 at 12:14

Amal

76.8k18 gold badges133 silver badges154 bronze badges

answered Sep 17, 2013 at 12:10

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

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

3 Comments

TimSPQR Over a year ago

Yes, Yes and Yes!!! Thanks very much!!! It works perfectly now. But I guess I'll have to spend some time looking at single quote requirements. I have quite a few queries with php variables $xxxx that work fine without any quotes. The only single quotes I put around the comparison variable are LIKE '%xx%'. Obviously I need to study this a bit more. What is sad is that I put double quotes around it before I posted this, and it didn't work either. So, I thank you VERY VERY much for your answer and insight.

Nil'z Over a year ago

Happie to help buddy :D

Carlos2W Over a year ago

If this $dob come from user_input (javascript, html, asp..), you SHOULD really think about sanitizing the variable before putting it in your select!

TimSPQR · Accepted Answer · 2013-09-18 20:05:02Z

I've been playing a bit with the concept of strtotime vs explode and learned in another thread that strtotime is good up until 12/13/1901. So to help other noobs such as myself, based on Alexandr's suggestions, I wrote a few lines that demonstrate the differences between the two. It's interesting to put in 1901-12-13 and 1901-12-14 I the first line of the php code. Thanks again to everyone!

<?php $dob = "1901-12-14"; ?>
<html>
<head>
<style>

.inputdiv {
    width:200px;
    margin:100px auto 10px auto;
    background-color:#CCC2FC;
    text-align:center;
    border:1px solid transparent;}

.spacer{
    width:199px;
    margin:20px auto 20px auto;}    

</style>
</head>
<body>

<div class="inputdiv">
  <div class="spacer"><?php echo "Raw dob: ".$dob ?></div>
  <div class="spacer"><?php echo "Strtotime dob: ".date("m-d-Y", strtotime($dob)) ?></div>
  <div class="spacer"><?php list ($y, $m, $d) = explode('-', $dob);
                        $dob = sprintf("%02d-%02d-%04d", $m, $d, $y);
                        echo "Explode dob: ".$dob ?></div>
</div>
</body>
</html>

A P · Accepted Answer · 2013-09-17 15:24:49Z

-2

except using quotes, try to avoid strtotime in BIRTHDAY, because it generates timestamp (IT STARTS FROM YEAR 1970). try to use explode() for example

$frontenddob = "12/06/1934";
list ($m,$d,$y) = explode('/', $frontenddob);
$dob = sprintf("%04d-%02d-%02d", $y, $m, $d);

edited Sep 17, 2013 at 15:24

answered Sep 17, 2013 at 12:20

A P

4573 silver badges7 bronze badges

5 Comments

TimSPQR Over a year ago

Very nice. I've seen "explode" before, but never used it. I'll play with it today. Thanks very much for your comment!

A P Over a year ago

those who minuses, probably don't know timestamp starts from year 1970

Amal Over a year ago

Try to avoid strtotime() -- why? because it generates a timestamp -- well, that's what the function does.

A P Over a year ago

lots of birthdays year is less then 1970, if you know what i mean

TimSPQR Over a year ago

Very good point. Many of my birthdays in the db are before 1940 - so I'll definitely play with expolode today. Thank you all again!

Collectives™ on Stack Overflow

MySQL and PHP query date = $date problems

3 Answers 3

3 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related