foreach loop issue

[alphamale]

First of all thanks for any help, please forgive the username I've had a drink

I have nill experience with foreach. Is this right?

PHP Code:

$sql = mysql_query("SELECT id from servers WHERE active = '1'");
       $list = array();
                while($ids = mysql_fetch_array($sql)){
                    $list[] = $ids['id'];
                    foreach($list as $id){
                    $get = mysql_query("SELECT * FROM SERVERS WHERE id = ".$id."");
                    $serv = mysql_fetch_array($get);
                    // rest of process here using $serv['cell_ref'] as a reference.
} 


This should cycle through all rows from `servers`. Is that right? (It's not working... so I'm hoping that's written wrong :P )

[tangoforce]

The query should work, put the sql into the sql tab in phpmyadmin, change the $id for a real value and see what happens. What won't work is your use of foreach().

foreach($list as $id), $id will be the index of the $list array (the position in the array - eg 1,2,3,4,5,6 etc)
foreach($list as $key => $value), $value will be what you want while $key will be the index

You shouldn't need to run the query in a loop though, that could end up running hundreds or even thousands of queries on the sql server. On a shared host that could kill it!

Instead, something like this would be better - adjust the SQL query in a loop and then run it just once:

PHP Code:

$Where = implode(' or id = ', $list);

//SELECT * FROM SERVERS WHERE id = 1 or id = 2 or id = 3 etc
$Query = "SELECT * FROM SERVERS WHERE id = $Where";

//Run query here, no looping queries, no sql server hogging etc. 


That assumes that the values of $list are all numerical. If they are string based you'll need to use singles quotes in the sql.

If your id is not numerical (eg say it has letters in it) then this would do it instead:

PHP Code:

foreach($list as $key => $value)
   {
   $list[$key] = "'$value'"; //Note use of single quotes
   }


$Where = implode(' or id = ', $list);

//SELECT * FROM SERVERS WHERE id = 'a1' or id = 'a2' or id = 'a3' etc
$Query = "SELECT * FROM SERVERS WHERE id = $Where"; 


[Cloud Ghost]

You don't need a foreach() loop in this case because while() acts as a loop with the MySQL rows you selected. You're missing a closing curly bracket, by the way but that could just be a problem with copy and pasting.

Try this:

PHP Code:

$sql = mysql_query("SELECT id FROM servers WHERE active = '1'") or die(mysql_error());
while($ids = mysql_fetch_array($sql)) { 
    $get = mysql_query("SELECT * FROM servers WHERE id = '{$ids['id']}'") or die(mysql_error()); 
    $serv = mysql_fetch_array($get); 
    // rest of process here using $serv['cell_ref'] as a reference. 
} 


Just a few notes...
I added or die(mysql_error()) onto the end of your MySQL queries. This is good practice because it will let you know of any errors in your query immediately which can save a lot of time when coding in the future.
I don't know exactly what you're doing here or your table structure, but SQL queries in loops can be considered slow and should be avoided if there's another, more efficient way to do what you want to do.

[alphamale]

Originally Posted by tangoforce

The query should work, put the sql into the sql tab in phpmyadmin, change the $id for a real value and see what happens. What won't work is your use of foreach().

foreach($list as $id), $id will be the index of the $list array (the position in the array - eg 1,2,3,4,5,6 etc)
foreach($list as $key => $value), $value will be what you want while $key will be the index

You shouldn't need to run the query in a loop though, that could end up running hundreds or even thousands of queries on the sql server. On a shared host that could kill it!

Instead, something like this would be better - adjust the SQL query in a loop and then run it just once:

PHP Code:

$Where = implode(' or id = ', $list);

//SELECT * FROM SERVERS WHERE id = 1 or id = 2 or id = 3 etc
$Query = "SELECT * FROM SERVERS WHERE id = $Where";

//Run query here, no looping queries, no sql server hogging etc. 


That assumes that the values of $list are all numerical. If they are string based you'll need to use singles quotes in the sql.

If your id is not numerical (eg say it has letters in it) then this would do it instead:

PHP Code:

foreach($list as $key => $value)
   {
   $list[$key] = "'$value'"; //Note use of single quotes
   }


$Where = implode(' or id = ', $list);

//SELECT * FROM SERVERS WHERE id = 'a1' or id = 'a2' or id = 'a3' etc
$Query = "SELECT * FROM SERVERS WHERE id = $Where"; 


Thank you both! Very insightful