Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Oct 2012
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Post foreach loop issue

    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 )

  • #2
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,325
    Thanks
    60
    Thanked 525 Times in 512 Posts
    Blog Entries
    4
    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"
    Last edited by tangoforce; 10-06-2012 at 11:18 PM.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • Users who have thanked tangoforce for this post:

    alphamale (10-06-2012)

  • #3
    New Coder
    Join Date
    Jan 2010
    Location
    Canada
    Posts
    34
    Thanks
    11
    Thanked 4 Times in 4 Posts
    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.

  • Users who have thanked Cloud Ghost for this post:

    alphamale (10-06-2012)

  • #4
    New Coder
    Join Date
    Oct 2012
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by tangoforce View Post
    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


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •