...

View Full Version : 2 tables for 1 login form



hujan
03-31-2013, 06:38 AM
Hi, is it possible one log in form being use by 2 tables in the same DB.

Where each table have different number of columns. Below is my table fields:

user [id(PK), profile, name, gender, dob, contact, addr1, email,username, password]

student [reg_no(PK), s_name, s_gender, s_ic, s_dob, s_contact, s_email, s_addr1, s_addr2, country, s_dept, s_post, username, password, profile].

Please help me, being stuck with this for days already. Thanks in advance!

Old Pedant
03-31-2013, 11:06 PM
I thought we answered this already???


$sql = "
SELECT 'user' AS recordtype, name, addr1, NULL AS s_addr2
FROM user WHERE username = '$user' AND password = '$pwd'
UNION
SELECT 'srudent' AS recordtype, s_name, s_addr1, s_addr2
FROM student WHERE username = '$user' AND password = '$pwd'
";

That doesn't show all the fields, but the idea is the same for all: If a field in STUDENT does not have a similar field in USER, then you need to use NULL AS xxxx in the first SELECT as a placeholder. If a field in USER does not have a similar field in STUDENT, you can do the same but you can also just omit the AS xxxx part.

When you look at the query from PHP, *ONLY* the names in the FIRST SELECT will be visible.

So if you do:

$result = mysql_query( $sql );
if ( $row = mysql_fetch_assoc($result ) )
{
$type = $row["recordtype"]; // will tell you if user or student
$name = $row["name"]; // you can not use $row["s_name"]
$addr1 = $row["addr1"]; // again, can not use $row["s_addr1"]
$addr2 = $row["s_addr2"]; // this is okay because of NULL AS s_addr2 in SQL
}

hujan
04-01-2013, 02:59 AM
I thought we answered this already???


$sql = "
SELECT 'user' AS recordtype, name, addr1, NULL AS s_addr2
FROM user WHERE username = '$user' AND password = '$pwd'
UNION
SELECT 'srudent' AS recordtype, s_name, s_addr1, s_addr2
FROM student WHERE username = '$user' AND password = '$pwd'
";

That doesn't show all the fields, but the idea is the same for all: If a field in STUDENT does not have a similar field in USER, then you need to use NULL AS xxxx in the first SELECT as a placeholder. If a field in USER does not have a similar field in STUDENT, you can do the same but you can also just omit the AS xxxx part.

When you look at the query from PHP, *ONLY* the names in the FIRST SELECT will be visible.

So if you do:

$result = mysql_query( $sql );
if ( $row = mysql_fetch_assoc($result ) )
{
$type = $row["recordtype"]; // will tell you if user or student
$name = $row["name"]; // you can not use $row["s_name"]
$addr1 = $row["addr1"]; // again, can not use $row["s_addr1"]
$addr2 = $row["s_addr2"]; // this is okay because of NULL AS s_addr2 in SQL
}

Is this the correct way to do the sql:


SELECT id,profile, name, gender, dob, contact, addr1, email,
NULL as s_reg, NULL as s_name, NULL as s_gender, NULL as s_ic, NULL as s_dob,
NULL as s_contact, NULL as s_email, NULL as s_addr1, NULL as addr2,
NULL as country, NULL as s_dept, NULL as s_post
WHERE username = '$myusername' and password = '$mypassword'
UNION
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
s_reg, s_name, s_gender, s_ic, s_dob, s_contact, s_email, s_addr1, s_addr2, country, s_dept, s_post
WHERE username = '$myusername' and password = '$mypassword'


I have copied this sql to my check_login.php but it gives me to wrong.php page instead.

Please correct me if i'm wrong.

Thank you,
Hujan

Old Pedant
04-01-2013, 03:24 AM
No. You did *NOT* read my answer carefully, *AT ALL*.

Oh, W.T.H.


$sql = "
SELECT id,profile, name, gender, dob, contact, addr1, NULL AS s_addr2, email,
NULL as s_ic, NULL as country, NULL as s_dept, NULL as s_post
FROM user
WHERE username = '$myusername' and password = '$mypassword'
UNION
SELECT s_regno, s_name, s_gender, s_dob, s_contact, s_email, s_addr1, s_addr2,
s_ic, country, s_dept, s_post
FROM student
WHERE username = '$myusername' and password = '$mypassword'
";


Look. If this is too hard for you, then just forget about using the UNION. Use two separate queries. If you don't find the person in USER table, then and only then look in the STUDENT table.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum