MySQL + PHP: fetching data using foreign keys
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:
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>
开发者_C百科<?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>
select w.MessageID, w.Message, u.UserID, u.Forename, u.Surname
from Wall w
inner join Users u on w.UserID = u.UserID
Or in an alternative form:
SELECT w.*, u.Forename, u.Surname
FROM Wall w, Users u
WHERE w.UserID=u.UserID
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)");
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.
加载中,请稍侯......
精彩评论