Looping through an array and running a query each time?

[Oatley]

Hello, I'm looking for some advice if possible please. I have an array to loop through

PHP Code:

$people = array (
'John',
'Mark',
'Joe'
);
//and so on 


Now for each element in the array I use a foreach through and run a query on my database table so

Select id from table where name = '$person'

Now With each query there will be a number of 'id' values output. What I want to do is hold these values in an array

Doing this seems to also hold the values from each previous query in the foreach as well

PHP Code:

foreach($people as $person) {
$query = "Select id from table WHERE name = '$person'";
$result = mysql_query($query);
  while($row = mysql_fetch_array($result)) {
    $ids[] = $row;
    print_r($ids);
} 


Can anyone help me here please? Thank you

[bcarl314]

You might want to change it to this:

PHP Code:

foreach($people as $person) {
$query = "Select id from table WHERE name = '$person'";
$result = mysql_query($query);
  while($row = mysql_fetch_array($result)) {
    $ids[$person][] = $row;
    print_r($ids);
} 


This should create an array like this...

$person = array(
    'John' => array( //all john entries here), 
    'Mark' => array( //all mark entries here),
    'Joe' => array ( //all joe entries here)
)

Of course, it depends on how you want to actually use that data.

You could also simply run the query like this:

PHP Code:

$query = "Select id from table WHERE name IN ('".implode("','",$people)."'"; 


Which would get you all the data in 1 query.

[Fou-Lu]

Use the IN clause. Try to avoid looping queries as much as you can. Alternatively you can use prepared statements and loop the bound parameter (which only sends the parameter's back to the sql so communication wise it should be the same). Prepared statements IMO are much better approach, and since you'll need to convert to MySQLi or PDO soon anyways (Mysql library is now officially deprecated and will disappear in the future), I'd suggest doing it with prepared statements.

[Oatley]

Thanks all I 'm going to look into using one query as advised, but for now I'm still after a little help on this though if that's OK please.

I'm still getting a litte confused on arrays. I thought i'd solved what I was trying to do based on the kind advice offered here, but it's not working.

If I have a simple array like so

PHP Code:

$people = array(
'John',
'Mark'
); 


Then for each element in the array I run through as kindly advised

PHP Code:

foreach($people as $person) {
 $sql = "Select id from table WHERE name = '$person'";
 $result = mysql_query($sql);
   while($row = mysql_fetch_array($result)) {
     $ids[$person][] = $row;
     print_r($ids);
   }
} 


It shows some results. But say John has 9 id's associated with his name, I get the first 'id' in one array, then that same id repeated in the next array with the next value as well and so on as per the example below

Array
(
  [John] => Array
       (
           [0] => Array
               (
                   [0] => 5066
                   [id] => 5066
               )

       )

)
Array
(
  [John] => Array
       (
           [0] => Array
               (
                  [0] => 5066
                  [id] => 5066
               )

           [1] => Array
               (
                   [0] => 5837
                   [id] => 5837
               )

       )


       )

When I want them all to be shown in the one 'John' array. Then show me the next set of results in one 'Mark' array?

Can anyone please offer any help? Thank you

[rgb]

mysql_fetch_array() needs a second argument e.g MYSQL_NUM to return just one result row instead of both the associative and the numerical ones. You are also creating another array inside your while loop. Why not just something like:

PHP Code:

foreach($people as $person) {
 $sql = "Select id from table WHERE name = '$person'";
 $result = mysql_query($sql);
   while($row = mysql_fetch_array($result,MYSQL_NUM)) {
    echo $person.$row[0];
   }
} 


[Oatley]

Thank you but I am looking to produce an array for each person holding the possible multiple id values for each iteration in my foreach here for 'John' and 'Mark' rather than echo the id and persons name each time. But thank you for the help.

[rgb]

Same principle. Wouldn't this work?

PHP Code:

foreach($people as $person) { 
 $sql = "Select id from table WHERE name = '$person'"; 
 $result = mysql_query($sql); 
   while($row = mysql_fetch_array($result,MYSQL_NUM)) { 
    $ids[$person][] = $row[0];
    //$person[]=$row[0]; this is wrong = it would overwrite each iteration!
   
   } 
} 


[tangoforce]

Originally Posted by Oatley

Thank you but I am looking to produce an array for each person holding the possible multiple id values for each iteration in my foreach here for 'John' and 'Mark' rather than echo the id and persons name each time. But thank you for the help.

Well in that case you really do need to take notice of the replies given to you about not using multiple SQL calls inside a loop then.

If you're not prepared to act on the easier advice people have given you, they probably won't help you with the harder more complex version. Why do I say it's harder? - because the results you're trying to get are harder to obtain the way you're doing it.