Easy JOIN in PHP using MySQL

First I will explain what this tutorial is to help you do JOINs and how to visualize the process.  Then I will show example code and finally rehash everything using my examples.

This tutorial is for PHP and MySQL.  I am not versed in any other database environment so it may or may not be similar for software other than MySQL.  This may make the code usable by less but even if you can't use the code the rest of this tutorial may help you visualize exactly what is going on and how to accomplish what you want to do so it could theoretically be useful for other languages and databases.

JOINs can help save database bloat which is always a good thing to avoid...ESPECIALLY if web server host imposes a disk quota (which most free ones do and some paid ones do for lower-end packages) which will usually also include data saved in all of your databases. Note that this will not lower your bandwidth as the same amount of data is being sent by the server to the clients' browser. How it helps eliminate bloat will be discussed in the next section.

Using a table join will allow you to use as many columns from one table that your script needs but also pull associated columns from another table to be used just as you would the columns of the first table.

Note that there are numerous types of joins and this will show you how to do the most basic one but it can help give you better understanding of the process which you can get down to help make learning the other types of joins easier.

Now, for a real world application...imagine that you are programming a system which will dynamically create pages.  The script generates 'pages' on-the-fly by pulling the data you want shown on it from the database and then prints/echos the data which creates a standard web page in the client's browser.  Now, to get other people involved you want to offer a comments feature where people that read the page can comment on it.

Now imagine that you already have a profile system with its own database where a user can save a path or URL to an image to be used as their icon/avatar on their profile and anywhere else that they post something on the site.

Here's a possible set up for the page_comments table:

1. comment_id - This will probably be an auto-incremented number which will act as the table's index.  This would be passed along for the sake of deleting or editing a comment. It gives every single comment left on every 'page' on your site it's own ID number.
2. page_id - For this set up each page has an ID number which the script uses to pull the right data from the database to print the viewed page.  This would be used in the comments table because we will make our script "know" to which page each individual comment belongs.  You don't want every comment left for every page on each page as that would be pointless.  In the form to leave a comment you'd need to pass along the page ID which would be saved into this column when a comment is left.
3. commentor - I use this to signify the name of the person that left the comment. In order for this to work, and in most cases this is how it would be done anyway, the commentor's name will be their username if they log in. In order for this code to work correctly these values would have to be the same. So the value in this column for each comment left should be a member's username which is almost definitely stored in another database.
4. date - This isn't essential but I'd like for people to see the date and time that a comment was left.
5. comment - Now this is the body of text which the member entered as their comment.

1. username - This is obviously the username for the member that left a comment.  Leaving a comment doesn't affect this profile table but we will be pulling from it when we display the comments that were saved.  This will most likely be the index column for the profile table.

Now I'll give line-by-line explanations and then help you visualize what's going on here.Start the MySQL query. I use "$result" but you can use what you want as long as you reference the same variable for the mysql_fetch_array at line 11.

2. We'll start with the SELECT command. You're most likely quite familiar with this as it is one of the most commonly used ones in queries. Now you'll name the columns you want to use separated by commas just like you would in a non-join query however there is one huge difference. You must reference the columns in the same table.column way that is shown in the example. This is viewed as "table name.column name". As with non-join queries you do not have to declare all available columns in the table. Just the ones that contain information that you will use in the rest of your code. All but one are from the page_comments table and the last one is from the profile table. We need only one specific column from the profile table but you could use more...just add others in the same manner.

4. Standard FROM command. For this join you will name the primary table which you can think of as being the one with the majority of the columns you'll be using. In this example we're using page_comments because basically we want to use all of the columns from that table but then append an extra column at the end of each row whose data is populated from the second table.
5. INNER JOIN - This is the most common type and you don't have to specify INNER. You can specify just JOIN and it will behave in the same manner. This is the table with the column(s) whose data will populate the extra column we're joining to the primary table that we're using. Of course in this example it's the profile table.
6. ON - This is the most essential part because it tells the query how you want to join the tables...as in which columns should match for the extra column to be added to the primary table. You must specify the columns in the same table.column format as in the SELECT command. In the example we are telling MySQL to create a match when the data contained in the commentor column of the page_comments is the same as the username column of the profile.
7. WHERE - This is used in the majority of MySQL queries as well. Because each individual comment "belongs" to a certain page and therefore has an integer value in the page_id column of the page_comments table we want to use only the rows of the table where that page_id value. So if someone views the page with the page_id '5' MySQL will return only the rows for comments which were left on the page with that ID.
10. WHILE - After performing the query we want to use returned results so we use mysql_fetch_array to create an associative array containing the keys and values of the rows returned by the query. Be certain to change the variable name ($result) in this line if you used a different one in the first line of the code.
12-16. On these lines I convert the rows into variables. Not everyone will do this but I do it because it makes it easier to echo the data from the database without having to concatenate $row['column_name'] and using more double quotation marks throughout your echo statement.
The rest: Put what you want echo'd to the browser which will most likely contain HTML, possibly table format and the data pulled from the database. And as always you need to format only one line as the statement will be echo'd once for each row that your MySQL query returned.
Of course within the query you can add commands such as LIMIT if you want only a certain number of rows returned which is imperative if you use this with something like my php pagination code. You can also add the ORDER command if you want to control how the rows are sorted. Technically the whole query could be written on one line but it becomes way too long so my code traverses several lines.

First picture a physical representation of your primary table. Across the very top would be the column names with the following rows being the results.  Just like in an HTML table.  So think of the page_comments table as:

comment_id | page_id | commentor | date | comment

And the profile table as (with unnecessary columns not listed):

username | img_url

You can think of the join as creating a temporary table.  No new table is created but for as long as the query returns rows it appends the img_url column value to the matching rows in the page_comments table to create:

The script will first match all rows where the value in the page_id column is equal to ID of the page you are viewing.  These are the only rows we care about during each query to be sure that we are echoing only the relevant comments.  Then for each row that is returned by the query MySQL will then look for the row in the profile table where the value of the username column matches the commentor name associated with each comment.  When a row from the profile table matches it adds the value of that row's img_url column to the page_comments row...like thus:

comment_id | page_id | commentor | date | comment | img_url

So now you can get the img_url to be used in an HTML img src tag to show their icon/avatar along with their commentor/user name, the date of the comment and the actual comment itself.  This, of course, will work for when the same user has commented on the same page multiple times.

I hope that you gained some solid knowledge on this and it will help you in your coding.  If I can make any changes to make this better or clearer please comment and let me know and I'll see what I can do.

Enjoy!

I found this tidbit out by messing around with my code.  This is especially important with this type of join.  If the database tables are set up correctly you wouldn't notice this anyway. But I am adding this section because I do want to give as much information on the topic as I can.

Your MySQL query will not match a row in the first table if the column which is being matched does not match the value in the column of the second table.

In order to test my forums I created a bunch of mock replies in the back-end where I added in the username, date and reply_body manually.  When I wanted to create a thread reply by someone other than the main account (admin) I used "C-M" (short for Christopher-Michael, which is me) in the replier column of the forum_replies table.  But when I viewed the forums all replies to the thread would be shows except for ones that I set the replier as C-M.

Eventually I figured out that the correct username for that user account was C_M. Once I changed C-M to C_M as the replier for any replies from that user they finally appeared in the thread as they should.

I wanted to clarify this because in my Visualizing the Concept section I made it seem that all rows that had the matching 'thread_id' would be added to the array created by the query and then if for some reason the code couldn't find an account in the profile table that the thread_reply row would still be a match but there would be no value for img_url as there was no match in the second table.  Which would result in an empty value for $img_url and therefore a broken img src tag and no avatar being shown.

However, since the query was unable to find a match in the profile table because the 'username' value was not found in the table...it didn't consider the row in the forum_replies table to be a match and therefore the reply was not shown even though it met the criteria of having the appropriate 'thread_id'.

Of course if I had used the actual comment form when logged into the C_M account then the correct username would have been saved into the forum_replies table and it would have shown the reply as expected.

So what this section boils down to is:  If you know that a row from the table that follows the FROM in your query should be matched because it meets the WHERE criteria...check to make sure that two values that are compared after the ON statement truly do match.