PHP: building query from array foreach

Question

$test=$_POST['Cities'];
 foreach ($test as $t){
 $query .= " AND u.bostadsort = \'".$t."\'";
 }

I have this for my

<select size="6" name="Cities[]" multiple="multiple">
    <option value="">All</option>
    <option value="1">C1</option>
    <option value="2">C2</option>
    <option value="3">S3</option>
    <option value="4">S4</option>
    <option value="5">S5</option>
</select>

But its not right at all.

What I'm trying to do is when you pick the cities(in the search form), it puts all the values into an array.

Now I would like to have it write like this, if you e.g pick 2, 4, 5

AND u.bostadsort = '2' OR u.bostadsort = '4' OR u.bostadsort = '5'

(maybe you could do this easier to, but this is the only way i know, using "OR")

So if you picked more than 1 then it should be OR, instead of the AND i got.

Maybe I done it in a wrong way, and there is a better method doing this than foreach..

How can I do this?

Daniel Vandersluis · Accepted Answer · 2010-10-04 20:41:47Z

4

You could use IN instead, which lets you supply multiple values:

if (is_array($_POST['Cities']))
{
  // If multiple values were selected, handle them
  $cities = array();
  foreach ($_POST['Cities'] as $city)
  {
    if (!empty($city))
    {
      $cities[] = mysql_real_escape_string($city);
    }
  }
}
elseif (!empty($_POST['Cities']))
{
  // ... or was a single, non-empty value passed in?
  $cities = array(mysql_real_escape_string($_POST['Cities']));
}

if (count($cities))
{
  $query .= " AND u.bostadsort IN ('" . join("', '", $cities) . "')";
}

Note that I am passing each value through mysql_real_escape_string before using it in the query; this is done to prevent SQL injection. If you are not using MySQL, the other RDBMSes have similar functions.

See http://www.ideone.com/UVXuu for an example (ideone doesn't seem to have mysql enabled, so the calls to mysql_real_escape_string are removed).

Alternately, you could use PDO's prepare and execute methods:

$cities = $_POST['Cities'];
if (count($cities))
{
  $query .= " AND u.bostadsort IN (?" . str_repeat(', ?', count($cities) - 1) . ")";
}

// $db is a PDO object
$stmt = $db->prepare($query);
$stmt->execute($cities);

(Of course, this solution ignores how you build the rest of your query because you don't give it in your question, so you'd want to parameterize the other parts as well.)

edited Oct 4, 2010 at 20:41

answered Oct 4, 2010 at 14:35

Daniel Vandersluis

94.7k23 gold badges174 silver badges158 bronze badges

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

9 Comments

Johnson Over a year ago

The first solution, is working great! How can I check if its empty? if(empty(join($cities)) ?

Daniel Vandersluis Over a year ago

You could check empty($cities) (no need for the join) or count($cities) == 0

Johnson Over a year ago

Im getting Warning: array_map() [function.array-map]: Argument #2 should be an array

Johnson Over a year ago

tested count too, same results. Count is 1 when i pick All, that´s why it still runs the $query ... And it doesnt run the $query when I dont pick anything, but then I receive the above err.

Johnson Over a year ago

Ok one issue is fixed, but now the last remains, if you pick All, it runs the query. (All has empty value)

|

Collectives™ on Stack Overflow

PHP: building query from array foreach

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related