How to check two tables

[Cheese]

I have two tables one called students and another called staff and when you login I need a query to be run to check both tables for a valid login and display a message to welcome the user hence why I also need first name.

 

I have tried doing the bellow sql query but I think all this does is check both tables for the same details and only lets you login if a match is found in both and not one or the another.

 

 
$q = "SELECT students.student_id, staff.staff_id, students.first_name, staff.first_name, students.user_level, staff.user_level 
FROM students, staff 
WHERE (students.email='$e' AND students.pass=SHA1('$p')  OR staff.email='$e' AND staff.password=SHA1('$p'))";

 

Any help would be appreciated.

[AyKay47]

$q = "SELECT students.student_id, staff.staff_id, students.first_name, staff.first_name, students.user_level, staff.user_level 
FROM students, staff 
WHERE (students.email='$e' AND students.pass=SHA1('$p'))  OR (staff.email='$e' AND staff.password=SHA1('$p'))";

[PFMaBiSmAd]

Your need to have one table for your `users`. The role each user has (staff, student) should be indicated by a value in a column in that one table.

[kickstart]

Hi

 

While in every way agreeing with PFMaBiSmAd, you could do it with:-

 

SELECT 'Student' AS LoginType, students.student_id, students.first_name, students.user_level
FROM students
WHERE students.email='$e' AND students.pass=SHA1('$p'
UNION
SELECT 'Staff' AS LoginType, staff.staff_id, staff.first_name, staff.user_level 
FROM staff 
WHERE staff.email='$e' AND staff.password=SHA1('$p'))

 

That should give you the row along with whether it is a student or a staff table row.

 

What your current code appears to be doing is a cross join, giving you every combination of rows from the 2 tables (ie, 100 students and 100 staff would generate 10000 rows), and then finding those where the email and password match (so if one student matched you would get 100 rows for that student, one for each member of staff).

 

All the best

 

Keith