All geek questions in one place

MySQL + PHP: fetching data using foreign keys

I have 2 tables (Users, Wall). The UserID in the Wall table is a foreign key. How can I get user information using this? (I want to get the Forename and Surname users who sent the message.)

User table: alt text http://i33.tinypic.com/1eq6n5.png

Wall chart : alt text http://i37.tinypic.com/b5po5u.png

EDIT: I can't 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 was displaying a message, but I have no idea how to display the name and surname of the poster.

+3
source share
4 answers
$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: , .

<?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>
+8

- , sql- , Wall table : $result = mysql_query ("SELECT u.Forename, u.Surname, w.Message FROM Wall AS w INNER JOIN Users AS u ON(w.UserID=u.UserID)");

:

<p><?=stripslashes($row['Surname'])?></p><br />
<p><?=stripslashes($row['Lastname'])?></p><br />
<p><?=stripslashes($row['Message'])?></p><br />

, .

+1
select w.MessageID, w.Message, u.UserID, u.Forename, u.Surname
from Wall w
inner join Users u on w.UserID = u.UserID
0
source

Or in an alternative form:

SELECT w.*, u.Forename, u.Surname
FROM Wall w, Users u
WHERE w.UserID=u.UserID
0
source

Source: https://habr.com/ru/post/1759802/

More articles:


All Articles