SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way to select one record from two tables?

    Hey everyone,

    My current login system stands as follows. Users have the traditional username/password combination (in one table, created when they register), but also have 'extra' names they can log in under (in another table, but only using the one password in the first table).

    What I want to be able to do, is have a user log in with their initial username OR any of their extra names, using their initial password. That is, user 'Foo' registered with the password 'secret'. Foo later added 'Bar' as an extra name he can log in with, so using Foo/secret or Bar/secret would log the user in.

    Initially I'd ran two seperate queries, one to check if the 'name' given was a username (from the users table) or an extra name (from the names tale). if in the first case, retrieve the password and check it against the submitted one. If not, retrieve the user ID and then select the username/password combo from the users table with that ID.
    (The user ID is the only field which connects the users table and names table.)

    I've got a feeling this can be done with a single query, though .. something like

    $sql = "SELECT u.userName, u.userPass FROM users u, names n WHERE (u.userName = $name OR n.userName = $name AND u.userID = n.userID)";

    Where $name is the supplied username. That should give a row with userName and userPass for the user regardless if they used a username or an extra name to log in with ..

    Am I on the right track? I'd appreciate any help!

    Many thanks,
    Alex ...

  2. #2
    SitePoint Addict
    Join Date
    May 2004
    Location
    .
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Here is the SELECT code:

    Code:
    SELECT u.username, p.password FROM u, p WHERE (u.username = 'brr' AND p.password = 'foo' AND u.login_id = p.login_id)
    and the following is the sample table:

    Code:
    CREATE TABLE `p` (
      `id` int(11) NOT NULL auto_increment,
      `login_id` int(11) NOT NULL default '0',
      `password` varchar(50) NOT NULL default '',
      KEY `id` (`id`)
    ) TYPE=MyISAM AUTO_INCREMENT=4 ;
    
    INSERT INTO `p` VALUES (3, 1, 'foo');
    
    CREATE TABLE `u` (
      `id` int(11) NOT NULL auto_increment,
      `login_id` int(11) NOT NULL default '0',
      `username` varchar(50) NOT NULL default '',
      KEY `id` (`id`)
    ) TYPE=MyISAM AUTO_INCREMENT=3 ;
    
    INSERT INTO `u` VALUES (1, 1, 'arr');
    INSERT INTO `u` VALUES (2, 1, 'brr');
    I hope this helps.
    Ilya Devyatovsky
    ThinkHost, Inc.
    Wind/solar powered web hosting - 6 months free!

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that, but the query I posted originally did the trick for me.

    Only, it needed a LIMIT 0,1 because if you logged in with your original name, it also returned all the extra names you registered too. Thanks anyway!

    By the way, this one query from two tables is faster than two seperate queries, right?

    Alex ...

  4. #4
    SitePoint Addict
    Join Date
    May 2004
    Location
    .
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both ways will work fast enough
    Ilya Devyatovsky
    ThinkHost, Inc.
    Wind/solar powered web hosting - 6 months free!

  5. #5
    SitePoint Evangelist vikrantkorde's Avatar
    Join Date
    Jun 2004
    Location
    Mumbai, India
    Posts
    541
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi alex,

    u can also try this.

    $sql = "SELECT users.userName, users.userPass FROM users , names WHERE (users.userName = $name OR names.userName = $name AND users.userID = names.userID)";

    eventually both are same. Just a difference is that u r writing table name itself than the aliase.


Bookmarks

Posting Permissions

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