Create a filter button SQL query

Question

I'm creating a filter button on my SQL query. I have a form where a user can insert a date and then a button after it. Whenever a user clicks this button it only searches for that date in the table. I've created all this but it's not working.

It's not giving me an error or anything..

$mysqli = mysqli_connect("localhost", "root", "", "urenregistratie");

The button doesn't do anything. This is what I have:

<body>
   <div class="form">
   <p>
      <a href="dashboard.php">Dashboard</a> | <a href="view.php">Projecten inzien</a> | <a href="logout.php">Loguit</a>
   </p>
   <h2>Projecten:</h2>
   <table width="100%" border="1" style="border-collapse:collapse;">
      <thead>
         <tr>
            <th><a href="overzicht.php?sort=gebruiker">Gebruiker</a></th>
            <th><a href="overzicht.php?sort=projectname">Project naam:</a></th>
            <th><a href="overzicht.php?sort=aantaluren">Aantal uren</a></th>
            <th><a href="overzicht.php?sort=datum">Datum:</a></th>
      </thead>
      <tbody>
         <?php
            //SORT BY ASC OR DESC

            $sel_query="SELECT * FROM uren";

            if ($_GET['sort'] == 'gebruiker') {
                $sel_query .= " ORDER BY gebruiker";
            }elseif ($_GET['sort'] == 'projectname') {
                $sel_query .= " ORDER BY projectname";
            }elseif ($_GET['sort'] == 'aantaluren') {
                $sel_query .= " ORDER BY aantaluren DESC";
            }elseif ($_GET['sort'] == 'datum') {
                $sel_query .= " ORDER BY datum";
            }

            $result = mysqli_query($mysqli,$sel_query);
            while($row = mysqli_fetch_assoc($result)) {
            ?>
         <tr>
            <td align="center"><?php echo $row["gebruiker"]; ?></td>
            <td align="center"><?php echo $row["projectname"]; ?></td>
            <td align="center"><?php echo $row["aantaluren"]; ?></td>
            <td align="center"><?php echo $row["datum"]; ?></td>
         </tr>
         <?php } ?>
      </tbody>
   </table>
   <form name="filter" method="POST" action="overzicht.php">
      "<input type="hidden" name="uid" value="<?php echo $uid; ?>">
      SHOW POSTS FROM:
      <input type"date" name="date">
      <input type="submit" name="submit" value="Filter">
   </form>
   <?php
      //FILTER BUTTON

      if (isset($_POST['submit'])) {
        $uid = $_POST['uid'];
        $date = $_POST['date'];
        $filter = "WHERE DATUM(post_date) IS '$date'";
        $query = "SELECT * FROM uren $filter";
        $respost = mysqli_query($mysqli, $query);
      } else {
        $query = "SELECT * FROM uren";
        $respost = mysqli_query($mysqli, $query);
      }
      ?>
</body>

If it's not giving you an error, then what *is* it doing? What's the actual problem? Note that you never use the `$filter` variable at the bottom, nor do you do anything with the results of the queries at the bottom or check for errors from those queries. — David, Feb 12 '17 at 19:11
_I have an idea we have been here before but_ 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 cannot miss or ignore. — RiggsFolly, Feb 12 '17 at 19:19
You create the where, but do not add it to the select when you do the query for the filter. — Sloan Thrasher, Feb 12 '17 at 19:20
And `$query = "SELECT * FROM uren $filter";` would probably help actually run the filter instead of just placing the filter in a string — RiggsFolly, Feb 12 '17 at 19:21
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) _But I think I have said that to you before as well_ — RiggsFolly, Feb 12 '17 at 19:21
Thanks for the replies. I've edited the code so that the $filter variable does get used. I'm trying to update the already created table when I press the filter button. But it still doesn't work. — Erik Venema, Feb 12 '17 at 19:22
add this to your queries `mysqli_error($mysqli)` see if anything comes of it. — Funk Forty Niner, Feb 12 '17 at 19:23
`WHERE DATUM(post_date) IS` < that `IS` isn't part of mysql, far as I know. Edit: How about you @RiggsFolly ? and `DATUM()`? or is that your column name? that isn't a core mysql function — Funk Forty Niner, Feb 12 '17 at 19:28
Added the mysqli_error($mysqli) but still not getting anything. The page creates a table with all of the entries in the table. I'm trying to update that table with a filter button. But when I add anything in the text input and click the filter button it doesn't work — Erik Venema, Feb 12 '17 at 19:29
...it's probably not even making it there. In any case, someone popped an answer for you below, ask them now. — Funk Forty Niner, Feb 12 '17 at 19:30
@Fred-ii- You are right DATUM() is not a valid function but DATE() is — RiggsFolly, Feb 12 '17 at 19:43
@ErikVenema You cannot UPDATE a row using SELECT, you can UPDATE a row using the UPDATE syntax — RiggsFolly, Feb 12 '17 at 19:44
@RiggsFolly well, seeing `$row["datum"]` is quite questionable. Seems like a few flashlights and a very savvy rabbit are required here. — Funk Forty Niner, Feb 12 '17 at 19:44
This question is getting far too deep, what with this comment left under the answer *"Oh I forgot to update the new column name. I'm trying to filter the column datum from the input name datum. I currently have it like this: $query = "SELECT * FROM uren WHERE date(datum) BETWEEN '$date' AND '$date'"; – Erik Venema 3 mins ago"* - plus, we don't even know if this is being run as `http://localhost` of `file:///`. Too many unknowns, way too many. — Funk Forty Niner, Feb 12 '17 at 19:46

affaz · Answer 1 · 2017-02-12T19:33:24.200

0

Remove action from form and the filter query is not used anywhere

 <form name="filter" method="POST" action="">
            <input type="hidden" name="uid" value="<?php echo $uid; ?>">
            SHOW POSTS FROM:
            <input type"date" name="date">
            <input type="submit" name="submit" value="Filter">
        </form>
        <?php

        //FILTER BUTTON

        if (isset($_POST['submit'])) {
            $uid = $_POST['uid'];
            $date = $_POST['date'];

            $query = "SELECT * FROM uren WHERE DATUM(post_date)='$date'";
            $respost = mysqli_query($mysqli, $query);
        } else {
            $query = "SELECT * FROM uren";
            $respost = mysqli_query($mysqli, $query);
        }
        ?>

I think this is how you meant your code to be..

edited Feb 12 '17 at 19:33

answered Feb 12 '17 at 19:22

affaz

1,191
9
23

Thanks for the reply. I've updated the code with what you added but it's not updating the already existing table. Do I need to link that somewhere? – Erik Venema Feb 12 '17 at 19:31
Where is the update query in it?and what is DATUM supposed to be? – affaz Feb 12 '17 at 19:32
I think I'm missing the update query part. DATUM is a column in the table. – Erik Venema Feb 12 '17 at 19:34
then what is post_date?? – affaz Feb 12 '17 at 19:35
Oh I forgot to update the new column name. I'm trying to filter the column datum from the input name date. I currently have it like this: $query = "SELECT * FROM uren WHERE date(datum) BETWEEN '$date' AND '$date'"; – Erik Venema Feb 12 '17 at 19:42
@ErikVenema what is date between date and date..how can the same date be used in BETWEEN?it makes no sense – affaz Feb 13 '17 at 03:53

Create a filter button SQL query

1 Answers1