Sort Table via Date Picker Range

Question

I'm trying to sort the data table via date picker range (server side) but can't get the date sorting part to work properly. Surely there are missing codes and other problems.

What am I missing?

<html lang="en">
<head>
<title>List</title>
<script src="jquery-3.3.1.min.js"></script>
</head>
<body>
<h1 align="center">List</h1>
</br>
</br>
</br>
        <center> <p class="search_input">
        <form method="post" action="#">
        <input type="date" name="dateFrom">&nbsp;&nbsp;&nbsp;<input type="date" name="dateTo">&nbsp;&nbsp;&nbsp;
        <input type="submit" name="range" id="range" class="btn-info" />
        </form>
        </center>

<table align="center" cellspacing="0" cellpadding="0">
  <thead class="fixedthead">
  <th width="120px" style="text-align: center; color: navy">Name</th>
  <th width="120px" style="text-align: center; color: navy">Description</th>
  <th width="120px" style="text-align: center; color: navy">Date</th>
  <th width="120px" style="text-align: center; color: navy">Open</th>
  </thead>

  <?php 
//retrieve content via data picker range
$dateFrom = $_POST['dateFrom'];
$dateTo = $_POST['dateTo'];


  $conn = mysqli_connect("localhost", "root", "", "order");
      // get results from database
  $result = mysqli_query($conn, "SELECT * FROM order.item WHERE date BETWEEN '$dateFrom' AND '$dateTo' ", MYSQLI_USE_RESULT)
  or die(mysqli_error($conn));
  while($row = mysqli_fetch_array( $result )) {
  ?>

    <tbody>   
    <tr>     
    <td width="120px" style="text-align: center"><?php echo $row['name']; ?></td>
    <td width="120px" style="text-align: center"><?php echo $row['description']; ?></td>
    <td width="120px" style="text-align: center"><?php echo $row['date']; ?></td>
    <td width="120px"><a href = "download.php?id=<?php echo $row['id']; ?>" style='text-decoration:none;'><button>View</button></a></td>
   </tr>
  </tbody>
</table><br><br><br>
<?php
}
?>

</body>
</html>

Any help is appreciated. Thanks guys!

First and foremost, you wan to read about [SQL Injection](http://php.net/manual/en/security.database.sql-injection.php), your code is **vulnerable**! Second, what are the percent signs doing in that expression, `BETWEEN '$dateFrom%' AND '$dateTo%'`? — sticky bit, Sep 01 '18 at 02:18
@sticky bit - That actually came from the SQL LIKE Operator that I forgot to take out. — David, Sep 01 '18 at 02:25
What's a `LIKE` doing on a date? They aren't stored as `varchar`, are they? If they are, change that and use a proper data type like `date`. — sticky bit, Sep 01 '18 at 02:28
@stickybit - yes, I agree. I was just testing and took it out after. — David, Sep 01 '18 at 02:30
don't mix myqli_ and mysql_ functions. (i see a reference to mysql_error) — spencer7593, Sep 01 '18 at 02:43
Possible duplicate of [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) — JBH, Sep 01 '18 at 02:46
You have a database called `order`, with a table called `item`? — Strawberry, Sep 01 '18 at 08:16
@Strawberry - Hello! Yes, that is correct. I've updated the code above as to where I am now, though it is still not retrieving anything. — David, Sep 01 '18 at 12:42

JBH · Answer 1 · 2018-09-01T02:27:09.433

0

Knowing nothing about your database...

$last_five_days = time() - 432000;
$sql = "SELECT * FROM order.item WHERE order.date > ".$last_five_days." ORDER BY order.date ASC";

If this answer doesn't help, then you need to provide us with more information about what you mean by "date picker range."

ORDER BY {column} ASC|DESC orders the rows by the indicated column. It defaults to ASC.

I'm using the WHERE clause to set up your last-five-days requirement. I'm assuming order.date (whatever you've actually named that) is storing a simple UNIX timestamp.

edited Sep 01 '18 at 02:27

answered Sep 01 '18 at 02:09

JBH

1,823
1
19
30

1

The `ORDER` clause comes **after** the `WHERE` clause. – sticky bit Sep 01 '18 at 02:22
@stickybit, Yes it does! Thanks for catching that. – JBH Sep 01 '18 at 02:27
@JBH - Hi! I've added a screenshot above. The date picker is FROM and TO, it will supposedly sort the content via that specified date range. – David Sep 01 '18 at 02:31
@David, just to be clear, the "last 5 days" issue is irrelevant because the user is supplying both sides of the date range? – JBH Sep 01 '18 at 02:41
@JBH - Yes, that for a different concept but totally irrelevant in the date range sorting. – David Sep 01 '18 at 02:44
OK! Please note that how to query between dates has already been answered on the site. So I've flagged your question as a duplicate. Follow the link that appears in your question's comment chain to get to the answer. (This is normal for SO, BTW). Use the `ORDER BY {} ASC|DESC` clause to sort the list. – JBH Sep 01 '18 at 02:47

Sort Table via Date Picker Range

1 Answers1