更正日期格式

Question

I have a php form which contains fields for start date and end date. I have already stored the rows in my mysql database and I would like to display the records within the range of start and end dates. I have a column called tdate of datetime value. My form displays the start and end dates in mm/dd/yyyy but mysql doesn't accept that.

Updated:-

$edate = $_POST['edate'] ?? '';
$sdate = $_POST['sdate'] ?? '';
$endDate = date('Y-m-d', strtotime($edate));
$startDate = date('Y-m-d', strtotime($sdate));
if(isset($_POST['submit'])){
    $sql = "SELECT * FROM admin_master_tbl ";
$sql .= "WHERE tdate <= '$endDate' AND ";
$sql .= "tdate >= '$startDate' ";
$result = mysqli_query($conn, $sql);
$output = mysqli_fetch_array($result);
print_r($output); } 

Ok now it works on my form!

Answer 1

Use prepared statements and convert your date strings into a format which can be used by MySQL as dates. This answer is the MySQL centric one, which uses STR_TO_DATE to convert your mm/dd/yyyy strings into dates.

$sql = "SELECT * FROM admin_master_tbl ";
$sql .= "WHERE tdate <= STR_TO_DATE(?, '%m/%d/%Y') ";
$sql .= " AND tdate >= STR_TO_DATE(?, '%m/%d/%Y')";
$stmt = $con->prepare($sql);
$stmt->bind_param("ss", $edate, $sdate);
$stmt->execute();
$stmt->close();

The first parameter to bind_param(), namely "ss" tells PHP that we are binding two string parameters to the query.

An alternative to STR_TO_DATE would be to convert your PHP date strings into an ISO format in PHP. In that case, you wouldn't need STR_TO_DATE, but using prepared statements would still be a good idea.