从3个表SQL和PHP中获取信息

Question

Im making a blog like system using HTML, CSS, PHP and MySQl. The site is made up of three tables.

user (id, username, password, email)
posts (postid, title, post)
comments (postid, id, comment, commentid) postid coming from posts and id from user.

I am trying to display all of the comments and the users username who left them for a certain post.

When i use this query in phpmyadmin: SELECT user.username, comments.comment FROM user INNER JOIN comments on user.id=comments.id where postid=1

It shows what i need.

When i add it into php i get a blank page.

<?php
//echo "1";
session_start();

$connect = mysql_connect('localhost', 'root', 'root') or die("Couldn't connect");
mysql_select_db("com541blog") or die("Couldn't connect to database");

//echo "2";
//$postid = $_GET['type'];
$_SESSION['postid'] = $postid;

//echo "3";
$query_comments = mysql_query("SELECT user.username as username, comments.comment as comment     FROM user INNER JOIN comments on user.id=comments.id WHERE postid='1'");

$info = mysql_fetch_array($query_comments);

$username = $info['username'];
$comment = $info['comment'];

echo $username;                         
echo $comment;

?>

Thanks in advance for the help :)

Answer 1

Your first line has an error I suspect, ie missing 'c' near the end of 'connect'.

include("db_connet.php"); should be include("db_connect.php");

Also, missing a semi-colon ;. This:

$query_comments = ("SELECT user.username, comments.comment 
                    FROM user INNER JOIN comments on user.id=comments.id 
                    where postid=1")

Should read:

$query_comments = ("SELECT user.username, comments.comment 
                    FROM user INNER JOIN comments on user.id=comments.id 
                    where postid=1");

Also, not bad practice to qualify each of your column names with a table name eg user.username as you're doing. But you might prefer eg the following more concise syntax using table aliases:

$query_comments = ("SELECT u.username, c.comment 
                    FROM user u INNER JOIN comments c on u.id = c.id 
                    where c.postid = 1");

(Note the table aliases don't need to be a single letter, so can be handy reducing a table name such as "ManufacturerSuppliedPartsListData_Feb01", to eg "mpl", without losing their meaning. Or eg if you've got "Customers" and "Credit" instead of just "c" you might use eg "cust" and "cred")