CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
wadie
wadie

Reputation: 508

Check two tables in one query

I have the following function:

function login_check($email, $password)
{
    $email       = mysql_real_escape_string($email);
    $password    = md5($password);
    $login_query = mysql_query("SELECT COUNT(`id`) as `count`, `id` FROM `table_name` WHERE `email`='$email' AND `password`='$password'");  

    return (mysql_result($login_query, 0) == 1) ? mysql_result($login_query, 0, 'id') : mysql_error();
}

I want it to check if the user login is correct in two different tables not only one since I've made another table for users who have authenticated their twitter account with my site.

Upvotes: 0

Views: 1207

Answers (2)

pät
pät

Reputation: 543

You could create an union view of both tables:

CREATE VIEW combined_accounts AS
  (SELECT id, twitter_mail AS mail, password FROM twitter_accounts)
  UNION
  (SELECT id, mail, password FROM my_accounts);

Upvotes: 0

mu is too short
mu is too short

Reputation: 434665

You'd be better off with a single table that has an "authenticated with Twitter" flag but you can check both with something like this:

select exists(
    select 1 from table_name where email = '$email' and password = '$password'
    union
    select 1 from twitter_table where email = '$email' and password = '$password'
)

MySQL will give you a one (AKA true) if at least one of the tables has what you're looking for and a zero (AKA false) if neither has a match.

Using the select exists(select 1...) trick will also be faster than counting as the database only needs to find one match or check the indexes to know that there are no matches before it returns from the query.

Upvotes: 3

Related Questions