Sql multitable query

Databases
1

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.



$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

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

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

If you want a book, try Simply SQL

You can also find loads of info and tutorials online.

5

Hi.

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



$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

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

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