Getting information from 3 tables 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 :)

Sounds like your original question(s) have now been answered (as you're no longer getting a blank page, rather an unexpected resultset), and you're now moving on to the next step in debugging your app. You should 1. upvote everyone who has helped, and 2. accept the solution that ultimately helped, then 3. create a new question, being precise in your explanations. "Eg in my database there is 3 row that have the post id 1" - in which table are these three rows?? Help us help you by being careful to frame your questions well. — Sepster

Sepster Sepster · Accepted Answer · 2013-03-28T13:26:11

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")

Getting information from 3 tables SQL & PHP

3 Answers