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 6 of 6
  1. #1
    New Coder
    Join Date
    Oct 2012
    Posts
    29
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Joining 2 tables

    Hi all,

    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.
    Last edited by hujan; 03-19-2013 at 11:05 AM.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,958
    Thanks
    120
    Thanked 76 Times in 76 Posts
    field name is name of what, user, course, ... ?
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    You don't JOIN them. You UNION them.
    Code:
    $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' ";
    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.

  • Users who have thanked Old Pedant for this post:

    hujan (03-20-2013)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Quote Originally Posted by ikuvae22 View Post
    You donít join user and student table. You can use two tables separately. So, open your databases create two new tables and use it separately.
    What a nonsense answer, especially after I gave him a *correct* way to do what he seems to be after.

    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.
    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.

  • Users who have thanked Old Pedant for this post:

    hujan (03-20-2013)

  • #5
    New Coder
    Join Date
    Oct 2012
    Posts
    29
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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!)
    Code:
    SELECT studentid, studentName FROM students WHERE ...
    UNION
    SELECT userid, username FROM users WHERE ...
    Then in your PHP code (or any other code, for that matter), you would *NOT* be able to do something like $row["userid"] or $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.
    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
    •