PDO Fetch Whilst in Loop

PHP
1

Hi all, I’m experimenting with PDO as I definately think it’s a positive move forward. I’ve got the loop to work but then I want it to check if an email address is in the database, if so then do something. However, it doesn’t seem to be working…the actual loop is though…

<?php

$dbh = new PDO('mysql:host=localhost;dbname=example', 'example', 'example');

	$sql = "SELECT * FROM registered_users";
	
	//Check email isn't in email_db
	$stmt = $dbh->prepare("SELECT * FROM email_db WHERE email_address LIKE ?");
		
		foreach($dbh->query($sql) as $row):
		
			$stmt->execute(array("%".$row['email_address']."%"));
			
			//Enter email address as it's not on the email database
			if($stmt->fetchColumn() == 0):
			
				echo 'No results found!<br>';
				
			else:
			
				echo 'RESULT FOUND<br>';
			
			endif;
		
		endforeach;

?>
2

I discourage the use of LIKE when it can be avoided. The performance hit is quite extreme. It’s one thing if you only have a partial email address but you make no mention of that.
Please consider the following:


$email = 'sample@mail.com';
$sql = "SELECT * FROM email_db WHERE email_address=:email";

try
{
$tmp_stmt = $pdo->prepare( $sql );
$tmp_stmt->execute( array( ':email' => $email ) );
$tmp_result = $tmp_stmt->fetchAll( PDO::FETCH_ASSOC );
}
catch( PDOException $e )
{
// handle error
}


if( empty($tmp_result) ) 
{
// There were no results.
}
else
{
// You have results
print_r( $tmp_result );
}

unset( $tmp_result, $tmp_stmt, $pdo, $email, $sql );
3

(I always get the feeling of Rudy looking over my shoulder when i venture into these types of threads!)

Get more fancy with your query, to prevent the need for all the queries inside loops!

Note: I am assuming email_db contains a field email_address

SELECT ru.email_address 
FROM registered_users AS ru 
LEFT JOIN email_db AS edb 
  ON ru.email_address = edb.email_address 
WHERE edb.someotherfield IS NULL;

^ generates a list of everyone who’s emails are not in the email_db table.

4 

//Check email isn't in email_db

Just noticed that comment.

Are you checking to see if an email address is unique prior to inserting it into the database?

5

Oh Lord, I would hope that this constraint is enforced at the database level. if not, then schema needs to be altered.

6

Hey chaps, thanks for the messages. I have a users table and an email database. Currently when a user joins they are not automatically put into the email database so I was trying to find which users aren’t already on the email database and add them! Thanks StarLion I think that’s the obvious way forward…damn why didn’t I think of that!

7

Because when we try to solve a problem, we dont look for ways never to encounter the problem.

If you wander over to the MySQL forum, r937 or the other gurus there can almost certainly point you in the right direction of an even better query that may eliminate the need for PHP to do anything with lists.