sql query from two tables

Question

I have two tables (customers, admin) A user logs in by email and password, I need to check if either the email and password exists in the customer or admin table. The emails are unquie so won't have a problem there. This is for uni work, obviously I would have a better database design than this but I have to use what is given. I have this:

$email = $_POST['email']; 
$password = $_POST['password'];

$sql = "SELECT * FROM customer, admin WHERE cus_email = '$email'
 AND cus_password = '$password' OR admin_email = '$email' AND admin_password = '$password'";

Am not very good with Joins so I havent attempted one on this.

Thanks for any help :)

Jonathan Leffler · Accepted Answer · 2010-11-11 20:38:14Z

3

You really need a UNION rather than a JOIN here:

SELECT username
  FROM Customer
 WHERE cus_email = ? AND cus_password = ?
UNION
SELECT adminname AS username
  FROM admin
 WHERE admin_email = ? AND admin_password = ?

You should either use placeholders - the question marks - or protect yourself against SQL Injection attacks. There are functions to help you do that in PHP.

answered Nov 11, 2010 at 20:38

Jonathan Leffler

760k145 gold badges961 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Elliott Over a year ago

Thanks, I would be using stored procedures but the tutor doesn't teach that so apparently I cannot use them.

Jonathan Leffler Over a year ago

@Elliott: Even inside a stored procedure, if you build the SQL dynamically (as a string) you have to be careful. If you use placeholders in some form, you should be safe.

D'Arcy Rittich · Accepted Answer · 2010-11-11 20:37:53Z

2

SELECT cus_id as id, 'false' as IsAdmin 
FROM customer
WHERE cus_email = '$email' 
    AND cus_password = '$password' 
UNION ALL    
SELECT admin_id as id, 'true' as IsAdmin
FROM admin
WHERE admin_email = '$email' 
    AND admin_password = '$password'";

answered Nov 11, 2010 at 20:37

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Collectives™ on Stack Overflow

sql query from two tables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related