SQL - Displaying names instead of ID

Question

I have 2 tables

rankID | name

• 1 | new
• 2 | learner
• 3 | experienced
• 4 | pro

And another with all the user info and passwords and stuff

id | username | rankID

• 1 | hello | 3
• 2 | hey | 3

I have come so far so I can display their rank number, but I want to display the rank name. How can I do that? I have tried a lot of things but I'm not so good at sql and the php part of it.

This is the code I use to display the rank number

//Get rankID
$query = "SELECT rankID FROM users WHERE id = '$userId'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);
$rank = $row['rankID'];

And to display the rank number:

Rank: <?php echo $rank; ?>

Answer 1

Simple JOIN query :-

"SELECT rank.name as rank_name,users.rankID as rankID from users LEFT JOIN rank ON rank.rankID = users.rankID WHERE id = '$userId'"

And then After:-

$query = "SELECT rank.name as rank_name,users.rankID as rankID from users LEFT JOIN rank ON rank.rankID = users.rankID WHERE id = '$userId'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);

Do:-

$rank = $row['rankID'];
$rank_name = $row['rank_name'];

Rank: <?php echo $rank; ?>
RankName: <?php echo $rank_name; ?>

Or

$rank_data = $row;
Rank: <?php echo $rank_data['rankID']; ?>
RankName: <?php echo $rank_data['rank_name']; ?>

Not:- lot of other possible ways are there which are listed by other programmers in comment and answer as well.

Answer 2

//Get datas

$query = "SELECT rankID, name FROM users WHERE id = '$userId'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);
$rank = $row['rankID'];
$rank = $row['name'];

And to display the datas:

Rank: <?php echo $rank; ?>
Name: <?php echo $name; ?>

Answer 3

Hope so this should make a trick for you.

$query = "SELECT rankID FROM users WHERE id = '".$userId."'";
$result = $conn->query($query);
$count = $result->num_rows;
if($count==0)
{
   return false;
}
else
{
     $rows=[];
     while($row = $result->fetch_assoc())
     {
        $rows[] = $row;
     }
     return $rows;
}

Answer 4

Please use below code

$query = "SELECT name FROM users as u JOIN rank as r ON r.rankID = u.rankID WHERE u.id = '$userId'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);
$name = $row['name'];


Name: <?php echo $name; ?>

Answer 5

try this:

//Get rankID
$query = "SELECT rankID, rank.name AS rank_name FROM rank, users WHERE id = '$userId' and users.rankid = rank.rankid";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);
$rank = $row['rank_name'];
echo $rank;

Answer 6

When you want to get data from two different table.You need join query. Here is your query which will solve your proble definitely :

$q="select a.name,b.rankID from rankname as a INNER JOIN user as b
ON a.rankID = b.rankID";

For more know about How to join two tables see this:http://www.tutorialspoint.com/sql/sql-using-joins.htm

Hope this will help you better.

Answer 7

Please try this

//Get rankID
$query = "SELECT r.name as rank_name FROM rank as r inner join users as u on r.rankID = u.rankID WHERE u.id = '$userId'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);
$rank = $row['rank_name'];

echo 'Rank: '. $rank;