SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot cools_sonu's Avatar
    Join Date
    Jan 2010
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql multi table select

    I'm developing a site where I need to pull data from multiple table at once.

    Earlier I was doing fine, but now when I practiced that code in phpmyadmin, it shows multiple table for just one query

    Say for example.

    I've two tables on MYISAM engine and when I was trying executing with the code. below

    Code MySQL:
    select users.password, userstatus.securityQ, userstatus.securityA from users, userstatus where users.id='$uid'

    then it shows multiple rows, and only the last row is relevant to my query while all others are just irrelevant.

    Now when I modified the same code with just one additional parameter then it fetching the correct query

    Code MySQL:
    select users.password, userstatus.securityQ, userstatus.securityA from users, userstatus where users.id='$uid' and userstatus.uid='$uid'"

    Though I've got the flaw here, but I'm no idea why the first code is not working properly. As per my level of knowledge, it should work fine as my userstatus is connected with users table with "uid".

    So here uid is unique in both tables.

    Please any body help me out here.

  2. #2
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First off, don't quote integer values, so change ='$uid' to just =$uid.

    Secondly, the first select query works fine. It is supposed to select multiple rows.

    It joins both tables and then selects all rows from the joined table where users.id equals to $uid. You should write it like this to get only one row:

    Code:
    SELECT * FROM users u 
    LEFT INNER JOIN userstatus us ON us.uid = u.id 
    WHERE u.id = $uid;
    Notice the ON us.uid = u.id part. It tells the db how to join tables, if you don't specify that the db will join all rows from first table with one row from the second table (the one where id is equal to $uid). I hope that makes sense.

  3. #3
    SitePoint Zealot cools_sonu's Avatar
    Join Date
    Jan 2010
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by risoknop View Post
    First off, don't quote integer values, so change ='$uid' to just =$uid.

    Secondly, the first select query works fine. It is supposed to select multiple rows.

    It joins both tables and then selects all rows from the joined table where users.id equals to $uid. You should write it like this to get only one row:

    Code:
    SELECT * FROM users u 
    LEFT INNER JOIN userstatus us ON us.uid = u.id 
    WHERE u.id = $uid;
    Notice the ON us.uid = u.id part. It tells the db how to join tables, if you don't specify that the db will join all rows from first table with one row from the second table (the one where id is equal to $uid). I hope that makes sense.
    Thanx for the help.

    I'm a newbie in database coding and structuring, while I tried your code, and when I remove the left word from the code, then it was working fine.

    It seems joins are really handy with dealing with multiple tables at one go.

  4. #4
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cools_sonu View Post
    Thanx for the help.

    I'm a newbie in database coding and structuring, while I tried your code, and when I remove the left word from the code, then it was working fine.

    It seems joins are really handy with dealing with multiple tables at one go.
    Yeah, sorry, it should just be INNER JOIN. I can't edit my previous post now


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
  •