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.
Where am I going wrong?
I thank you in advance.
Try:
$query = " SELECT * FROM administrative WHERE dob = '$dob' ORDER BY lastname ASC ";
^ ^
Note the single quote around the $dob.
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);
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>