[PHP] Help Grabbing From 2 tables [MySql]

[JayC]

I tried to use a join statement, but I had a rough time because trying to get the specific user and then all that, so I was just going to use 2 queries in a while loop.

$q = mysql_query("SELECT * FROM users WHERE groupid = '" . $job['id'] . "' ORDER BY jobrank DESC") or die();
            while($user = mysql_fetch_assoc($q))
            {
                echo "<table width='100%'>";
                    echo "<tr><td width='0%'>";
                   
                   
                        echo "<img src='" . FigGen() . $user['figure'] . "'>";
                        $query2 = mysql_query("SELECT groupid,jobrank FROM users WHERE username = '".$user['username']."'");
                        $query3 = mysql_query("SELECT name FROM jobranks WHERE jobrank = '".$query2['groupid']."' AND rankid = '".$query2['jobrank']."'");
                    echo "
                   
                    </td>
                        <td>
                            <b>Name:</b> " . $user['username'] . " <br/>
                            <b>Rank:</b> " . $query3['name'] . " <br/>
                            <b>Last visit:</b> " . date("F j, Y, g:i a",$user['timestamp_lastvisit']) . "
                        </td>
                    </tr>";
               
                echo "</table><hr>";
            }

This is what the page looks like:

So I can't seem to get it to display the rank.

Users Table -> Groupid, jobrank
Jobranks Table -> Jobid, rankid

 

[RastaLulz]

For one, you shouldn't be using mysql_* functions; it's depreciated as of PHP 5.5, and removed in PHP 7.

Anyways, what's the point of $query1? You should already have that information from the initial query?

You could accomplish what you're doing with just one query:

SELECT u.figure, u.username, u.groupid, u.rank, j.name
FROM users AS u
INNER JOIN jobs AS j
ON j.jobid = u.groupid
WHERE u.groupid = ?
ORDER BY u.jobrank DESC

 

[griimnak]

I suggest you start to use PDO for your web applications.
It's secure, prepares your query before executing it, and you can bind certain params cleanly. Oh, and it's not depreciated.

However, on topic;

$query3 = mysql_query("SELECT name FROM jobranks WHERE jobrank = '".$query2['groupid']."' AND rankid = '".$query2['jobrank']."'");

AND rankid = '".$query2['jobrank']."'"

you sure that part is right?

Otherwise rasta should have you on this, man.

 

[JayC]

yes I have seen posts and I just haven't had time to change everything over to PDO I need to learn.

I will try these when I can OKAY, thanks guys.

 

[Sysode]

yes I have seen posts and I just haven't had time to change everything over to PDO I need to learn.

I will try these when I can OKAY, thanks guys.

Let us know how you got on. If complete then mark the best answer and we'll close the thread for you

 

[JayC]

For one, you shouldn't be using mysql_* functions; it's depreciated as of PHP 5.5, and removed in PHP 7.

Anyways, what's the point of $query1? You should already have that information from the initial query?

You could accomplish what you're doing with just one query:

SELECT u.figure, u.username, u.groupid, u.rank, j.name
FROM users AS u
INNER JOIN jobs AS j
ON j.jobid = u.groupid
WHERE u.groupid = ?
ORDER BY u.jobrank DESC

The problem is WHERE u.groupid = ? You don't know the group ID of the user, until in the loop when you are on THAT specific user.

 

[RastaLulz]

The problem is WHERE u.groupid = ? You don't know the group ID of the user, until in the loop when you are on THAT specific user.

What? Your first query is selecting users by their group id? How do you NOT know the group id?