MySQL和PHP查询日期= $ date问题

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.

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

Where am I going wrong?

I thank you in advance.

Answer 1

Try:

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

Note the single quote around the $dob.