Filter results based on user input

Question

I am currently working on a kind of "list", where users can put in different data to create a kind of log for various activities, specifically in simulators. I got the basics of it layed down and in fact fully up and running, however, theres still one component, rather critical to me. This being, the users ALSO being able to search for the author of the route. So look at it like this:

Name - Distance - Date

Bob - 100km - 01-01-2015

Jones - 200km - 01-01-2015

Jones - 250km - 01-01-2015

This, is essentially the list the users would see. If the users put in "Jones", they should only see the result of Jones. However, I cannot get this to work.

The code I am currently using to try to achieve the "filter" :

<div class="filters">
        <ul>
            <li>
                <form method="POST" action="index.php?category=trucking">
                    <input type="text" name="filterName" />
                    <input type="submit" name="submit" label="Search for Author" />
                </form>
            </li>
            <li>
                <a href="index.php?category=addEntryFT"><b>Add a entry</b></a>
            </li>
        </ul>
    </div>
    <div class="results">

        <?php
            // Get from database
                      if(isset($_POST['submit'])) {

                      $filterName = $_POST['filterName'];

                      $result = mysqli_query($anslutning, "SELECT * FROM tblLogs WHERE category = 'trucking' AND author LIKE '$filterName'");
                        }

        ?>

So first I create a form where the user puts in a author, and whatever they put in, should apply. Two issues :

1) The form makes the page redirect to the previous page, and 2) it doesn't filter out anything

So in its very basic, pretty much a search bar that points to a database and checks the records there, and this is all I have to come with, which obviously does not work.

score 1 · Accepted Answer · answered Jan 11 '16 at 19:14

1

For ist issue:

Use empty action in form field becuase your all code available in a single file like:

<form method="post" action="">

For second point:

You need to add % sign for matching string like:

$result = mysqli_query($anslutning, "SELECT * FROM tblLogs WHERE category = 'trucking' AND author LIKE '%$filterName%'");

Side Note:

If you also want to use category as a form field than you can use hidden form input for category.

answered Jan 11 '16 at 19:14

devpro

16,184
3
27
38

This would have been the solution in my case. Thank you! – Xariez Jan 11 '16 at 20:06

score 1 · Answer 2 · edited May 23 '17 at 11:59

If you're looking for an exact match for "Jones" or the author in question, then don't use LIKE, use =.

WHERE category = 'trucking' AND author = '$filterName'

Then iterate over your results.

Sidenote: You can use mysqli_fetch_assoc() or mysli_fetch_array() depending on what you want to use here.

Another sidenote: Make sure the input does not contain whitespace. Use trim() against it.

Plus, using a GROUP BY col and possibly an ORDER by col added to your query.

while($row = mysqli_fetch_assoc($result)){

    echo $row['the_row_you_want_to_show'];

    echo "<br>";

    echo $row['the_OTHER_row_you_want_to_show'];

    // etc.

}

Check for errors against your query using mysqli_error($anslutning).

I.e.: if(!result) { echo "Error: " . mysqli_error($anslutning); }

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

N.B.:

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements.

Footnotes:

It's unsure as to why you're using action="index.php?category=trucking" since that implies a GET method being used and if used somewhere in code you did not show, then you will need to elaborate on that.

If you're not using it, then simply remove the ?category=trucking.

An example would be and if you intend on keep the action the way it is:

$cat = $_GET['category'];

// $cat = $_REQUEST['trucking']; // or $_REQUEST

and using it in your query as

WHERE category = '$cat' AND author = '$filterName'

Plus, it's best to check if your input is empty or not, so use a conditional !empty() against it.

if(!empty($_POST['filterName'])){ do something }

In not doing so and you've a live site already, then it could generate an error.

I know about SQL Injection but I had completely forgotten about it in this case. Thanks for pointing it out! — Xariez, Jan 11 '16 at 20:07
@Xariez You're welcome. Glad to see you got your solution. However, these are things that I did point out in my answer. I'm not asking you to change your decision on the acceptance (do keep the accepted answer), I'm just stating where the errors where and what could have done. *Cheers* — Funk Forty Niner, Jan 11 '16 at 20:08
Yeah, I see that. I appreciate everyones answers and i certainly did not expect this amount of both quantity and quality answers to the question above. Cheers! — Xariez, Jan 11 '16 at 20:43

score 0 · Answer 3 · edited May 23 '17 at 11:44

First you would need to avoid sql injections, the current approach exposes you to these. This SO post gives some good advice on how to.

Coming to your code, I think the below statement

    $result = mysqli_query($anslutning, "SELECT * FROM tblLogs WHERE category = 'trucking' AND author LIKE '$filterName'");

Would need to change so that you prefix/suffix or do both for the author name, like below

    $result = mysqli_query($anslutning, "SELECT * FROM tblLogs WHERE category = 'trucking' AND author LIKE '%{$filter_name}%'");

Filter results based on user input

3 Answers3