0

I looked at the similar questions and answers but not seemed to help.

I have a query:

$stmt = $conn->prepare( 
'SELECT kpp_id FROM kpp_profile WHERE email = :email AND password = :password
UNION ALL
SELECT pm_id FROM pm_profile WHERE email = :email AND password = :password 
UNION ALL
SELECT trainer_id FROM trainer_profile WHERE email = :email AND password = :password');
        $stmt->execute( array( ':email' => $email, ':password' => $password ) );
        $result = $stmt->fetchAll();

For some reason this only returns results for the first query. Can anyone help me understand why?

Twiggit
  • 93
  • 10
  • This [Q&A](https://stackoverflow.com/questions/18511645/use-bound-parameter-multiple-times) about using bound parameter multiple times may help. – Zhorov Jul 16 '20 at 10:28

2 Answers2

0

Are you sure you have data in pm_profile and trainer_profile tables for same supplied email and password? What happens if you run same sql directly using hard coded same email/password?

Maftahur Rahman
  • 100
  • 5
  • 1
    Thank you for your enthusiasm to help. Please post clarification requests as comments to the OP. – Markus AO Jul 15 '20 at 19:25
  • Hi there, Yes I did check the other queries by changing the order of the selects statement and they showed results which is why I am confused – Twiggit Jul 15 '20 at 20:23
  • Your SQL is very fine and must return duplicate results (if exists). I tried your SQL in four environments: MySQL direct command in cmd prompt, Php-MySQL using PDO, Navicat and phpMyAdmin. It works fine and returned expected results in all environment except phpMyAdmin where it removes the duplicate results. Also you should specify the fetch style like $stmt->fetchAll(PDO::FETCH_ASSOC) or $stmt->fetchAll(PDO::FETCH_OBJ). I hope it will help you. – Maftahur Rahman Jul 16 '20 at 05:23
0

Thank you everyone. I realized my mistake. I was misreading the result set. I did not realize that Union keeps the column name of the first table only. I was expecting the array to keep the matching tables key value pair.

Twiggit
  • 93
  • 10