PHP foreach, arrays data used in query

Question

I have:

$array1 =     //contains places ids and locations;
$array2 = array();
$array3 = array();


  foreach($array1 as $itemz)
  {     
      $array2[] = $itemz[place][id];
      $array3[] = $itemz[place][location][city];

      $sql = "select * from places where id=".$array2." and location=".$array3."";
  }

But when I print $sql I get:

  select * from places where id=12 and location=Array

Can anyone please tell me what is wrong with the code?

Thanks!

Answer 1

I'm sorry but your code doesn't make sense at all. I'm surprised that you're getting that result at all. Let's walk through it.

Where are the quotes?

$array2[] = $itemz[place][id];
$array3[] = $itemz[place][location][city];

You're missing quotes here, please add them

$array2[] = $itemz['place']['id'];
$array3[] = $itemz['place']['location']['city'];

Array to String conversion

$sql = "select * from places where id=".$array2." and location=".$array3."";

This statement shouldn't work for 2 reasons.

1. Assuming that id is a single field of INT and you have a bunch of INTs in $array2 you still can't compare them without a MySQL IN.

2. You're converting from a PHP array to a string. That won't work.

Since you're running this in a loop $array2[] and $array3[] will continue to change and will grow.

So what you're actually trying to do is come up with a query like

$sql = "SELECT * 
        FROM places 
        WHERE 
             id IN (" . implode(',', $array2) . ") AND 
             location IN (" . implode(',', $array3) . ")";

But this makes no sense at all because as the loop continues you're retrieving the same data incrementally.

So I think what you actually want to do is

$sql = "SELECT * 
        FROM places 
        WHERE 
             id = {$itemz['place']['id']} AND 
             location = {$itemz['place']['location']['city']}";

This is most probably what you need. This retrieves the rows for each row as you iterate through you array.

A couple of improvements I would do is.

Run your query once after the looping is done so you only have to run the query one time and not n times.

Also, consider retrieving only the columns you need instead of doing SELECT *

Answer 2

You can't use $array3 to build query, cause it is an array. Rather you can code like bellow -

 foreach($array1 as $i=>$itemz)
  {     
      $array2[$i] = $itemz[place][id];
      $array3[$i] = $itemz[place][location][city];

      $sql = "select * from places where id=".$array2[$i]." and location=".$array3[$i]."";
  }

Answer 3

This line:

 $array3[] = $itemz[place][location][city];

results in creating an array named $array3 and adding an element equal to $itemz[place][location][city] with a key of 0 to it. When you try to embed this variable into the query you have a problem because it's not a string.

What you probably need is:

 $id = $itemz['place']['id'];
 $city = $itemz['place']['location']['city'];
 $sql = "select * from places where id=".intval($id)." and location='".
        mysql_real_escape_string($city)."'";

Notice that I have made changes to fix some other serious problems with the code (indexing into arrays with constants instead of strings and leaving your code vulnerable to SQL injection).

Answer 4

Why using array when you only need a standard variable :

$array1 =     //contains places ids and locations;

foreach($array1 as $itemz)
{     
    $id = $itemz['place']['id'];
    $city = $itemz['place']['location']['city'];

    $sql = "select * from places where id='$id' and location='$city'";
}