Send dropdown form data to php for database query

Question

I am trying to construct a query based upon a form consisting of several drop down boxes. I have tried googling around for solutions but haven't been able to get any success and am currently getting 'Databse query failed' and am not sure if this is because my query is constructed wrong or if the form inputs aren't being correctly transferred over to the php file. Any help on this would be amazing as at the moment I am really stuck.

    <form action="search.php" method=post>

    <select id="dung-name-select" name="dung-select">
        <option name="" disabled selected hidden>Name</option>
        <option value="*">All</option>
        <option value="Black Rock Hold">Black Rock Hold</option>
        <option value="Court of Stars">Court of Stars</option>
        <option value="Darkheart Thicket">Darkheart Thicket</option>
        <option value="Eye of Azshara">Eye of Azshara</option>
        <option value="Halls of Valor">Halls of Valor</option>
        <option value="Maw of Souls">Maw of Souls</option>
        <option value="Neltharions Lair">Neltharions Lair</option>
        <option value="The Arcway">The Arcway</option>
        <option value="Vault of the Wardens">Vault of the Wardens</option>
    </select>

    <select id="dung-loc-select" name="loc-select">
        <option name="" disabled selected hidden>Location</option>
        <option value="*">All</option>
        <option value="Valsharah">Valsharah</option>
        <option value="Suramar">Suramar</option>
        <option value="Azsuna">Azsuna</option>
        <option value="Stormheim">Stormheim</option>
        <option value="Highmountain">Highmountain</option>
    </select>

    <select id="dung-rating-select" name="rate-select">
        <option name="" disabled selected hidden>Rating</option>
        <option value="*">All</option>
        <option value="1">1</option>
        <option value="2">2</option>
        <option value="3">3</option>
        <option value="4">4</option>
        <option value="5">5</option>
        <option value="6">6</option>
        <option value="7">7</option>
        <option value="8">8</option>
        <option value="9">9</option>
        <option value="10">10</option>
    </select>

    <input type="submit" value="Go">

</form>

    <?php

    $dungeon = $_POST["dung-select"];
    $location = $_POST["loc-select"];
    $rate = $_POST["rate-select"];

    //Database Query
    $query = "SELECT * FROM dungeon where Name = $dungeon and Location = $location and Rating = $rate";
    $result = mysqli_query($connection, $query);
    if (!$result) {
        die("Database query failed.");
    }
?>

NB: the php code is in a separate file to the form.

what do you get in the $result variable when you dump it? it could be on the on your database credentials is causing the error and please observe proper quotation and SQL case. Read about php concatenation. — threeFatCat
– threeFatCat, Commented May 19, 2017 at 23:09

TCooper · Accepted Answer · 2017-05-19 23:20:31Z

1

You need to concatenate your variables with the string portions of your query to end up with a complete query. So:

$query = "SELECT * FROM dungeon where Name = ".$dungeon." and Location = ".$location." and Rating = ".$rate;

I believe you can also use:

$query = 'SELECT * FROM dungeon where Name = {$dungeon} and Location = {$location} and Rating = {$rate}';

Either way, unless I'm mistaken, your simply passing your variable names in your query.

answered May 19, 2017 at 23:20

TCooper

1,9701 gold badge14 silver badges20 bronze badges

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

1 Comment

threeFatCat Over a year ago

@Kai Jones this should be the right way. Sorry just too lazy to write this code. But the best way supposed to be is to learn it by yourself. cheers!

Ehtasham Nasir · Accepted Answer · 2017-05-19 23:15:08Z

Don't use name attribute in tag, if you want first option with without value then use <option value="">Name</option>

Try this:

<select id="dung-name-select" name="dung-select">
        <option value="" disabled selected hidden>Name</option>
        <option value="*">All</option>
        <option value="Black Rock Hold">Black Rock Hold</option>
        <option value="Court of Stars">Court of Stars</option>
        <option value="Darkheart Thicket">Darkheart Thicket</option>
        <option value="Eye of Azshara">Eye of Azshara</option>
        <option value="Halls of Valor">Halls of Valor</option>
        <option value="Maw of Souls">Maw of Souls</option>
        <option value="Neltharions Lair">Neltharions Lair</option>
        <option value="The Arcway">The Arcway</option>
        <option value="Vault of the Wardens">Vault of the Wardens</option>
    </select>

Kai Jones · Accepted Answer · 2017-05-19 23:18:30Z

0

Thank you to those that posted but I have found a solution that seems to be working from my testing what I changed was within the query creation section of the php I added an if elseif statement that will based upon the user inputs change the query construction to filter it.

    if ($dungeon == "*" && $location == "*" && $rate == "*") {
    $query = "SELECT * FROM dungeon";
} elseif ($dungeon == "*" && $location == "*") {
    $query = "SELECT * FROM dungeon WHERE Rating = '$rate'";
}

Using this I can filter when I want a filter to be applied. I also realised that the variables such as $rate need to be surrounded in '' which I was missing before.

answered May 19, 2017 at 23:18

Kai Jones

1554 silver badges15 bronze badges

1 Comment

threeFatCat Over a year ago

Actually you need to add dots like this in your variable .$rate. so it will work properly. :) Please check this sample

threeFatCat · Accepted Answer · 2017-05-19 23:26:47Z

0

in addition to TCooper answer, it would be better if you use single quote, double quote:

$query = 'SELECT * FROM dungeon WHERE Name = '".$dungeon."' AND Location = '".$location."' AND Rating = '".$rate."';

EDIT: And proper SQL casing too!

answered May 19, 2017 at 23:26

threeFatCat

82913 silver badges32 bronze badges

Comments

marc_s · Accepted Answer · 2017-05-20 07:41:28Z

0

You need to use some debug tricks to find the origin of your problem.

First, you may want to check whether or not your data are correctly given to the PHP part. You can add :

print_r($_POST);

in your PHP file to display the content of the $_POST variable.

Same thing for the SQL query, it may be useful to display it. This would allow you to read it, or to copy it to your SQL manager in order to test it.

This will do the trick :

echo $query."<br/>";

Let's hope this will help you :)

edited May 20, 2017 at 7:41

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 19, 2017 at 23:11

technico

1,1721 gold badge12 silver badges22 bronze badges

Collectives™ on Stack Overflow

Send dropdown form data to php for database query

5 Answers 5

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related