ansaurus

Question

MySQL + PHP: fetching data using foreign keys

Answer 1

A:

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

RedFilter 2010-08-15 20:10:25

Answer 2

+2 A:

$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"&gt;
<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>

Zurahn 2010-08-15 20:15:41

+1 for a full answer with php source code :)

silvo 2010-08-15 20:17:12

Thanks for help so far, However i have no idea how to output the data with the current message being printed.

ritch 2010-08-15 20:30:33

cheers again, I get the following message "Invalid query: Not unique table/alias: 'Wall'"

ritch 2010-08-15 20:46:05

Any chance of help on this matter? Would be very great full. Thanks

ritch 2010-08-15 20:51:53

change 'FROM USERS, WALL' to 'FROM USER' (lose the ', wall') Joined tables can always show there results without calling them in the from.

Tim 2010-08-15 21:12:11

Answer 3

A:

Or in an alternative form:

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

silvo 2010-08-15 20:15:52

Thanks for help so far, However i have no idea how to output the data with the current message being printed.

ritch 2010-08-15 20:31:04

Answer 4

+1 A:

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.

Centurion 2010-08-15 20:51:56

Hi, Thanks. I'm using the code provided by Zurahn below however I get the following output on execute: "Invalid query: Not unique table/alias: 'Wall'"Any chance of help on this? Cheers

ritch 2010-08-15 20:59:41

Ok, I changed the query, take a look.

Centurion 2010-08-15 21:21:01

ansaurus

tags:

views:

answers:

MySQL + PHP: fetching data using foreign keys

related questions