Echoing data from two arrays in one table?

Hi,

I am trying to echo a comment, with the comment senders name, ina table, however, only the comments come up, I cant get the name of the sender to follow. The table structures are:

user
id, f_name

profilecomment
id, profileid, comment

The sender's ID is retrieved from a session variable and the profile ID is retrieved from GET.

Here is the code that I have so far:

<?php
$profilecomments = "SELECT id, comment FROM profilecomment WHERE profileid = '$user_id'";
$pc = mysql_query($profilecomments) or die();
$senderrow = mysql_fetch_array($pc); 
$senderid = $senderrow['id'];

$getfriends = "SELECT friendid FROM friends WHERE id = '$user_id'";
$gf = mysql_query($getfriends) or die();
$friendrow = mysql_fetch_array($gf);
 while ($rowgf = mysql_fetch_array($gf, MYSQL_ASSOC)) {
$getfriendid = $rowgf['friendid'];
 }

$getfname = "SELECT f_name, l_name FROM user WHERE id = '$getfriendid'";
$gfn = mysql_query($getfname) or die();
?>

Code (markup):

    <?php

if (mysql_num_rows($pc) == 0) {
	echo '<p><h2>' . $f_name . ' has no comments!</h2></p>' ;
} else {
$sender = "SELECT f_name FROM user WHERE id = '$senderid'";
$sc = mysql_query($sender) or die();

while($rowa = mysql_fetch_array($pc)) {
		$rows = mysql_fetch_array($sc);
		
echo '<table id="profiletable"><tr><td><a href ="userprofile.php?user=';
echo $rowa['id'];
echo '">';
echo $rowa['comment'];
echo ' from ';
echo $rows['f_name']."</a></tr></td></table>\n";
}
}
?> 

Code (markup):

Any help would be great, I've been trying to work this out for a couple of days now and I just cant get my head around it!

Thanks,

Gareth

 

You can do this easily with a JOIN statement (it lets you query multiple tables with a single mysql query). This code probably won't work because I suck at doing join queries, but it should point you in the right direction and save your sever a huge number of queries (everything you need can be done in a single query, as opposed to potentially hundreds that your code will execute):

$query = "select profilecomment.profilecomment as comment, profilecomment.profileid as pid, user.f_name as name from profilecomment left join user on profilecomment.profileid = user.id";

$result = mysql_query($query);

echo "<table><tr><td>Profile ID</td><td>Name</td><td>Comment</td></tr>";

while($row = mysql_fetch_object($result)) {
  echo "<tr><td>" . $row->pid . "</td><td>" . $row->name . "</td><td>" . $row->comment . "</td></tr>";
}

echo "</table>";

PHP:

Again, this probably won't work the way it is. Writing join statements takes alot of work, so you may need help from somebody that knows them really well. I'm sure somebody else here could clear up the code for you.