I’m having a problem with my date search in the database. My database doesn't have (dd/mm/yyyy) 31/2/1948 and so with other years (start from 1940 to 2021) but whenever I submit the form to test with the value that not found in database, it automatically return the result to the following month date.
For example: I submit 31/2/1948, it returns the result of 3/3/1948.
Here is my submit form code:
<form method="post" action="./result" id="searchform">
<div class="container">
<span class="label">Date:</span> <input type="text" id="f1" name="day" maxlength="2" placeholder="01" /><!-- Date -->
<br /><br />
<span class="label">Month:</span> <input type="text" id="f2" name="month" maxlength="2" placeholder="01" /><!-- Month -->
<br /><br />
<span class="label">Year:</span> <input type="text" id="f3" name="year" maxlength="4" placeholder="1950" /><!-- Year -->
<br /><br />
<div class="button">
<div id="errormsg">All fields are required! Please fill it in!</div>
<input class="submit-btn" type="submit" name="submit" value="Search" /><!-- Search -->
</div>
</div>
</form>
And here is the result.php where it query from the database.
//Check input if empty or people use the direct link and exit the script
if(empty($_POST['day']) && empty($_POST['month']) && empty($_POST['year'])) {
echo 'Please don'."'".'t try to hack!<br /><a href="./" title="Retry">Click here to <b>retry</b> again</a>';
exit;
}
// Set the test data.
$_POST_day = $_POST['day'];;
$_POST_month = $_POST['month'];;
$_POST_year = $_POST['year'];;
// Get POST data
$day = str_pad(intval($_POST_day), 2, 0, STR_PAD_LEFT);
$month = str_pad(intval($_POST_month), 2, 0, STR_PAD_LEFT);
$year = str_pad(intval($_POST_year), 4, 0, STR_PAD_LEFT);
$userDate = implode('-', array($year,$month,$day));
// Date that user keyin
$date_format = 'Y-m-d';
$dateTime = DateTime::createFromFormat($date_format, $userDate);
$myFormat = $dateTime->format($date_format);
echo $myFormat;
//Query string and put it in a variable.
$query = "SELECT * FROM $table_data WHERE dob = :date";
//Prepare data from database
$stmt = $db->prepare($query);
$stmt->execute(array('date' => $myFormat));
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
if ( !$data ) {
echo 'The data <strong>'.$userDate.'</strong> wasn'."'".'t found in database!<br /><a href="./" title="Retry">Click here to <b>retry</b> again</a>';
exit;
}
MySQL data structure and data screenshot:
Image Link: http://i.imgur.com/RxjPb0d.png
I tried with the date which not in the database, for example 31/2/2040, it return the $data
not found message, but if the year range (1940 - 2021) that already store in my database, it always returns me to the following month.
How do I force it to return me the error message if people key in 31/2/19xx which not in the database rather than returning the following month result?
Additional info:
- My date is stored in MySQL database using the DATE format.
- I'm using jQuery live validation to check the input field, as the user need to manually key in the date and not using datepicker script, so there is chances people may enter wrongly.
- I prefer to stick back to the current script rather than datepicker as the date range is from 1940 to 2021, therefore it may be a trouble for user to select back to 1940. Furthermore the user is not computer savvy that's why I void out the datepicker script even I know that the datepicker will save me from this trouble.