Date Format between PHP and SQL with user driven parameters-CodePudding

I'm simply trying to get data out of a DB between 2 dates. these dates are given thru user input.

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
        startdate: <input type="date" name="from_date">
        enddate: <input type="date" name="to_date">
        <input type="submit" name="date" id="date">
    </form>

But I get no data because I think the date Format is wrong.

 if (isset($_POST["date"])) {
       echo $startDate = date("Y-m-d", strtotime($_POST['from_date'])); // Y-m-d strtotime
      echo  $endDate = date("Y-m-d", strtotime($_POST['to_date']));
      echo gettype($startDate); // something weird is happening with the dates random dates slip between date ranges

        $sql = "SELECT A,C,D,F,G,H,I,K,created_timestamp,changed_timestamp,dep,resp FROM TABLE_data WHERE created_timestamp > ?  and created_timestamp < ?";
        //$sql = "SELECT A,C,D,F,G,H,I,K,created_timestamp,changed_timestamp,dep,resp FROM TABLE_data WHERE created_timestamp > '2021-01-01'  and created_timestamp < '2021-01-31'";
        
        

        $stmt = $db->prepare($sql);
        $stmt->execute([$startDate, $endDate]);
        //$stmt->execute();
        $result = $stmt->fetchAll();

this Query works :

$sql = "SELECT A,C,D,F,G,H,I,K,created_timestamp,changed_timestamp,dep,resp FROM TABLE_data WHERE created_timestamp > '2021-01-01'  and created_timestamp < '2021-01-31'";

this one does not work :

 $sql = "SELECT A,C,D,F,G,H,I,K,created_timestamp,changed_timestamp,dep,resp FROM TABLE_data WHERE created_timestamp > ?  and created_timestamp < ?";

Can someone help me with the Format of these dates because I am completely lost, I tried to switch up formats but was not successful ?

Thanks in advance.

CodePudding user response：

if (isset($_POST["date"])) {
$startDate = date("Y-m-d", strtotime($_POST['from_date']));
$endDate = date("Y-m-d", strtotime($_POST['to_date']));
$sql = "SELECT

A,C,D,F,G,H,I,K,created_timestamp,changed_timestamp,dep,resp FROM TABLE_data WHERE created_timestamp > ? and created_timestamp < ?";

$stmt=$db->prepare($sql);$stmt->bind_param('ss',$startDate ,$endDate);
$stmt->execute();
$result= $stmt->get_result()->fetch_assoc();
$stmt->close();
}

CodePudding user response：

I think better use this way

echo $startDate = date("Y-m-d", strtotime($_POST['from_date'])); 
echo  $endDate = date("Y-m-d", strtotime($_POST['to_date']));
    
    
 $sql = "SELECT A,C,D,F,G,H,I,K,created_timestamp,changed_timestamp,dep,resp FROM TABLE_data WHERE created_timestamp > '$startDate'  and created_timestamp < '$endDate'";

$sql = "SELECT A,C,D,F,G,H,I,K,created_timestamp,changed_timestamp,dep,resp FROM TABLE_data WHERE created_timestamp > ?  and created_timestamp < ?";
    
    $stmt->bind_param("is", $startDate, $endDate); 
    $stmt->execute();