SQL -显示名称而不是ID - SQL - Displaying names instead of ID

SQL - Displaying names instead of ID

本文关键字：ID 显示 SQL | 更新日期: 2023-09-27

我有两个表

rankID | name

1 | new

2 |学习者

3 |经验丰富

4 | pro

另一个是所有的用户信息和密码之类的

id | username | rankID

1 | hello | 3

2 | hey | 3

我已经走了这么远，所以我可以显示他们的排名号码，但我想显示排名名称。我该怎么做呢?我已经尝试了很多东西，但我不太擅长sql和php的一部分。

这是我用来显示排名的代码

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

和显示排名号:

Rank: <?php echo $rank; ?>

简单的JOIN查询:-

"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);

: -

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

或

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

Not:-其他程序员在评论和回答中列出了许多其他可能的方法

//获取数据

$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'];

和显示数据:

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

希望这对你会有帮助。

$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;
}

请使用下面的代码

$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; ?>

当您想从两个不同的表中获取数据时。您需要连接查询。以下是你的问题，它将绝对解决你的问题:

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

有关如何连接两个表的更多信息，请参阅:http://www.tutorialspoint.com/sql/sql-using-joins.htm

请尝试一下

//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;

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;