BETWEEN statement using PHP variables for dates

Question

im using $orderdate = date('Y-m-d H:i:s'); to store in MySQL datetime

but when retrieving sales report which has a form of

    $initialstartdate = $_POST['startdate'];
    $initialenddate = $_POST['enddate'];

    $startdate = " '$initialstartdate' ";
    $enddate = " '$initialenddate' ";

    $sql="SELECT * FROM orders 
    left join order_item on orders.order_id=order_item.order_id 
    left join products on products.product_id=order_item.product_id 
    left join category on products.category_id=category.category_id 
    where orders.order_date BETWEEN $startdate and $enddate;";

it is not showing dates and BETWEEN is not inclusive maybe there is a problem regarding H:i:s because it was working properly before changing this $orderdate = date('Y-m-d'); to this $orderdate = date('Y-m-d H:i:s');

i tried concatenating .'00:00:00' to $startdate and $enddate but it didnt work

(Possible) side note: Learn to use parameterized queries. Your program is vulnerable to SQL injections. — sticky bit
– sticky bit, Commented Jul 29, 2020 at 13:21
...but it is still testing... Actually there is no reason for "but" :) you should start to learn what @stickybit said immediately — B001ᛦ
– B001ᛦ, Commented Jul 29, 2020 at 13:24
Using prepared statements not only prevents SQL injections, but it also prevents quoting issues. Using prepared statements now will remove all of the headaches that $startdate = " '$initialstartdate' "; and $enddate = " '$initialenddate' "; is attempting to fix. — aynber
– aynber, Commented Jul 29, 2020 at 13:25
..i will use parameterized queries when my problem is solved. thanks... You don't get the point — B001ᛦ
– B001ᛦ, Commented Jul 29, 2020 at 13:26
Correct. Between is not inclusive. So 2020-07-28 - 2020-07-28 will show no results, 2020-07-28 - 2020-07-29 will only show results for 2020-07-28. To make it inclusive, you have to pass in the timestamp for beginning and ending of the days -- 2020-07-28:00:00:00 - 2020-07-28:23:59:59 will give all of the results for the 28th, 2020-07-29:23:59:59 as the end date will give results for the 28th and 29th — aynber
– aynber, Commented Jul 29, 2020 at 13:46

Рыбалко А.М. · Accepted Answer · 2020-07-29 13:51:50Z

2

Your code is absolutely not safe for sqlingections. Use PDO and SQL preparing. Like this:

$start_date = $_POST['startdate'];
$end_date = $_POST['enddate'];

$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);  

$DBH->prepare("SELECT * FROM orders 
    left join order_item on orders.order_id=order_item.order_id 
    left join products on products.product_id=order_item.product_id 
    left join category on products.category_id=category.category_id 
    where orders.order_date BETWEEN STR_TO_DATE(:start_date, '%Y-%m-%d %H:%i:%s') and STR_TO_DATE(:end_date, '%Y-%m-%d %H:%i:%s')");

$STH->bindParam(':start_date', $start_date, PDO::PARAM_STR);
$STH->bindParam(':end_date', $end_date, PDO::PARAM_STR);
$STH->execute();

answered Jul 29, 2020 at 13:51

Рыбалко А.М.

561 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

BETWEEN statement using PHP variables for dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related