"nested" mysql select commands

[christophermichael]

I am attempting to use use two tables from the same database as described below. My db connection is made and is good.

 

As part of a larger application I am creating a comments feature. Each row is a separate comment that was left by a user.  I've no problem getting each row from the database and printing each out as I want formatted.

 

The second table I am using contains all of the personal info of each member including their username, their displayed name, location and among other things the URL to an image they want associated with their account.

 

Each row in the comments table contains the following id # of the comment, username of the person that left it, the date and of course the body of the message itself.

 

Now I am trying to get the comment to show the image found at the URL in the second table (personal info).

 

So for each row in the comments table I want it to pull the commentor's img_url from the second table.

 

$result = mysql_query("SELECT * FROM page_comments WHERE page_id = '$id'");

if(mysql_num_rows($result)==0) {
	echo "There are currently no comments.";
}else{
  	while($row = mysql_fetch_array($result)) {

  	echo "<table>";
echo "<tr>";
echo "<td>";
echo "<img sro=\"$img_url\" width=50 height=50>";
echo "</td>";
echo "<td>";
echo "<a href=profile.php?member=";
echo $row['commentor'];
echo ">";
echo $row['commentor'];
echo "</a>";
echo "</td>";
echo "<td>";
echo $row['date'];
echo "</td>";
echo "</tr>";
echo "<tr>";
echo "<td cospan=3>";
echo $row['comment'];
echo "</td>";
echo "</tr>";
echo "</table>";
echo "<br />";


 }
}

 

As you can see I've thrown the "$img_url" in there as a placeholder for the time being.

 

I've tried a couple of ideas and while none of them threw any errors it, of course, didn't return a value so the img was broken and the HTML source was just empty.

 

Any and all ideas are greatly appreciated.

[MrXHellboy]

You just want a image to pop up ?

img sro=\"$img_url\" width=50 height=50>

must be

img src=\"$img_url\" width=50 height=50>

[christophermichael]

Well that IS correct but that wasn't keeping it from displaying the picture as I do not know how to get the img_url info out of the second database.

[mdgeus]

Try this:

while($row = mysql_fetch_assoc($result)) {

[litebearer]

You have NOT queried the 2nd table yet. 

 

Query the 2nd table first, put the image url into a variable, THEN query the first table

 

[christophermichael]

Yes, my code is without a second query which I removed because it wasn't so much as an error in my code per-se as it was an error in logic.  If I were to query the second table first then I would be pulling every img_url for every single member. I guess I guess I could attempt it this way but I was trying to keep overheard to a minimum.

 

I was trying to open the first (the comments) database which is done in the example and then check the members table for the img_url for each member that has left a comment.

 

If there are only 3 comments available and there are 1,000 members I wouldn't want to pull in 1,000 possible img_url's when at most only three will be used. Less if one member left two of the comments.

 

A little later today I will respond with my coded attempt at what was suggested so you can see the second query.  I am still startlingly new to PHP so my error may be far more self evident that way.

 

Here is the code with the second query which I'd removed.  If it's the incorrect way to do it then it might be able to help someone point me the correct way.  This  should be a relatively common thing done within PHP but I spent two hours googling for correct ways to d what I want.

 

$result = mysql_query("SELECT * FROM page_comments WHERE page_id = '$id'");

if(mysql_num_rows($result)==0) {
	echo "There are currently no comments.";
}else{
  	while($row = mysql_fetch_array($result)) {
<!-- here I create an easier to use variable for the commentor which will be the member's username -->
$commentor = $row['commentor'];

<!-- This is my attempt at querying the second database to find the value of img_url in the row where the username matches -->
$query = mysql_query("SELECT img_url FROM profile WHERE username = '$commentor'");
    $img_url = mysql_fetch_array($query);

  	echo "<table>";
echo "<tr>";
echo "<td>";
echo "<img src=\"$img_url\" width=50 height=50>";
echo "</td>";
echo "<td>";
echo "<a href=profile.php?member=";
echo $row['commentor'];
echo ">";
echo $row['commentor'];
echo "</a>";
echo "</td>";
echo "<td>";
echo $row['date'];
echo "</td>";
echo "</tr>";
echo "<tr>";
echo "<td cospan=3>";
echo $row['comment'];
echo "</td>";
echo "</tr>";
echo "</table>";
echo "<br />";


 }
}

 

At least a part of my problem is that I am not sure how to handle the second query where I specify  the specific column in the specific row that I want as this should lead to only one possible value.

 

And again, I could be incorrect in my whole logic but it seems to make sense to me that I would pull only the one value for only those members that have left comments on that page.  I don't want there to be way too much bloat if a page has a thousand comments on it and tens of thousands of members if I were pulling the img_url of every member whether or not they left a comment.

[litebearer]

I was in error; I had presumed you were displaying all comments by 1 member.

 

Although NOT adept in JOINS, I suspect the eaiest solution will incorporate using the JOIN feature.

[christophermichael]

Thank you litebearer!

 

I know that my original post was semi-ambiguous. I do that because I try to keep my posts as general as possible. As I've been using Google to find help with my problems a lot of solutions to similar problems are so problem-specific that I couldn't seem to get them to work for my use.

 

I have solved the problem and I am going to post my working code so that if someone hits upon this through a search engine they can see my code and I'll explain my code following that. Turn my solution into a bit of a tutorial(?).

 

Before I get to the code here are a few things I should make known.  This code involves two databases with the following columns.

The way that I was displayed columns in the database tables is similar to BBcode so I had to make it 'code' to have the post submit.

 

page_comments: [comment_id] [page_id] [commentor] [date] [comment]

profile: [id] [username] [display_name] [img_url] [age] [location] [bio]

 

This code is used in page.php which accepts a page id # (which is stored in $page_id), displays whatever is in the database in the row for that page id # and then displays all of the comments left on that page by users which are signified by the first column in the page_comments table.

 

I know that grammatically 'commenter' is correct and 'commentor' is wrong but I think my word is cooler.

 

Now for the code:

 

$result = mysql_query("
SELECT page_comments.comment_number, page_comments.page_id, page_comments.commentor, page_comments.date, page_comments.comment, profile.img_url
FROM page_comments
INNER JOIN profile
ON page_comments.commentor=profile.username
WHERE page_id = '$id'
");

  	while($row = mysql_fetch_array($result)) {


  	echo "<table>";
echo "<tr>";
echo "<td>";
echo '<img src="';
echo $row['img_url'];
echo '" width=50 height=50>';
echo "</td>";
echo "<td>";
echo "<a href=profile.php?member=";
echo $row['commentor'];
echo ">";
echo $row['commentor'];
echo "</a>";
echo "</td>";
echo "<td>";
echo $row['date'];
echo "</td>";
echo "</tr>";
echo "<tr>";
echo "<td cospan=3>";
echo $row['comment'];
echo "</td>";
echo "</tr>";
echo "</table>";
echo "<br />";


 }

 

For the SELECT: You need to specify all of the columns which you want to be used in your query if they are going to be printed out.  The query will be using the username column of the profile table but since I won't be using the value outside of the query it doesn't have to be added to the select.  It is important that you follow the [table_name].[column] pattern here.  As you can see I am selecting the page_id, commentor, date and comment columns from the page_comments table and only the img_url column from the profile table.  If you wanted to use more than one column from the second database you may. Just make sure that you follow the same pattern of comma/space between them.

 

FROM is the first table you want to use. I used the table with the most used columns and the one that dictates which comments are actually being shown (specified by $page_id).

 

INNER JOIN (or just JOIN) is where you specify the second table where you will pull data from if/when the predicated (predicated by ON and WHERE operators) match up.

 

ON is where you specify which columns need to match in order for the column to be added to the first table.  In my code I needed to use page_comments.commentor=profile.username which tells MySQL to add the matching column from the second table to the appropriate row of the first table.  In my code I am saying that it should add the column to the row when the value in the commentor field of the page_comments table matches the value in the username column of the profile table.

 

WHERE is used specifically for my code. Depending on what you are doing you may not need it.  Since all of the comments for all of the pages are saved to one single table the code in page.php gets the page id # and performs the JOIN matching only on the rows where the page_id column equals the page # being viewed.  Please note the single quote around the $page_id variable in the sql query within the queries double quotes. SQL needs this to use the actual value of the variable.  I am making note of this because this alone had taken me some time to find the solution to.

 

So, in English basically the entire SQL query is telling your database:  If the value of the page_id column for each comment equals the # stored in $page_id then that row should be used.  Then the value stored in the img_url column of the profile table should be appended to the row  appropriate rows of the page_comments table when the commentor's name matches the username of the profile table.

 

I think the overall most difficult part of joining tables, and what made me have to view many examples to get mine to work, is that none of the examples I'd read explained it in a visual way.  You aren't technically creating a new table when you join (although it is possible and done often but is also outside my scope here) but theoretically you are. You are taking the columns you want from one table and adding to them the column(s) you want from another table.  So in a way you are appending columns to each individual row.  Here is a visual aide by what happens in my code:

 

[comment_id] [page_id] [commentor] [date] [comment]  and   [id] [username] [display_name] [img_url] [age] [location] [bio] become

 

[comment_id] [page_id] [commentor] [date] [comment] [img_url]

 

I am not positive if the 'new column' would be appended to the end so I am not sure whether or not you could define $img_url as $row[4] but you can use it just like the columns from the page_comments table when you use  while($row = mysql_fetch_array($result)) by way of $row['img_url'] as I did in my code.  It is now usable in all the same ways that the columns from the original database table can.

 

I hope this helps someone out there.