Joining 2 tables
I have a problem in joining 2 tables. each tables have different fields and only 3 fields that are same.
1st table (user)
name, position, class, username and password
2nd table (student)
name, course, supervisor, username and password
How to join this two tables? I want to do joining for this tables for log in purpose.
field name is name of what, user, course, ... ?
You don't JOIN them. You UNION them.
$sql = "SELECT name, position, `class`, NULL AS course, NULL AS supervisor "
. " FROM user "
. " WHERE username = '$usr' AND password = '$pwd' "
. " UNION "
. " SELECT name, NULL, NULL, course, supervisor "
. " FROM student "
. " WHERE username = '$usr' AND password = '$pwd' ";
What a nonsense answer, especially after I gave him a *correct* way to do what he seems to be after.
Originally Posted by ikuvae22
He wants a *SINGLE* login form, where the user enters name and password, and then he wants to search both tables for possible matches on name and password and log the person in automatically according to what table he/she is in. My code allows him to do that.
Thanks for the reply.. Now, I added more fields to my Student's table but User's table remain the same.
Student's table: name, reg_id, address, contact_number, edu_level, email,course, supervisor, username and password
User's table: name, position, class, username and password
My question is, how to union these table since the number of columns are not same.
Thanks for the help!
Do as I showed in my first answer:
Any columns that you want to get from table A that are not in table B, you simply use (just making ONE example)
NULL AS edu_level
Note that, in a UNION, *ONLY* the field names given in the *FIRST* SELECT are seen by your PHP code.
So if you did (just another example!)
Then in your PHP code (or any other code, for that matter), you would *NOT* be able to do something like
SELECT studentid, studentName FROM students WHERE ...
SELECT userid, username FROM users WHERE ...
$row["username"]. Again, ONLY the field names from the first SELECT are visible, so it's very important the you use meaningful names in *that* SELECT, even when supplying just NULL values.