date return result wrong even the date is not store in the database

Question

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:


    
        Date: 
        

            
        Month: 
        


        Year: 
        


        
            All fields are required! Please fill it in!

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!
Click here to retry again';
   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 '.$userDate.' wasn'."'".'t found in database!
Click here to retry again';
    exit;
}

MySQL data structure and data screenshot:

Image Link: https://i.sstatic.net/3HN2F.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.

Giacomo1968 · Accepted Answer

Your issue might be strange, but will say that there are oddities in you coding of the date logic. Here is my test code based on your code:

// Set the test data.
$_POST_day = 31;
$_POST_month = 2;
$_POST_year = 1948;

// Get POST data
$day  = $_POST_day;
$month = $_POST_month;
$year  = $_POST_year;
$dob = implode('/', array($day,$month,$year));

// Date that user keyin
$userDate = $dob;
$dateTime = DateTime::createFromFormat('d/m/Y', $userDate);
$myFormat = $dateTime->format('Y-m-d');

echo $myFormat;

Which outputs the value of:

1948-03-02

I am 100% unclear what the value is of this line when you have the values right there:

$dob = implode('/', array($day,$month,$year));

Nor do I understand the value of this:

$userDate = $dob;

Why assign a new variable? Here is my reworking of that code:

// Set the test data.
$_POST_day = 31;
$_POST_month = 2;
$_POST_year = 1948;

// 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;

Here is my breakdown of the adjustments to simplify that. First, I am actual processing the values by using str_pad and intval. Using intval ensures your values are actually numbers. And str_pad ensures that the digits are properly formatted to 2 digits (days & months) or 4 digits (year) using STR_PAD_LEFT. Works great.

Then for the DateTime::createFromFormat I am standardizing the actual date format to Y-m-d. Assuming you are passing it to DateTime to properly get a date out of the input? Meaning if a user enters 9999 for a year, DateTime should catch that? Unclear. You might as well forgo that part for testing & just do this instead:

// 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);
$myFormat = implode('-', array($year,$month,$day));

Notice how I am skipping the whole DateTime::createFromFormat logic. Because you are already formatting it manually anyway? But again, unclear.

Would also recommend looking at your database settings for the dob field to see if those could be choking it.

EDIT Okay, looking at your edits & data structures, I think the issue is with the query itself:

//Query string and put it in a variable.
$query = "SELECT * FROM $table_data WHERE DATE(dob) = :date";

The DATE() function in that query is strictly to format a date within MySQL. So you already have it formatted properly in the database, right? Why do that? I think the query should be:

//Query string and put it in a variable.
$query = "SELECT * FROM $table_data WHERE dob = :date";

ANOTHER EDIT Okay, looking at how my code fixed are placed in your code, this is wrong:

// 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));

First, why are there two ;; after each $_POST value like this: $_POST_day = $_POST['day'];;. That said, you are repeating the first problem I am absolving because—I will be honest—you do not seem to understand how any of the code works & are just cutting and pasting clips from whoever you find things. Here is how it should be:

// 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));

You just set the $_POST stuff right into that code. I created those $_POST_day, $_POST_month & $_POST_year variables for testing since I am not going to post a form to a test page for a few lines of code.

Also—as another user points out the date 31/2/1948 is returning 3/3/1948 because there are only 28 days in February (Month number is 2) so when you set they days to 31, the $dateTime->format is doing what it knows how to do and is calculating a proper date from an improper one. The remainder of 31 - 28 is 3. And the next month would be March. So the date it believes you are attempting to make is 3/3/1948.

But again, we realize you are truing to learn, but simply pasting bits of code together from wherever without actually understanding how it works will only cause you more & more headaches like this as time goes on. So you need to borrow code, understand what that does & then reshape it into your code based on you needs.

date return result wrong even the date is not store in the database

Answers (2)

Related Questions