Using foreach and while loop, for an array of a mysql query

[juniper747]

I have an array of email addresses. The array variable is called $d_contacts. So what I want to do is go through each email in the array and see if the email exists in a table of a mysql db.

If so, i want to grab some info from the table that corresponds to that email (such as id, firstname, last name). Finally I want to generate a list of these, so I can echo them out in html.

If the email is not in the table, I want to generate a separate list of just the email addresses.

So far I have the following, but can't seem to get it to work. I must be doing something wrong. Can anyone please simplify this for me?

foreach((array)$d_contacts as $d_contact) {
	$sql_check = mysql_query("SELECT * FROM members WHERE email='$d_contact' ");
	while($row = mysql_fetch_array($sql_check)){ 
		$countrows = mysql_num_rows($check_sql);		
		if($countrows > 0){
                    $firstname = $row["firstname"];
		    $lastname = $row["lastname"];
		    $outputList_A .= '
			<div>
				' . $firstname . ' ' . $lastname . '
			</div>	
		    ';
               } else  {
                    $outputList_B .= '
			<div>
				' . d_contact . '
			</div>	
		    ';
               }
	}
}

echo "$outputList_A";
echo "$outputList_B";

[Weedpacket]

Using a loop to make multiple database queries? You're doing something wrong.

Assuming you're going to soldier on with the mysql library (PHP's developers wish you wouldn't):

$in_clause = array();
foreach($d_contacts as $d_contact)
{
  $in_clause[] = "'" . $d_contact . "'";
}
$in_clause = join(',', $in_clause);

$found_emails = "select email, firstname, lastname from members where email in ($in_clause)";

// Actually I'm not going to bother with actually writing the mysql_query bit...

And for the other part

$existent_emails = "Select email from members";

Get all the existent emails into an array, and then use [man]array_diff[/man] to remove them from $d_contacts.

[Derokorian]

In the OP you have the check if there are results within the while loop, which will never execute (and in fact will throw errors) if there is no row(s) returned by the query, you should instead flip these control structures around.

Next, are you really expecting more than one row to be returned by searching for a specific email? If not there is no need to put that in a loop at all.

You code should probably be closer to this psuedo code (of course Weed's way is probably still better than this way):

foreach( $d_contacts as $d_contact ) {
   $sql = "SELECT id,fname,lname FROM table WHERE email = '$d_contact'";
   $res = mysqli_query($sql); //MySQLi_* because you should use that instead of mysql_*
   if( mysqli_num_rows($res) > 0 ) {
      $row = mysqli_fetch_assoc($res);
      $output_a .= "<div>ID: {$row['id']}\tName:{$row['fname']} {$row['lname']}</div>";
   } else {
      $output_b = "<div>$d_contact</div>";
   }
}

[bradgrafelman]

Weedpacket;10996353 wrote:

$in_clause = array();
foreach($d_contacts as $d_contact)
{
  $in_clause[] = "'" . $d_contact . "'";
}
$in_clause = join(',', $in_clause);

Any reason why you didn't just do:

$in_clause = "'" . join("', '", $d_contacts) . "'";

or, for security's sake,

$in_clause = "'" . join("', '", array_map('mysql_real_escape_string', $d_contacts)) . "'";

?

Weedpacket;10996353 wrote:

And for the other part

$existent_emails = "Select email from members";

Get all the existent emails into an array, and then use [man]array_diff[/man] to remove them from $d_contacts.

Yikes... that could be a huge array and take a (relatively) considerable time to build.

Since you already know which e-mails did match based on the previous query, why not just do the [man]array_diff/man directly against that list of e-mails?

Derokorian wrote:

of course Weed's way is probably still better than this way

Aye, only because your code snippet still has the hideous construct of SQL queries being executed in a loop.

[Weedpacket]

bradgrafelman wrote:

Any reason why you didn't just do:

No particular reason (though see below); the difference is between [font=monospace]IN ()[/font] and [font=monospace]IN ('')[/font]. Based on what I've been doing most recently along those lines, the latter would be bad. I guess it would be the better option even if it does waste time looking for an empty string.

Since you already know which e-mails did match based on the previous query, why not just do the array_diff() directly against that list of e-mails?

You could, but you'd have to have more than two hours' sleep in the last forty eight first.

[bradgrafelman]

Weedpacket;10996402 wrote:

the difference is between [font=monospace]IN ()[/font] and [font=monospace]IN ('')[/font].

Aye, I had that thought circling somewhere in my head while I was composing my reply but forgot to articulate my thoughts before posting.

I had assumed that this whole chain of processing would be short-circuited at some higher level in the event that the "array of emails" as actually an array of 0 emails.

Weedpacket;10996402 wrote:

You could, but you'd have to have more than two hours' sleep in the last forty eight first.

Ah, well there it is then - we're polar opposites; I'm mainlining coffee like it's going out of style. :p