cools_sonu — 2010-04-06T14:36:37-04:00 — #1
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 ?
dr_john — 2010-04-06T15:13:48-04:00 — #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.
cools_sonu — 2010-04-08T05:20:12-04:00 — #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 ?
guido2004 — 2010-04-08T05:47:29-04:00 — #4
If you want a book, try Simply SQL
You can also find loads of info and tutorials online.
cools_sonu — 2010-04-08T11:13:02-04:00 — #5
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";
r937 — 2010-04-08T11:37:23-04:00 — #6
your code is not technically wrong but it is very poor in three different ways
don't use the dreaded, evil "select star" -- instead, list only the columns you really want
don't use "comma" joins -- instead, use JOIN syntax
don't write your query on one single, humoungously long line -- instead, use line breaks and indentation to make it easier to read
FROM users AS u
JOIN userinfo AS ui
ON ui.uid = u.id
JOIN userstatus AS us
ON us.uid = ui.uid
JOIN post AS p
ON p.uid = us.uid
JOIN feedback AS fb
ON fb.uid = p.uid
WHERE u.id = $uid