CODEWITHSUNDEEP
phpmysqljoin
Space
Space

Reputation: 23

Getting information from 3 tables SQL & PHP

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

Upvotes: 0

Views: 156

Answers (3)

Sepster
Sepster

Reputation: 4849

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

Upvotes: 1

Pouki
Pouki

Reputation: 1664

You need to specify mysql_query in PHP ... Else your query will not be executed

Like :

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

Upvotes: 1

James
James

Reputation: 22247

You're not executing any query.

$rs = mysql_query($query_comments);
$info = mysql_fetch_array($rs);

Upvotes: 2

Related Questions