Liam Liam - 6 months ago 10
MySQL Question

How to fetch data from a joined query in PHP

I've a joined query in PHP, which I wanted to join 2 databases VIA the user ID, but I want to be able to fetch data from both tables (users & user_stats), although it's not letting me output any data from the user_stats table, which is leaving me to believe there's a error in my query..

Hence this being my first time using joined tables, could someone please guide me in the correct direction, so far I have:

$getMembers3 = dbquery("SELECT users.id, users.look, users.username
FROM users
JOIN user_stats
ON users.id = user_stats.id
WHERE users.rank < 2 ORDER BY user_stats.Respect DESC LIMIT 10");


Which I am trying to fetch Respects from user_stats VIA:

while ($member2 = mysql_fetch_assoc($getMembers3))
{
echo $member2['user_stats.Respect'] . '<br>';
echo $member2['username'] . '<br>';
}


Although it allows me to view their username from the users table, it won't allow me to view the user_stats.Respect. If someone could enlighten me in the right direction that'd be fantastic.

Answer

I always get confused when selecting from mutliple tables and this is how I generally resolve my confusion:

$getMembers3 = dbquery("SELECT users.id as id, users.look as look, users.username as username, user_stats.Respect as respect
                        FROM users
                        JOIN user_stats
                        ON users.id = user_stats.id
                        WHERE users.rank < 2 ORDER BY user_stats.Respect DESC LIMIT 10");

This way you know what the column names are expected to be. Sidenote, the result set will not have dots in the array keys.

Then you can access results from:

while ($member2 = mysql_fetch_assoc($getMembers3))
    {
     echo $member2['respect'] . '<br>';
     echo $member2['username'] . '<br>';
    }
Comments