christophermichael

I can elaborate if necessary but this should be pretty straightforward. The site I am coding has a membership system of course. I would like to create associations between members, basically what one might call a friend's list. But I am not sure how to create this table of relationships. I do not want to have a table for every member to store each of their 'friends'. I am not sure about a table with each row being a particular member with their username as the index. Each column could be a friend but if someone has 200 friends then someone with only 3 would end up with 197 blank fields. And it seems to be a logistical nightmare to have to deal with the random number of friends that might be listed in each user's row. I am curious if there are any better ideas of how to achieve storing such information. Thanks!

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.

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.

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.

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.

I will look into going that route. It's very interesting that you should be the one to respond as it was your pagination tutorial that spawned my question haha It would have come up eventually but this was what first brought it to my mind. Here's hoping I get it right. And thanks for the help and the tutorial!

Due to my home brewed templating system I must use 'return' instead of 'echo' or 'print'. I have a simple database query and running a while loop where all rows are to "printed" to the browser. But when I use return only the first row is shown. Using echo will print out all of the rows as it should but of course it prints the rows outside of where they should in the template. This may be very simple but I am just starting out. I tried to use the forum's search to see if this has been answered but the daemon could not 'be reached' and searching on google brings up a ton of non-helpful links. The solution will be very helpful as I will be doing this A LOT in my scripts for my current project. Thank you!

In the first snippet you are echoing a variable that has no value. It will echo to the browser exactly what you tell it to. And in that case you are telling it to echo nothing. In the second snippet you've assigned a value to the variable before you call it. Hopefully this is along the lines of what you were asking. Not positive because I'm not sure why you'd prefer it the first way.

Aha! Well it was slightly different than you had suggested and of course the site title was the only part that WAS where it was supposed to be. But you were entirely right that I should go with return and not echo. No idea why it hadn't occurred to me! Greatly appreciated as it was really holding my progress up!

I am about as new to PHP as one can get but I had done something similar to what I am trying to do now nearly a decade ago. I am using perhaps the most simple templating system I can. I have VERY specific needs and 'engines' such as SMARTY are too big for my purposes. My program is quite simple as far as the templates go. Straight HTML-based template file with tags. Naturally. I will have a specific list of usable tags defined for use by the end-user to create their templates. I will post an example of the template class file, the html template file and one of the php files that will tie it together. I want to state that everything is working exactly as it should except for template tags which invoke a function. The functions are running exactly as they should and they output exactly what they should. However what it puts out is being put before the opening HTML tag. I haven't shown the code yet but the value of [[site_title]] DOES show between the HTML title tags as it should. But the two which kick off functions are before the HTML. I ran into the same thing when I first tried a very similar templating deal in PERL and someone pointed out that I needed to make everything that the functions put out concatenates to $template so that it is 'compiled' within the template. But I am not sure if this is what I need to do in PHP or exactly how I would. It could be quite similar but unfortunately I can't recall how I had done it in PERL oh so long ago. Again, I want to throw out that the IF statements within the functions do exactly what they are supposed to do. It's just not showing where it should. Any and all help is greatly appreciated. I will be using this tiny 'engine' in several projects and I will include credit in associated files for any person(s) that are able to help. [attachment deleted by admin]

Hello! Up until about 3 years ago I had been writing scripts in CGI/PERL but then became waaaay to busy with work and personal pursuits. Now I'm coming back but I'm making the switch over to PHP. I will be posting when I have specific questions that seem to be too specific for me to find answers on here or online by searching. I look forward to talking PHP with everyone on here whether they be novice or advanced expert! And as I learn more, I too, will help answer questions where I am able. Let's rock this!