Fetch data from mysql database using drop down list and then use search function to filter data from selected drop down list option

Question

I want to comebine the use of a drop down list (with filters/options) with a search function (a search bar and a "Ok" button).

When I select a filter/option in the drop down list, as for example "Team". The drop down list then should be aware of that the list has changed to filter/option "Team". After this, I use the search function where I input the search term, like "USA" then press the Ok-button. Once the Ok-button is pressed, a sql-query like: SELECT * FROM mytable WHERE Team = 'USA'; is generated to fetch all the matched rows in the db "mytable".

Select a filter, "Team".
Select a team, "USA".
Fetch matched rows with relevant data from the db mytable.

Hope I made myself understood better, thank you all for your time!

This is the code I have:

<html>
    <head> 
        <title>AiFind</title>
        <link rel="stylesheet" href="Style.css">
        <script src="logic.js"></script>

    </head>
    <body>
        <h1>AiFind</h1>
    </body>

</html>


<?php

include "connection.php";

$sql = "SELECT * FROM mytable";

if (isset($_POST['search'])) {

    $search_term = mysql_real_escape_string($_POST['search_box']);

    $sql .= " WHERE F_ar = '$search_term' ";
    $sql .= " OR Postnr = '$search_term' ";
    $sql .= " OR Postort = '$search_term' ";
    $sql .= " OR Vardgivare = '$search_term' ";
    $sql .= " OR Team = '$search_term' ";
    $sql .= " OR Orsak = '$search_term' ";
    $sql .= " OR Planerat_datum = '$search_term' ";
    $sql .= " OR fran = '$search_term' ";
    $sql .= " OR AAA_diam = '$search_term'; ";
}

$query = mysql_query($sql) or die(mysql_error());

?>

<form name="Select_filter" method="POST" action="VGR_data_display.php">
<select id="dropdown" name="filter">
<option value=""></option>
<option value="1">ID</option>
<option value="2">Alder</option>
<option value="3">Postnummer</option>
<option value="5">Postort</option>
<option value="6">Vårdgivare</option>
<option value="7">Planerat Datum</option>
<option value="8">Status</option>
<option value="9">AAA_diameter</option>
</select>
</form>


<!--search bar for search term input -->
<form name ="search_form" method="POST" action="VGR_data_display.php">

    <input id="search_box" type="text" name="search_box" value="" />
    <input id="submit" type ="submit" name ="search" value ="Ok">

</form>

<table style="margin:auto;" id="table" border='1'>
<tr>
<th>ID</th>
<th>F_ar</th>
<th>Postnr</th>
<th>Postort</th>
<th>Vardgivare</th>
<th>Team</th>
<th>Orsak</th>
<th>Planerat_datum</th>
<th>fran</th>
<th>AAA_diam</th>
</tr>


<?php while($row = mysql_fetch_array($query)) { ?>
  <tr>
  <td><?php echo $row['id']; ?></td>
  <td><?php echo $row['F_ar']; ?></td>
  <td><?php echo $row['Postnr']; ?></td>
  <td><?php echo $row['Postort']; ?></td>
  <td><?php echo $row['Vardgivare']; ?></td>
  <td><?php echo $row['Team']; ?></td>
  <td><?php echo $row['Orsak']; ?></td>
  <td><?php echo $row['Planerat_datum']; ?></td>
  <td><?php echo $row['fran']; ?></td>
  <td><?php echo $row['AAA_diam']; ?></td>
  </tr>

<?php } ?>

Your not checking what filter is selected. What should the filter do? You've not explained it very well in your question. — Styphon, May 29 '14 at 10:03
What do you mean? As per my understanding, You mean to say when you apply where clause result not coming. Am i right? — Jitendra Yadav, May 29 '14 at 10:08
This is not a code it for you site. Its more a can anybody spot the error in this bit of code site. This question is far to broad. — RiggsFolly, May 29 '14 at 10:10
@Styphon: No, I'm not because i don't know how to make it "listen" to what filter is selected. — CodeDreamweaver, May 29 '14 at 10:11
@user3159219 Right, well I don't understand what you need it to do when you say "listen" to what filter is selected. You need to edit your question and explain it better. — Styphon, May 29 '14 at 10:14
@Styphon I just edited the description of my problem, hope it's more understandable. Thanks for taking your time! — CodeDreamweaver, May 29 '14 at 10:28
If I understand you correctly you want the search to know which filter is selected and only search that column? — Styphon, May 29 '14 at 10:32
@Styphon, Exactly, I want to search for all rows where the team equals USA. SELECT * FROM mytable WHERE Team = 'USA'; Team = Filter USA = search term, input in search bar. I took this example to simplify it, in fact I have other data in my db. But I want to learn the codes for how to do this. — CodeDreamweaver, May 29 '14 at 10:39
Please be aware that the mysql extension (supplying the mysql_ functions) has been deprecated since 2012, in favor of the mysqli and PDO extensions. It's use is highly discouraged. See http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Oldskool, May 29 '14 at 12:17

Styphon · Accepted Answer · 2014-05-29T11:40:35.727

0

If I've understood correctly then you want to only search the column that the drop down has selected. Then you want to use this:

if (isset($_POST['search'])) {

    $search_term = mysql_real_escape_string($_POST['search_box']);

    switch($_POST['filter']) {

        Default: 
            $sql .= " WHERE F_ar = '$search_term' ";
            break;
        case 1:
            $sql .= " WHERE Postnr = '$search_term' ";
            break;
        case 2:
            $sql .= " WHERE Postort = '$search_term' ";
            break;
        case 3:
            $sql .= " WHERE Vardgivare = '$search_term' ";
            break;
        case 4:
            $sql .= " WHERE Team = '$search_term' ";
            break;
        case 5:
            $sql .= " WHERE Orsak = '$search_term' ";
            break;
        case 6:
            $sql .= " WHERE Planerat_datum = '$search_term' ";
            break;
        case 7:
            $sql .= " WHERE fran = '$search_term' ";
            break;
        case 8:
            $sql .= " WHERE AAA_diam = '$search_term'; ";
            break;

    }
}

As for your error, you've got two forms, you need everything to be in one form like this:

<form name="Select_filter" method="POST" action="VGR_data_display.php">
    <select id="dropdown" name="filter">
        <option value=""></option>
        <option value="1">ID</option>
        <option value="2">Alder</option>
        <option value="3">Postnummer</option>
        <option value="5">Postort</option>
        <option value="6">Vårdgivare</option>
        <option value="7">Planerat Datum</option>
        <option value="8">Status</option>
        <option value="9">AAA_diameter</option>
    </select>


    <!--search bar for search term input -->

    <input id="search_box" type="text" name="search_box" value="" />
    <input id="submit" type ="submit" name ="search" value ="Ok">

</form>

edited May 29 '14 at 11:40

answered May 29 '14 at 10:38

Styphon

10,304
9
52
86

Sorry for edit, hit save instead of cancel and can't roll back the edit. – webnoob May 29 '14 at 11:02
I'm testing both solution, for webnoobs there is an error syntax, unexpected String. For Styphon's, I got this: Notice: Undefined index: filter in /Applications/XAMPP/xamppfiles/htdocs/PhpProject3/VGR_data_display.php on line 33 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE F_ar = 'Boras'' at line 1. I set the Filter to "Postort" why does it say "F_ar" ? – CodeDreamweaver May 29 '14 at 11:36
@user3159219 You've got your select drop down in a different form. The drop down and the search_box need to be in the same form. It was webnoob that downvoted I think. – Styphon May 29 '14 at 11:39
@Styphon

– CodeDreamweaver May 29 '14 at 11:46
@user3159219 No, you need it to be one form, like in my answer. – Styphon May 29 '14 at 11:47
How could this form look like?

I got that one for the moment, can drop down and search_box be put in that one? Because I got syntax error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE F_ar = 'Boras'' at line 1 – CodeDreamweaver May 29 '14 at 11:52
Sorry I missed your answer above. – CodeDreamweaver May 29 '14 at 11:54
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Vardgivare = '54198'' at line 1 @Styphon, I selected filter Postnr and input 54198, could it be something with the order of cases? – CodeDreamweaver May 29 '14 at 12:04
What do you get if you `echo $sql` after the switch? – Styphon May 29 '14 at 12:10
@Styphon, I fixed it, it was mere sql syntax. Styphon, thank you very much for taking your time solving my problem, with this I'm a bit wiser in PHP! +rep from me. – CodeDreamweaver May 29 '14 at 12:22
Yepp I marked it as green, did I mark this question as answered by green marking you reply? Hmm, I'll manage somehow. – CodeDreamweaver May 29 '14 at 12:39
Yes, by ticking my answer you mark the question as answered. – Styphon May 29 '14 at 12:40
@user3159219 Why did you untick my answer as correct after all the help I gave you? – Styphon May 29 '14 at 14:25
@Styphon, that's wierd. :S I never did that lol. I ticked it again. – CodeDreamweaver May 29 '14 at 16:05
You do deserve the points for helping but the solution is still messy. – webnoob May 29 '14 at 16:17

webnoob · Answer 2 · 2014-05-29T12:00:00.190

0

Change your drop down list so that the select values are the database column names. i.e

<select id="dropdown" name="filter">
  <option value=""></option>
  <option value="ID">ID</option>
</select>

Then in PHP:

if (isset($_POST['search'])) {

    $search_term = mysql_real_escape_string($_POST['search_box']);
    $column = mysql_real_escape_string($_POST['filter']);
}

SQL

SELECT * FROM mytable where $column = '$search_term'

This means adding new options is easy and saves a long winded if / switch statement. It also allows you to build in multiple options down the line.

Note: If you are worried about showing your column names in the form, you could encrypt them when building the drop down list then decrypt when you need to use them

EDIT - Based on comments.

Before your SQL:

$allowed[] = array("ID") //Add all allowed columns.
if (in_array($column, $allowed))
{
  //Run SQL
}

You can build this array when you build the DDL if you want it automated.

edited May 29 '14 at 12:00

answered May 29 '14 at 10:55

webnoob

15,747
13
83
165

on this line: if (isset($_POST['search'])) { I get: Syntax error, unexpected: String after: [ Expected: identifier, variable, NUM_STRING, define. – CodeDreamweaver May 29 '14 at 11:17
This opens you up to SQL injection attacks. It's much better to use a whitelist like in my answer below. – Styphon May 29 '14 at 11:27
@Styphon - If the data is sanitised you are no more open to injection attacks than any other SQL accepting input from a user. – webnoob May 29 '14 at 11:29
You can break the script though. Using a whitelist stops the script from breaking if they try to inject. If someone edited the value of the option to a column that didn't exist, your solution would throw an error. My solution wouldn't. – Styphon May 29 '14 at 11:32
If someone is editing the form before posting, they can expect to get an error. – webnoob May 29 '14 at 11:35
That's bad practice though. You shouldn't be giving them an error, if someone is trying to hack a site and you give them an error you're helping them. Hence why a whitelist is better, it doesn't give the user an error but defaults to something that works. – Styphon May 29 '14 at 11:36
It's a fair point, see my edit. This will solve the issue and still keep things tidy. – webnoob May 29 '14 at 12:00
@webnoob, thank you very much for this, I will save this code and further experiment with it!! – CodeDreamweaver May 29 '14 at 12:23
@user3159219 - No worries, if it answers the question feel free to mark it as the answer. – webnoob May 29 '14 at 12:30

Fetch data from mysql database using drop down list and then use search function to filter data from selected drop down list option

2 Answers2