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 8 of 8
  1. #1
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts

    Looping through an array and running a query each time?

    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

    Code:
    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

  • #2
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    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...

    Code:
    $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.

  • Users who have thanked bcarl314 for this post:

    Oatley (01-17-2013)

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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.

  • Users who have thanked Fou-Lu for this post:

    Oatley (01-17-2013)

  • #4
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts
    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

    Code:
    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
    Last edited by Oatley; 01-19-2013 at 02:01 PM.

  • #5
    rgb
    rgb is offline
    New Coder
    Join Date
    Jul 2011
    Posts
    17
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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];
       }


  • Users who have thanked rgb for this post:

    Oatley (01-19-2013)

  • #6
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts
    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.

  • #7
    rgb
    rgb is offline
    New Coder
    Join Date
    Jul 2011
    Posts
    17
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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!
       
       


    Last edited by rgb; 01-19-2013 at 06:47 PM. Reason: Got it wrong

  • #8
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,325
    Thanks
    60
    Thanked 525 Times in 512 Posts
    Blog Entries
    4
    Quote Originally Posted by Oatley View Post
    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.
    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!


  •  

    Posting Permissions

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