Number of Records Between two datetime

Question

It gives me this error when I run the script: Call to a member function fetch_assoc() on boolean. It is driving me crazy.

I get data from input box like this: 2017-12-14 06:42:10

$sql = "SELECT count(*) as total FROM `purchase` where purchase_datetime BETWEEN str_to_date($date1, 'Y-m-d H:i:s') and str_to_date($date2, 'Y-m-d H:i:s')";

or

$sql = "SELECT count(*) as total FROM `purchase` where purchase_datetime BETWEEN '$date1' and '$date2'";

or
Trying This:

$date1 = date('Y-m-d H:i:s', $date1);
$date2 = date('Y-m-d H:i:s', $date2);

FULL CODE:

            <?php
            if (isset($_POST['submit']) && isset($_POST['date1']) && isset($_POST['date2'])) {
                $date1 = $_POST['date1'];
                $date2 = $_POST['date2'];

                $date1 = date('Y-m-d H:i:s', $date1);
                $date2 = date('Y-m-d H:i:s', $date2);


                $sql = "SELECT count(*) as total FROM `purchase` where purchase_datetime BETWEEN '$date1' and '$date2'";
                $query_result = $conn->query($sql);
                $rows = $query_result->fetch_assoc();
                echo "Result is: " . $rows['total'] . " purchases";
            }
            ?>

Tip: use `$result = $conn->query($sql) or die($conn->error);` to see the error in your SQL. — Racil Hilan, Dec 14 '17 at 17:25
`It gives me this error: Call to a member function fetch_assoc() on boolean. It is driving me crazy.` -> please show your full code — Alive to die - Anant, Dec 14 '17 at 17:25

Racil Hilan · Answer 1 · 2017-12-14T17:35:42.420

The date function is to format today's date, not a given date. Use the DateTime::format function to format a given date. Try this:

$date1 = $date1->format('Y-m-d H:i:s');
$date2 = $date2->format('Y-m-d H:i:s');
$sql = "SELECT COUNT(*) AS total FROM purchase WHERE purchase_datetime BETWEEN '$date1' AND '$date2';";

Alternatively, you can use the date_format alias function like this:

$date1 = date_format($date1, 'Y-m-d H:i:s');
$date2 = date_format($date2, 'Y-m-d H:i:s');
$sql = "SELECT COUNT(*) AS total FROM purchase WHERE purchase_datetime BETWEEN '$date1' AND '$date2';";

score 0 · Answer 2 · answered Dec 15 '17 at 09:08

0

make sure your format as:

$date1=$_POST['date1']//1496620800;
$date2=$_POST['date2']//1496707200;
$date1 = date('Y-m-d', $date1);//2017-06-05
$date2 = date('Y-m-d', $date2);//2017-06-06

purchase_datetime format:2017-xx-xx

answered Dec 15 '17 at 09:08

cyan ji

1

Number of Records Between two datetime

2 Answers2