CODEWITHSUNDEEP
phpsqlhtml
Mason240
Mason240

Reputation: 3034

Using a dropdown menu in a form for a SELECT statement with SQL

Thank you for reading my question.

I am building a form that will let users search a db for cards used in a collectible card game. I want to include a dropdown menu that will let users narrow their search down to a specific card expansion.

Here is my dropdown menu:

<select name="Expsn" onchange="this.submit()">
     <option>All Expansions</option>
     <option value="Core">Core</option>
     <option value="SoM">Shadows of Mirkwood</option>

And here is the select statement:

$sql = "SELECT * FROM carddb 
WHERE Expsn ='" . $_POST['Expsn'] . "' AND Type='Hero' ";

This works well when selecting either Core or MoR, but if the dropbox is left blank "All Expansions" is passed into the $_POST['Expsn']. (I use echo $sql; to output the SELECT statement). How can I set up the default to return card from all expansions.

I have spent the last few weeks learning PHP and SQL online, so it is very possible that my whole approach here is VERY wrong and not scalable, so any 'big picture' help would be greatly appreciated. Thank you.

Upvotes: 1

Views: 3443

Answers (3)

Alex Jose
Alex Jose

Reputation: 464

just use this 

<option value="*">All Expansions</option>

Upvotes: 0

Long Ears
Long Ears

Reputation: 4896

First change your "All Expansions" option to have an empty value:

<option value="">All Expansions</option>

This is so that you can change the wording later without having to update your PHP code.

if (empty($_POST['Expsn'])) {
    $sql = "SELECT * FROM carddb WHERE Type='Hero'";
} else {
    $sql = "SELECT * FROM carddb WHERE Expsn ='" . mysql_real_escape_string($_POST['Expsn']) . "' AND Type='Hero'";
}

The mysql_real_escape_string bit is really important to prevent SQL injection - I've assumed you're using the mysql extension here but there's equivalent functions for the other extensions.

Upvotes: 3

TuteC
TuteC

Reputation: 4382

If Expsn comes set, then add the condition to SQL, like:

$expsn = (strlen($_POST['Expsn']) > 0) ? "Expsn = '" . $_POST['Expsn'] . "' AND " : '';
$sql = "SELECT * FROM carddb WHERE $expsn Type='Hero'";

Upvotes: 1

Related Questions