MySQL + PHP：使用外键获取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3489017/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL + PHP: fetching data using foreign keys
提问by ritch
I have 2 tables (Users, Wall). The UserID in the Wall table is a foreign key. How would I go about fetching the users details using this? (I want to fetch the users Forename and Surname who posted the message.)
我有 2 个表（用户、墙）。Wall 表中的 UserID 是外键。我将如何使用它获取用户详细信息？（我想获取发布消息的用户姓名和姓氏。）
Users Table:
用户表：
Wall Table:
墙桌：
EDIT: I cannot figure out how to show the data.
编辑：我不知道如何显示数据。
<?php include('config.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html>
<head>
<title>Alpha</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<?php
// Logged IN
if(!empty($_SESSION['LoggedIn']) && !empty($_SESSION['Email'])) {
// Post to Database
if(!empty($_POST['message']))
{
$message = mysql_real_escape_string($_POST['message']);
$postmessage = mysql_query("INSERT INTO Wall (Message, UserID) VALUES('".$message."', '".$_SESSION['UserID']."')");
}
// Collet Latest Posts
$result = mysql_query('SELECT Message, UserID
FROM Wall
ORDER BY MessageID DESC
LIMIT 20') or die('Invalid query: ' . mysql_error());
// Collet Post User
$query = mysql_query('SELECT Forename, Surname FROM Users INNER JOIN Wall ON Users.UserID = Wall.UserID;') or die('Invalid query: ' . mysql_error());
?>
<div id ="container">
<div id="insideleft">
<ul>
<li><a href="index.php">Home</a></li>
<li><a href="profile.php">Edit Profile</a></li>
<li><a href="wall.php">Community Wall</a></li>
<li><a href="logout.php">Logout</a></li>
</ul>
</div>
<div id="insideright">
<h1>Community Wall</h1>
<br />
<form method="post" action="wall.php" name="wallpost" id="wallpost">
<label for="message" class="message">Message: </label> <input type="text" name="message" id="message" class="message"/>
<input type="submit" name="messagesub" id="messagesub" value="Post" /><br /><br />
</fieldset>
</form>
<?php while ($row = mysql_fetch_assoc($result)) { ?>
<p></p>
<p><?=stripslashes($row['Message'])?></p><br />
<?php
} ?>
</div>
</div>
<?php
}
//else {echo "<meta http-equiv='refresh' content='0;index.php'>";}
?>
</body>
</html>
As you can see I am outputting the message but I have no idea how to output the Forename and Surname of the poster.
如您所见，我正在输出消息，但我不知道如何输出海报的名字和姓氏。
回答by Derek H
$hostname = 'localhost';
$username = 'username';
$password = 'password';
$dbname = 'database';
$db = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
$query = <<<QUERY
SELECT Forename, Surname
FROM Users
INNER JOIN Wall ON Users.UserID = Wall.UserID;
QUERY;
$statement = $db->query($query);
$rows = $statement->fetch(PDO::FETCH_ASSOC);
print_r($rows);
$db = null;
EDIT:Given the new information, you should combine your queries into one.
编辑：鉴于新信息，您应该将您的查询合并为一个。
<?php include('config.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html>
<head>
<title>Alpha</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<?php
// Logged IN
if(!empty($_SESSION['LoggedIn']) && !empty($_SESSION['Email'])) {
// Post to Database
if(!empty($_POST['message']))
{
$message = mysql_real_escape_string($_POST['message']);
$postmessage = mysql_query("INSERT INTO Wall (Message, UserID) VALUES('".$message."', '".$_SESSION['UserID']."')");
}
// Collet Latest Posts
$query = <<<QUERY
SELECT Users.UserID, Message, Forename, Surname
FROM Users
INNER JOIN Wall ON Users.UserID = Wall.UserID;
ORDER BY MessageID DESC
LIMIT 20;
QUERY;
$result = mysql_query($query) or die('Invalid query: ' . mysql_error());
// Collet Post User
?>
<div id ="container">
<div id="insideleft">
<ul>
<li><a href="index.php">Home</a></li>
<li><a href="profile.php">Edit Profile</a></li>
<li><a href="wall.php">Community Wall</a></li>
<li><a href="logout.php">Logout</a></li>
</ul>
</div>
<div id="insideright">
<h1>Community Wall</h1>
<br />
<form method="post" action="wall.php" name="wallpost" id="wallpost">
<label for="message" class="message">Message: </label> <input type="text" name="message" id="message" class="message"/>
<input type="submit" name="messagesub" id="messagesub" value="Post" /><br /><br />
</fieldset>
</form>
<?php while ($row = mysql_fetch_assoc($result)) { ?>
<p></p>
<p>
<?php
echo "Message: ".stripslashes($row['Message'])."<br />";
echo "Name: {$row['Surname']}, {$row['Forename']}";
?>
</p><br />
<?php
} ?>
</div>
</div>
<?php
}
//else {echo "<meta http-equiv='refresh' content='0;index.php'>";}
?>
</body>
</html>
回答by silvo
Or in an alternative form:
或者以另一种形式：
SELECT w.*, u.Forename, u.Surname
FROM Wall w, Users u
WHERE w.UserID=u.UserID
回答by Centurion
As here the main target are messages you write in a sql query first the table with message, in your example Wall table and the query can look like this:
$result = mysql_query ("SELECT u.Forename, u.Surname, w.Message FROM Wall AS w INNER JOIN Users AS u ON(w.UserID=u.UserID)");
由于这里的主要目标是你的消息在SQL查询中首先与消息表写在你的榜样墙表和查询可以是这样的：
$result = mysql_query ("SELECT u.Forename, u.Surname, w.Message FROM Wall AS w INNER JOIN Users AS u ON(w.UserID=u.UserID)");
Now when output use:
现在当输出使用：
<p><?=stripslashes($row['Surname'])?></p><br />
<p><?=stripslashes($row['Lastname'])?></p><br />
<p><?=stripslashes($row['Message'])?></p><br />
Of course do the proper formatting.
当然要进行正确的格式化。
回答by RedFilter
select w.MessageID, w.Message, u.UserID, u.Forename, u.Surname
from Wall w
inner join Users u on w.UserID = u.UserID
