[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!! :D:D

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.

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');

---

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.

Quote:

---

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).

Quote:

---

Originally Posted by Twey

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.

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.