This should be straightforward. I have three tables (relevant columns shown).
users : ID,USERNAME,PWD
shops: ID,MANAGER_ID (referencing a USER_ID),NETWORK_ID
networks : ID,ADMIN_ID (referencing a USER_ID)
All I need to do, is upon login, check USERNAME and PWD against the ID and to be able to tell if the user that just logged on, is simply in shops table as a manager, or a network admin, or both.
Can this be done with one single SELECT with PHP ? Thanks !
SELECT
users.id AS user_id,
shops.id AS shop_id,
networks.id AS network_id
FROM users
LEFT OUTER JOIN shops
ON shops.manager_id = users.id
LEFT OUTER JOIN networks
ON networks.manager_id = users.id
WHERE users.username = ?
AND users.pwd = ?
LIMIT 1
When this query returns, shop_id will be non-null if the user is referenced from the shops table, and the same goes for network_id.
Note that if the user is referenced from the shops or networks table more than once, this query will only produce one row; I assume from your question that you don't care which shop/network the user is associated with, you only want to check for such an association.
Alternatively, this query would work too:
SELECT
users.id AS user_id,
EXISTS (SELECT shops.id
FROM shops
WHERE shops.manager_id = users.id)
AS has_shop,
EXISTS (SELECT networks.id
FROM networks
WHERE networks.admin_id = users.id)
AS has_network
FROM users
WHERE users.username = ?
AND users.pwd = ?
One of the queries might be faster than the other depending on what indexes you have set up on the tables, as well as your MySQL version. Consider running them both through EXPLAIN if performance is an issue.
