CODEWITHSUNDEEP
phpsqlpdo
Ricki
Ricki

Reputation: 943

Is this PDO code valid?

i have extracted this snippet from my code as im concerned that there is a better way to do this. I'm hoping someone can help me or point me in the right direction.

basically this code checks numerous tables in a database and checks if the result returns or not.

    $stmt = $conn->prepare('SELECT email FROM 1table WHERE email = :email'); 
    $stmt = $conn->prepare('SELECT email FROM 2table WHERE email = :email');
    $stmt = $conn->prepare('SELECT email FROM 3table WHERE email = :email'); 
    $stmt->bindParam(':email', $email);
$stmt->execute();
    if($stmt->fetch(PDO::FETCH_NUM) > 0){

Is there a better way? or a more productive way, which could tell me what table the result was found in?

Upvotes: 0

Views: 42

Answers (3)

Saic Siquot
Saic Siquot

Reputation: 6513

None of previous anwsers shows to you how to know which talbe is the data from. So, if this is relevant, this is the correct UNION

$sql  = "SELECT email, '1table' as fromTable  FROM 1table WHERE email = :email"; 
$sql .= " UNION ALL"; 
$sql .= " SELECT email, '2table' FROM 2table WHERE email = :email"; 
$sql .= " UNION ALL"; 
$sql .= " SELECT email, '3table' FROM 3table WHERE email = :email";

Upvotes: 1

Niels
Niels

Reputation: 49919

This would only use the last query. You can do this:

$stmt = $conn->prepare('
    (SELECT email FROM 1table WHERE email = :email)
    union all
    (SELECT email FROM 2table WHERE email = :email)
    union all
    (SELECT email FROM 3table WHERE email = :email)');

Upvotes: 1

Shoe
Shoe

Reputation: 76280

It's valid PHP but your logic is not valid:

$stmt = $conn->prepare('SELECT email FROM 1table WHERE email = :email'); 
$stmt = $conn->prepare('SELECT email FROM 2table WHERE email = :email');
$stmt = $conn->prepare('SELECT email FROM 3table WHERE email = :email');

You keep overwriting $stmt with a new value. Which means that $stmt will only contains the last prepare statement.

You can take a look at UNION and modify your code as following:

$sql = '(SELECT email FROM 1table WHERE email = :email)'; 
$sql .= 'UNION ALL'; 
$sql .= '(SELECT email FROM 2table WHERE email = :email)'; 
$sql .= 'UNION ALL'; 
$sql .= '(SELECT email FROM 3table WHERE email = :email)'; 
$stmt = $conn->prepare($sql);

Upvotes: 1

Related Questions