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.
$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.
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";
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
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