MySQL + PHP:使用外键获取数据 [英] MySQL + PHP: fetching data using foreign keys
问题描述
用户表:
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屋!