Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Oct 2012
    Posts
    29
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Unhappy 2 tables for 1 login form

    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!
    Last edited by hujan; 03-31-2013 at 02:01 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    I thought we answered this already???
    Code:
    $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:
    Code:
    $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
    }
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Oct 2012
    Posts
    29
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I thought we answered this already???
    Code:
    $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:
    Code:
    $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:
    Code:
    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

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    No. You did *NOT* read my answer carefully, *AT ALL*.

    Oh, W.T.H.
    Code:
    $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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •