Checking is username exists on two tables PHP PDO?

Question

I have a users table and a users_banlist table.

On my registration form i want to check all in one query whether the username someone entered on form exists in the users table and see if it also exists on the users_banlist table.

I can do them on there own in individual queries but would rather do it all in one.

Here is what I got, but even though I enter a username that is taken it does not tell me it's already taken.

$stmt = $dbh->prepare("
SELECT
  users.user_login,
  users_banlist.user_banlist
FROM
  users ,
  users_banlist
WHERE
  users.user_login = ? OR
  users_banlist.user_banlist = ?");

// checker if username exists in users table or users_banlist table
$stmt->execute(array($username, $username));

if ( $stmt->rowCount() > 0 ) {
    $error[] = 'Username already taken';
}

Basically I think it is something to do with the execute or rowCount(), could anyone tell me where I am going wrong? Being new to pdo I'm finding it a little confusing at the moment.

score 1 · Accepted Answer · answered Apr 14 '12 at 11:04

1

You could use the query

SELECT
  users.user_login
FROM
 users 
WHERE
  users.user_login = ? 
UNION ALL
SELECT
  users_banlist.user_banlist
FROM
  users_banlist
WHERE
  users_banlist.user_banlist = ?

and then proceed as normal. What you were using is a CROSS JOIN which returns the cartesian product that produce rows which combine each row from the first table with each row from the second table. If one of those tables returns 0 rows you get 0 results

answered Apr 14 '12 at 11:04

Nicola Peluchetti

76,206
31
145
192

thank you, works great and done a search on UNION and UNION ALL and never knew they existed, they are going to come in handy for other future queries so thanks for your help :) phplover – PHPLOVER Apr 14 '12 at 11:18

score 1 · Answer 2 · answered Apr 14 '12 at 11:24

1

Use SQL JOINS instead

SELECT
  users.user_login,
  users_banlist.user_banlist
FROM
  users INNER JOIN
  users_banlist ON users.ID=users_banlist.user_id
WHERE
  users.user_login = ? OR
  users_banlist.user_banlist = ?

where users.ID and users_banlist.user_id are same

answered Apr 14 '12 at 11:24

Vipin Jain

1,382
1
10
19

any reason in particular why to use joins over union ? thanks phplover – PHPLOVER Apr 14 '12 at 11:48
i didnt check the first line properly the union will be the right answer for this – Vipin Jain Apr 14 '12 at 12:05
ok thanks, still appreciate your time in trying to help, thanks phplover – PHPLOVER Apr 14 '12 at 12:35

Checking is username exists on two tables PHP PDO?

2 Answers2