Creating Custom MySQL Search (Drop Down Menus)

Question

I have a database full of items for a video game. Swords, Shields, etc. I tagged all the items with level, effect, location found and stuff like that.

I can hard code pages to pull from the database. Such as a hard coded dagger that stuns query:

$results = $mysqli->query("SELECT name, type, level, effect 
           FROM Items 
           WHERE type = 'Dagger' 
           AND effect = "Stun" 
           ORDER BY name ASC"); 

while ($item= $results->fetch_assoc()) { $result_array[] = $item; }

However I want users to go through drop down menus so they can filter results from the database from themselves. I have no idea how to go about this. I have tried googling but a lot of it seems outdated or when I try it just doesn't work.

Something similair to this website - http://www.wowhead.com/items

So for example users could pick the "Effect" drop down and it creates another drop down where you can choose from; Freeze, Heal, Stun or whatever. Then pick level from drop down menu and enter 50. Then the database would pull the results from the database for daggers that stun and can be used at level 50.

Thanks!

`AND effect = "Stun"` wrong quotes for one thing. Checking for errors will throw you a syntax error. — Funk Forty Niner, Mar 15 '15 at 20:39

score 1 · Answer 1 · answered Mar 15 '15 at 20:48

It's pretty simple. First you have the form element.

<form type="post" action="controller.php">
    <select name="weapon">
        <option value="Dagger"> Dagger </option>
        <option value="Sword"> Sword </option>
    </select>

    <select name="effect">
        <option value="Stun"> Stun </option>
        <option value="Knock Back"> Knock Back </option>
    </select>
</form>

When the user selects a value from these dropdowns, they'll be sent over to the server in the $_POST array with their key's matching the "name" of the select element. The controller.php file is the file that will handle the form submission. You can change the location of this file etc.

Then in your form submission handler you want to handle the $_POST array and then create a prepared statement for security as we're dealing with user input.

/**
| ---------------------------------------------------
| controller.php
| ---------------------------------------------------
*/
if(isset($_POST)){
    $weapon = isset($_POST['weapon']) ? $_POST['weapon'] : false;
    $effect = isset($_POST['effect']) ? $_POST['effect'] : false;

    if($weapon && $effect){
        $mysqli = new mysqli('host', 'user', 'pass', 'database');
        $result = $mysqli->prepare("SELECT name, type, level, effect 
            FROM Items 
            WHERE type = ?
            AND effect = ? 
            ORDER BY name ASC"); 

        $result->bind_param('ss', $weapon, $effect);

        if($result->execute()){
            while($row = $result->fetch_assoc()){
                //access column names here by $row['index'];                     
            }
        }
    }   
}

The above script is checking if the post array is populated, then checks for our specific variables. from there, we establish the database connection, create the safe prepared statement, bind our parameters to our prepared statement, execute the query, then we iterate over the returned resultset with fetch_assoc.

I hope this helps you.

Creating Custom MySQL Search (Drop Down Menus)

1 Answers1