MySql Query results from multiple select form

Question

I have a form on a html page which allows me to select multiple items. The name of the form is name="region[]

It posts to another page where I use this code:

The region(s) selected:
<?php
$values = $_POST['region'];

foreach ($values as $region){
echo $region;
}
?>

This will display the results of the form perfectly; if there is one value it will print the one value, but if there is more than one then it will print them all.

I need to use the results of this in a query:

<?php
$con=mysqli_connect("****","****","****","****");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT
GoogleBusinessData.BusName,
GoogleBusinessData.BusAddress,
PostcodeTbl.PostcodeFirstTown,
PostcodeTbl.PostcodeFirstArea,
PostcodeTbl.Region,
PostcodeTbl.Postcode,
PostcodeTbl.PostcodeFirstLetters,
PostcodeTbl.PostcodeFirstPart,
PostcodeTbl.Country,
GoogleBusinessData.BusPhone,
GoogleBusinessData.BusCats,
GoogleBusinessData.FaroukCat,
GoogleBusinessData.BusWebsite,
GoogleBusinessData.BusDescription,
GoogleBusinessData.BusGoogleBusinessID,
GoogleBusinessData.BusPageURL,
EmailTable.EmailNumberOfEmails,
EmailTable.EmailAddresses
FROM
GoogleBusinessData
INNER JOIN PostcodeTbl ON GoogleBusinessData.BusPostalCode = PostcodeTbl.Postcode
INNER JOIN EmailTable ON GoogleBusinessData.BusWebsite = EmailTable.EmailWebsite
WHERE EmailTable.EmailNumberOfEmails > 0 AND
GoogleBusinessData.FaroukCat = 'Wedding Planner'
GROUP BY
GoogleBusinessData.BusWebsite
ORDER BY
GoogleBusinessData.BusName ASC
LIMIT 0,20");

while($row = mysqli_fetch_array($result))
{
echo $row['BusName'] . "  -  " . $row['PostcodeFirstTown'] . "  -  " . $row['PostcodeFirstArea'] . "  -  " . $row['Region'] . "  -  " . $row['Postcode'];
echo "<br>";
}

mysqli_close($con);
?>

So I need to add the condition in the WHERE to only return the results if it contains one of the regions with the form. I tried the following with no joy:

WHERE PostcodeTbl.Region IN ('$region') AND
EmailTable.EmailNumberOfEmails > 0 AND
GoogleBusinessData.FaroukCat = 'Wedding Planner'

But this only returns the last selection (as if there were only one selected).

Can anyone help?

score 0 · Answer 1 · edited May 23 '17 at 12:28

In your first script, you are looping through the items. Each item is put into the variable $region one by one. So after the loop, $region contains the last item. If you construct the where clause at that time, it explains why the query only returns the last item.

To fix this, you'll have to construct a variable (e.g. $regions) that contains a list of regions.

For instance:

$regions = "'" . implode($_POST['region'], "','") . "'";

...  WHERE PostcodeTbl.Region IN ('$regions') AND ...

Note that this is potentially unsafe, since the input in $_POST is not validated, so it is better to loop through the array (like you did) and validate each item one by one while constructing the string in $regions. You can use mysqli_real_escape_string for that.

Alternatively, and arguably better, is to use the parameter binding capabilities of mysqli. This is a bit tricky with array variables, but the details on how to do that are already described in this question.

I tried to add the two lines of code but it won't return any results to anything. — user2803146, Nov 24 '13 at 00:24

MySql Query results from multiple select form

1 Answers1