Live MySQL filtering via HTML Select

Question

I'm trying to use a select list to actively filter the results I get from my SQL database. For example, if I choose "Yankees" from my select list, I want my SQL query to only show Yankee players.

EDIT: By "live filtering" I mean that when a user selects Yankees, Phillies, or Announcer, the select list will only display names under that category.

Here is my code for the select.

<select name="ident" id="ident">
    <option value="yankees">Yankees</option>
    <option value="phillies">Phillies</option>
    <option value="announcer">Announcer</option>
</select>

And this is the code that follows (after database connection):

<?php
    $ident = $_POST['ident'];
    $query = @mysql_query('SELECT name, id FROM grade ORDER BY name asc WHERE ident="' . $ident . '"');
    echo "<select name='name'>";
    while ($temp = mysql_fetch_assoc($query)) {
        echo "<option>".htmlspecialchars($temp['name'])."</option>";
    }
        echo "</select>";
?>

I know the while statement works for populating my second select list (of names) if I manually put in the name, but am unsure if I can/am doing the live filtering of that list correctly.

Thanks in advance.

Not my question, but okay. Would you care to elaborate on this and tell me what exactly is wrong? — tnw
– tnw, Commented Jun 13, 2011 at 13:56
A malicious user could POST invalid data that'd allow them to do any SQL query, including dropping your tables. You need to use mysql_real_escape_string on $ident prior to using it in the query. — ceejayoz
– ceejayoz, Commented Jun 13, 2011 at 13:58

Shef · Accepted Answer · 2011-06-13 14:03:47Z

1

Nope, you are not doing it correctly. First of all, there is a security hole. Your code is vulnerable to SQL Injection. Second, your SQL query was wrong. ORDER BY should have come after WHERE clause.

Here is the secure and correct way to do it:

<?php
$ident = mysql_real_escape_string($_POST['ident']);
$query = @mysql_query("SELECT name, id FROM grade WHERE ident = '$ident' ORDER BY name ASC");
echo "<select name='name'>";

while ($temp = mysql_fetch_assoc($query)) {
    echo "<option>".htmlspecialchars($temp['name'])."</option>";
}

echo "</select>";
?>

answered Jun 13, 2011 at 14:03

Shef

45.6k15 gold badges82 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukas Knuth · Accepted Answer · 2011-06-13 13:58:08Z

1

I'm not sure what you mean by "Live filtering". Something like Google implemented in their search (which is called "Google Suggest")?

In this case, you would use JavaScript with the jQuery Library to update your list.

Also, you shouldn't use the @ when you're testing something, because it suppresses the error message (if there is one) and you don't know what's wrong. If you don't want your productive server to give the user error-messages, there are options in the php.ini-file which disable this.

Last but not least, I would suggest you use a PreparedStatement to make prevent your code form SQL-Injections (this amuses you're using the MySQLi-class):

Create a PrearedStatement with the prepare()-method.
Bind your parameters
Execute the Statement
Bind the results
Fetch them.

Example code on this can be found in the linked PHPDoc-Samples.

answered Jun 13, 2011 at 13:58

Lukas Knuth

25.8k16 gold badges88 silver badges114 bronze badges

3 Comments

excanoe Over a year ago

In fact jquery isn't so necessary, and you can use error_reporting(0) (php.net/manual/en/function.error-reporting.php).

tnw Over a year ago

By live filtering I mean in one menu, a user could select Yankees, Phillies, or Announcer. In the second menu, the name results would be filtered by the name they just selected rather than having all Yankees, Phillies, and Announcer names listed.

Lukas Knuth Over a year ago

@Tory If this should be done without reloading the Page, you'll need to do it with jQuery or something similar.

Collectives™ on Stack Overflow

Live MySQL filtering via HTML Select

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related