How to show records between Dates in search?

Question

It only shows 'No record found' but I'm trying to show the records between the dates using search

<?php

$query = "SELECT title, count(title) as totalnotary , notary_date, book_no 
          FROM tbl_notary ";
// Date filter
if (isset($_POST['search'])) {
    $fromDate = $_POST['fromDate'];
    $endDate  = $_POST['endDate'];

    if (!empty($fromDate) && !empty($endDate)) {
        $query = "SELECT title, count(title) as totalnotary , 
                         notary_date, book_no 
                  FROM tbl_notary 
                  Where 1 
                  and notary_date between '" . $fromDate . "' and '" . $endDate . "'";
    }
}

// Sort
$query .= " group by book_no,title,Year(notary_date),
                     month(notary_date),day(notary_date) 
            ORDER BY notary_date DESC";
$Records = mysqli_query($conn, $query);

// Check records found or not
if (mysqli_num_rows($Records) > 0) {
    while ($Record = mysqli_fetch_assoc($Records)) {
        $book_no     = $Record['book_no'];
        $title       = $Record['title'];
        $totalnotary = $Record['totalnotary'];
        $notary_date = date("F j,Y", strtotime($Record['notary_date']));

        echo "<tr>";
        echo "<td>" . $book_no . "</td>";
        echo "<td>" . $title . "</td>";
        echo "<td>" . $totalnotary . "</td>";
        echo "<td>" . $notary_date . "</td>";
        echo "</tr>";
    }
} else {
    echo "<tr>";
    echo "<td colspan='4'>No record found.</td>";
    echo "</tr>";
}
?>

**Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Apr 18 '19 at 12:07
Possible duplicate: https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments/22662582#22662582 — Dharman, Apr 18 '19 at 12:10
You are NOT checking for any errors! Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any mysqli_ errors to generate an Exception that you can see on the browser as well as normal PHP errors. — RiggsFolly, Apr 18 '19 at 12:19
I do not see any code that connects this script to the database? — RiggsFolly, Apr 18 '19 at 12:19
i'm using include('../controllers/conn.php'); in top of my codes — grim reaper, Apr 18 '19 at 12:24
there is no error but it just only shows no record found if i click the search button — grim reaper, Apr 18 '19 at 12:43
If you echo your query where you select date and c/p it to phpmyadmin or whatever tool you are using, what result do you get? — Budimir Skrtic, Apr 18 '19 at 12:51
i would get 'no record found' but if i didn't select a date it shows the records in the table — grim reaper, Apr 18 '19 at 12:56

score 0 · Answer 1 · answered Apr 18 '19 at 13:05

0

OK, so you are saying that here is your problem.

$query = "SELECT title, count(title) as totalnotary , notary_date, book_no 
              FROM tbl_notary 
              Where 1 //!!!!!What is this 1???? You are missing a column name probably
              and notary_date between '" . $fromDate . "' and '" . $endDate . "'";

Try this query. You have WHERE 1 which can't do anything and probably is failing your query. Try with some error reporting and you will probably get an error there.

$query = "SELECT title, count(title) as totalnotary , notary_date, book_no 
              FROM tbl_notary 
              WHERE notary_date BETWEEN '" . $fromDate . "' AND '" . $endDate . "'";

Then put this below to see if query is failing or not.

$Records = mysqli_query($conn, $query) or die (mysqli_error($conn));

answered Apr 18 '19 at 13:05

Budimir Skrtic

419
3
12

Thank you for the code but the result is also the same – grim reaper Apr 18 '19 at 13:11
1

`where 1` is totally legal, but completely unnecessary in this case – RiggsFolly Apr 18 '19 at 13:14
Can you echo `$query` and display what you get? – Budimir Skrtic Apr 18 '19 at 13:29
SELECT title, count(title) as totalnotary , notary_date, book_no FROM tbl_notary group by book_no,title,Year(notary_date),month(notary_date),day(notary_date) ORDER BY notary_date DESC it is what i would get if i echo $query – grim reaper Apr 18 '19 at 13:35
I'm interested in seeing WHERE part of that query, that's why I'm asking for echo of complete query? – Budimir Skrtic Apr 18 '19 at 13:38
if i select a dates this is i would get if i echo $query SELECT title, count(title) as totalnotary , notary_date, book_no FROM tbl_notary Where notary_date between 'April 1,2019' and'April 12,2019' group by book_no,title,Year(notary_date),month(notary_date),day(notary_date) ORDER BY notary_date DESC – grim reaper Apr 18 '19 at 13:45
`between 'April 1,2019' and'April 12,2019'` is your problem. Put it like `between '2019-04-01' and '2019-04-12'` and it will work – Budimir Skrtic Apr 18 '19 at 13:47
No. You format it only for user preview. For DB query you use it as I showed in a comment. – Budimir Skrtic Apr 18 '19 at 13:51

How to show records between Dates in search?

1 Answers1