CODEWITHSUNDEEP
phpmysqlarraysforeach
user638009
user638009

Reputation: 223

PHP foreach, arrays data used in query

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!

Upvotes: 1

Views: 4639

Answers (4)

Matthieu Napoli
Matthieu Napoli

Reputation: 49553

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'";
}

Upvotes: 1

JohnP
JohnP

Reputation: 50019

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 *

Upvotes: 4

Jon
Jon

Reputation: 437376

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).

Upvotes: 2

mahadeb
mahadeb

Reputation: 676

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]."";
  }

Upvotes: 3

Related Questions