SitePoint Sponsor

User Tag List

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

    sql multitable query

    Hello All.

    I'm developing a website which connects to database and retrieve information from it and then displays it.

    I'm newbie in database structure and it's related queries.

    If I need to retrieve information from multitable then I'm using the command as such.

    Code MySQL:
     
    $sql = "select users.*, userinfo.*, userstatus.* post.*, feedback.* from users, userinfo, userstatus, post, feedback where users.id = $uid and userinfo.uid = $uid and userstatus.uid = $uid and post.uid = $uid and feedback.uid = $uid";

    This is one simple example as I've selected all colums and rows from these tables, but I don't need to extract all colums, only specific ones.

    But my code is becoming too large.

    Is there any way to make it short ?

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No it's not too long, it's just wrong

    $sql = "select users.*, userinfo.*, userstatus.* post.*, feedback.* from users, userinfo, userstatus, post, feedback where users.id = $uid and userinfo.uid = $uid and userstatus.uid = $uid and post.uid = $uid and feedback.uid = $uid";

    You have no joins in this query!

    Start by joining the users to users info, then user info to userstatus, then userstatus to post, then post to feedback, then the bit about userid = $uid. At least that's a first guess, as we can't see the actual table details just now.

    Then start asking yourself do you really need every single bit of info in every single table?

    Then read about normalisation and check that the tables are normalised properly. Which from their names alone, I suspect they are not.

  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 Dr John View Post
    No it's not too long, it's just wrong

    $sql = "select users.*, userinfo.*, userstatus.* post.*, feedback.* from users, userinfo, userstatus, post, feedback where users.id = $uid and userinfo.uid = $uid and userstatus.uid = $uid and post.uid = $uid and feedback.uid = $uid";

    You have no joins in this query!

    Start by joining the users to users info, then user info to userstatus, then userstatus to post, then post to feedback, then the bit about userid = $uid. At least that's a first guess, as we can't see the actual table details just now.

    Then start asking yourself do you really need every single bit of info in every single table?

    Then read about normalisation and check that the tables are normalised properly. Which from their names alone, I suspect they are not.

    Thanx Dr. John for the reply. I too wanna learn joins..

    Can u suggest any good book or tutorial for learning the basics of joins.

    I'm planning to read a book from Apress publisher's Beginning SQL Queries.

    Can u suggest me any good book ?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you want a book, try Simply SQL

    You can also find loads of info and tutorials online.

  5. #5
    SitePoint Zealot cools_sonu's Avatar
    Join Date
    Jan 2010
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    Can any one suggest me, now my code is wrong or somewhat I'm going in the right way.

    Code MySQL:
     
    $sql = "select * from users u, userinfo ui, userstatus us, post p, feedback fb where u.id = ui.uid and ui.uid = us.uid and us.uid = p.uid and p.uid = fb.uid and u.id = $uid";

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cools_sonu View Post
    Can any one suggest me, now my code is wrong or somewhat I'm going in the right way.
    your code is not technically wrong but it is very poor in three different ways

    1. don't use the dreaded, evil "select star" -- instead, list only the columns you really want

    2. don't use "comma" joins -- instead, use JOIN syntax

    3. don't write your query on one single, humoungously long line -- instead, use line breaks and indentation to make it easier to read
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star
      FROM users AS u
    INNER
      JOIN userinfo AS ui
        ON ui.uid = u.id
    INNER
      JOIN userstatus AS us
        ON us.uid = ui.uid 
    INNER
      JOIN post AS p
        ON p.uid = us.uid
    INNER
      JOIN feedback AS fb 
        ON fb.uid = p.uid 
     WHERE u.id = $uid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •