Results 1 to 6 of 6

Thread: [help] WHILE loop or not?

  1. #1
    Join Date
    May 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default [help] WHILE loop or not?

    I think I need to use something else besides a while loop with this problem I have. I'm just not sure what.

    I'm trying to run a while loop so I can determine if one of the (many) email addresses inside my "table_1" match any of the email addresses inside my "table_2". And if it does then I need to delete that row from table_1.

    But it won't work!!

    I must be doing something wrong or maybe I shouldn't even be using a WHILE loop. Can someone help me with this. I would be so happy and grateful!!

    PHP Code:
    while($row2 mysql_fetch_array($get_names2_res)) { 
    while(
    $row mysql_fetch_array($get_names_res)) { 

      
    $friend_email stripslashes($row['friend_email']);
      
    $users_email stripslashes($row2['email']);
      
      if (
    $users_email == $friend_email) {
      
      echo
    "Yeah we found that name ($users_email)";
           
      }
    }



    It works but not how I want it to work. It only reads one email at a time and I need it to read all the emails at one time.

  2. #2
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    You should not be doing this in PHP code -- this is a job for your RDBMS.
    Code:
    mysql_query('DELETE table_1 FROM table_1, table_2 WHERE table_1.friend_email = table_2.email');
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  3. #3
    Join Date
    Sep 2005
    Posts
    882
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Default

    Like Twey said, this should be done in SQL. However, I don't really like the SQL he used. I would write the query like this.
    Code:
    DELETE table_1 WHERE friend_email IN (SELECT email FROM table_2)
    Just a matter of preference really.

    If you really wanted to do this in PHP. You would need to store all the results from query in an array. Then, loop through the second set of results and check the match.
    PHP Code:
    $names2 = Array();
    while(
    $row2 mysql_fetch_array($get_names2_res)){
       
    $names2[] = $rows2['email'];
    }

    while(
    $row mysql_fetch_array($get_names_res)) { 
    $friend_email stripslashes($row['friend_email']);
    for(
    $i=0;$i<count($names2);$i++){
      
    $users_email stripslashes($names2[$i]);
      
      if (
    $users_email == $friend_email) {
      
      echo
    "Yeah we found that name ($users_email)";      
      }
    }

    However, this should NOT be done with PHP.
    Last edited by blm126; 07-18-2008 at 05:22 PM.

  4. #4
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Just a matter of preference really.
    No, not really. Yours has two queries instead of my one (that's a subquery, it counts as an extra query) and so is considerably less efficient; also, MySQL has issues with subqueries. It could well be that that won't even work (although it's perfectly valid SQL, and will work happily under Postgres).
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  5. #5
    Join Date
    Sep 2005
    Posts
    882
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Default

    Quote Originally Posted by Twey View Post
    No, not really. Yours has two queries instead of my one (that's a subquery, it counts as an extra query) and so is considerably less efficient; also, MySQL has issues with subqueries. It could well be that that won't even work (although it's perfectly valid SQL, and will work happily under Postgres).
    Really? I don't know a lot about SQL tuning. However, I would think that an inner join wouldn't be any more efficient than another query. Plus, if the query was run often, the sub query would end up in the query cache. If the query isn't run often, then there isn't any point in worrying about efficiency. Normally, I just use the "Does it work?" principle.

  6. #6
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    A join is considerably more efficient than a query, although I don't know enough database theory to explain why. I only know that extra queries are always bad. I'd look it up, but I have to go out now. Maybe later.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

Bookmarks

Posting Permissions

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