CODEWITHSUNDEEP
phpmysql
Neil Kennedy
Neil Kennedy

Reputation: 41

Generalised select query with multiple values

I'm trying to write a select query in MySQL which display all column names with checkboxes in the frontend form and allow the user to choose which of to use in the select query. I am OK with using $_POST to post across the values but I'm not sure how I can put these into my query string correctly as some users may select 1, while others may select 3.

This is what I have so far:

Front end form:

<form action="back.php" method="post">
<input type="checkbox" name="person" value="forename">Forename<br>
<input type="checkbox" name="person" value="surname">Surname<br>
<input type="checkbox" name="person" value="nationality">Nationality<br>
</form>

Back end:

$person=$_POST["person"];

$query = "select" . $person . " from Actor WHERE nationality = British";

Many thanks for your help

Upvotes: 1

Views: 66

Answers (1)

jeroen
jeroen

Reputation: 91734

You should use an array so that $_POST["person"] will be an array:

<form action="back.php" method="post">
<input type="checkbox" name="person[]" value="forename">Forename<br>
<input type="checkbox" name="person[]" value="surname">Surname<br>
<input type="checkbox" name="person[]" value="nationality">Nationality<br>
</form>

Then on the php-side you need to validate your input. You cannot automatically escape or prepare column names so you would need to check each element against a white-list.

Something like:

// your valid column names
$valid_column_names = array('forename', ...);

$validated_array = array();
foreach ($_POST["person"] as $value)
{
   if (in_array($value, $valid_column_names))
   {
      $validated_array[] = $value;
   }
}

Then you can simply implode the validated, non-empty array and use it in your query:

$query = "select `" . implode('`,`', $validated_array) . "` from Actor WHERE nationality = British";

Note that I added a space after select (you need that) and backticks to quote the field names (not necessary in your example but necessary in case of reserved words, etc.).

Upvotes: 1

Related Questions