Search in between two different dates

Question

I am trying out a search function. But instead of searching from a specific date, I am trying to search from a range of date so that it only displays data I want.

<form action ="searchreceipt.php" method ="post">
      <input name="start" type="date" size="30" required />
      <input name="end" type="date" size="30" required />
      <input type="submit" value="Search"/>
</form>
<?php
$output = '';

if(isset($_POST['search'])) {
$search = $_POST['search']; 
$search = preg_replace("#[^0-9a-z]i#","", $search); 


$mysqli = new mysqli(spf, dbuser, dbpw, db);
$query = $mysqli->query("SELECT * FROM submission WHERE date BETWEEN 'start' AND 'end'") or die ("Could not search");

while ($row = $query->fetch_array(MYSQLI_ASSOC)) {
    $officer_id = $row ['officer_id'];
    $sbranch_no = $row ['sbranch_no'];
    $carno = $row ['carno'];
    $cost = $row ['cost'];
    $area = $row ['area'];
    $receipt = $row ['receipt'];

    echo "<table border='1' style='width:50%'>";
    echo "<td>";
    echo "<b>Receipt ID: <a href ='transactiondetail.php?receipt=$receipt'>$receipt</b></a>";
    echo "<br><br>";
    echo "Used By: $officer_id";
    echo "<br><br>";
    echo "Officer Branch No: $sbranch_no";
    echo "<br><br>";
    echo "Cost: $cost";
    echo "<br><br>";
    echo "Area travelled: $area";
    echo "<br><br>";
    echo "</td>";

    }
    echo "</table>";
 }

 ?>

What type is the `date` column`? What is the error you are getting? — rollstuhlfahrer, Jan 18 '18 at 09:55
`"SELECT * FROM submission WHERE date BETWEEN 'start' AND 'end'"`, I think this is wrong. It looks like you are just using **start** and **end** as strings — Carl Binalla, Jan 18 '18 at 09:56

score 0 · Answer 1 · answered Jan 18 '18 at 09:57

0

You need to execute query like this

$startDate="2017-07-23";
$endDate="2018-01-01";
     $query = $mysqli->query("SELECT * FROM submission
     WHERE date BETWEEN '".$startDate."' AND '".$endDate."'")
        ;

answered Jan 18 '18 at 09:57

Bilal Ahmed

4,005
3
22
42

No. See about prepared and bound queries – Strawberry Jan 18 '18 at 10:55

score 0 · Answer 2 · answered Jan 18 '18 at 10:32

Your query must be (ie.select * from table between lowerdate and upperdate):

Here lowerdate is 2017-12-26 10:37:45 and upper date is 2017-12-27 09:38:37

SELECT * FROM `table_name` WHERE (field_name BETWEEN '2017-12-26 10:37:45' AND '2017-12-27 09:38:37')

This will must work.

score 0 · Answer 3 · edited Jan 18 '18 at 10:45

0

You needed to get parameter from header by using $_POST.

Try this below.

For more, you can refer here

$start = $_POST['start'];
$end = $_POST['end'];

$query = $mysqli->query("SELECT * FROM submission WHERE date BETWEEN '$start' AND '$end'") or die ("Could not search");

edited Jan 18 '18 at 10:45

Strawberry

33,750
13
40
57

answered Jan 18 '18 at 10:43

Deno

434
4
16

See about prepared statements – Strawberry Jan 18 '18 at 10:45

Search in between two different dates

3 Answers3