MySQL + PHP：使用外键获取数据 [英] MySQL + PHP: fetching data using foreign keys
本文介绍了MySQL + PHP：使用外键获取数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
用户表：
alt text http://i33.tinypic.com/1eq6n5.png
挂表：
alt text http://i37.tinypic.com/b5po5u.png
编辑：我想不出如何显示数据。
<？php include（'config.php'）; ？>
<！DOCTYPE html PUBLIC - // W3C // DTD XHTML 1.1 // ENhttp://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd\">
< html>
< head>
< title> Alpha< / title>
< link rel =stylesheethref =style.csstype =text / css/>
< / head>
< body> （$ _ SESSION ['LoggedIn']）&&！empty（$ _ SESSION（$ _ $））
//登录
（'Email']））{
//发布到数据库
if（！empty（$ _ POST ['message']））
{
$ message = mysql_real_escape_string（$ _ POST ['message']）;
$ postmessage = mysql_query（INSERT INTO Wall（Message，UserID）VALUES（'。$ message。'，'。$ _ SESSION ['UserID']。'））;
// Collet最新帖子
$ result = mysql_query（'SELECT Message，UserID
从Wall
ORDER BY MessageID DESC
LIMIT 20'）或死（'无效查询：'。mysql_error（））;
$ b $ // Collet Post User
$ query = mysql_query（'SELECT Forename，Surname FROM Users INNER JOIN Wall ON Users.UserID = Wall.UserID;'）or die（ '无效的查询：'。mysql_error（））;
？>
< div id =container>
< div id =insideleft>
< ul>
< li>< a href =index.php>主页< / a>< / li>
< li>< a href =profile.php>编辑个人资料< / a>< / li>
< li>< a href =wall.php>社区墙< / a>< / li>
< li>< a href =logout.php>注销< / a>< / li>
< / ul>
< / div>
< div id =insideright>
< h1>社区墙< / h1>
< br />
< form method =postaction =wall.phpname =wallpostid =wallpost>
< label for =messageclass =message>消息：< / label> < input type =textname =messageid =messageclass =message/>
< input type =submitname =messagesubid =messagesubvalue =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>
正如您所看到的，我正在输出消息，但是我不知道如何输出Forename和Surname 。
解决方案
$ hostname ='localhost';
$ username ='username';
$ password ='password';
$ dbname ='database';
$ b $ db = new PDO（mysql：host = $ hostname; dbname = $ dbname，$ username，$ password）;
$ query =<<< QUERY
SELECT姓氏，姓氏
从用户
INNER JOIN墙上用户。用户ID = Wall.UserID;
QUERY;
$ statement = $ db-> query（$ query）;
$ rows = $ statement-> fetch（PDO :: FETCH_ASSOC）;
print_r（$ rows）;
$ db = null;
编辑：
给定新的信息，您的查询到一个。
<？php include（'config.php'）; ？>
<！DOCTYPE html PUBLIC - // W3C // DTD XHTML 1.1 // ENhttp://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd\">
< html>
< head>
< title> Alpha< / title>
< link rel =stylesheethref =style.csstype =text / css/>
< / head>
< body> （$ _ SESSION ['LoggedIn']）&&！empty（$ _ SESSION（$ _ $））
//登录
（'Email']））{
//发布到数据库
if（！empty（$ _ POST ['message']））
{
$ message = mysql_real_escape_string（$ _ POST ['message']）;
$ postmessage = mysql_query（INSERT INTO Wall（Message，UserID）VALUES（'。$ message。'，'。$ _ SESSION ['UserID']。'））;
}
// Collet最新帖子
$ query =<<< QUERY
SELECT Users.UserID，Message，Forename，Surname
从用户
INNER JOIN Wall ON Users.UserID = Wall.UserID;
ORDER BY MessageID DESC
LIMIT 20;
QUERY;
$ result = mysql_query（$ query）或者死（'Invalid query：'。mysql_error（））;
// Collet Post User
？>
< div id =container>
< div id =insideleft>
< ul>
< li>< a href =index.php>主页< / a>< / li>
< li>< a href =profile.php>编辑个人资料< / a>< / li>
< li>< a href =wall.php>社区墙< / a>< / li>
< li>< a href =logout.php>注销< / a>< / li>
< / ul>
< / div>
< div id =insideright>
< h1>社区墙< / h1>
< br />
< form method =postaction =wall.phpname =wallpostid =wallpost>
< label for =messageclass =message>消息：< / label> < input type =textname =messageid =messageclass =message/>
< input type =submitname =messagesubid =messagesubvalue =Post/>< br />< br />
< / fieldset>
< / form>
<？php while（$ row = mysql_fetch_assoc（$ result））{？>
< p>< / p>
< p>
<？php
echoMessage：.stripslashes（$ row ['Message']）。< br />;
echoName：{$ row ['Surname']}，{$ row ['Forename']};
？>
< / p>< br />
<？php
}？>
< / div>
< / div>
<？php
}
// else {echo< meta http-equiv ='refresh'content ='0; index.php'>; }
？>
< / body>
< / html>
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.)
Users Table: alt text http://i33.tinypic.com/1eq6n5.png
Wall Table: alt text http://i37.tinypic.com/b5po5u.png
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.
解决方案
$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>
这篇关于MySQL + PHP：使用外键获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
![alt text http://i37.tinypic.com/b5po5u.png](\"http://i37.tinypic.com/b5po5u.png\"){kind=link}
