Subscribe to Tutorial Feed

Flash and PHP Bible

The Flash and PHP Bible has been released! The book can be found on Amazon or wherever fine books are sold in your area.

The Flash and PHP Bible has a forum for quick support.

Scriptplayground » tutorials » php » Joining Tables in MySQL

Joining Tables in MySQL

Basics of joining tables in MySQL

This tutorial is from the now defunct SPLounge from our very own Jhecht. However the information contained within this tutorial is very much still relevant.

Almost every web server has a database, if not, well, why are you reading this? On the other side, those databases make editing and running your site a lot easier. However, those newer to the trade of programming are often a bit confused with MySQL. Let's assume something right now, you've got 2 tables; posts and users. Posts has the following fields:

post_id int unique not null auto_increment,
post_title varchar(255) not null,
post_text text not null,
post_userid int not null
post_date bigint not null.

And that users has these fields:

user_id int unique not null auto_increment
username varchar(50) not null
user_password varchar(32) not null

Now that we have those assumptions, Let's assume that the users table has a few rows in it, let's say the users are Bob, Joe, and Fred. Their password hashes don't matter, and neither do their user_id's. At least not right now.

So lets assume you want to take top 5 most recent posts from the database. How would you do that? If something like this came to mind, then good for you!

<?php
$msa = mysql_connect('localhost','user_name','password') or die(mysql_error());
//Stores the connection resource. Some MySQL functions require it to run properly.
@mysql_select_db('db') or die(mysql_error());
//select a database to work on. If that should fail, quit compiling code.

$sql = "SELECT * FROM posts ORDER BY post_date DESC LIMIT 5";
$ans = mysql_query($sql,$msa) or die(mysql_error());
//Run the query held in the string $sql to mysql, using the connection resource of $msa.

while($row = mysql_fetch_assoc($ans)) {
  // Your HTML or other things here...
}
@mysql_free_result($ans);
?>

Thats good, except for one serious issue with it. Let say you want to display who the author was, using something like echo "<b>Posted By:</b>".$row['post_userid']. The output you'll get from that will be : Posted By: 1. I'm sure you don't want people to be referred to by numbers, and if you do then, well, why are you reading this? But how would you go about getting the name from the table which holds the value for it. We now that post_userid is the value from the user table's column named user_id. So the row exists in there, but we just can't seem to figure out how to do this. But that one ambitious person in the back says "I GOT IT!!!!!" and he gives us something like this:

<?php
 $msa = mysql_connect('localhost','user_name','password') or die(mysql_error());
  //Stores the connection resource. Some MySQL functions require it to run properly.
  @mysql_select_db('db') or die(mysql_error());
  //select a database to work on. If that should fail, quit compiling code.

  $sql = "SELECT * FROM posts ORDER BY post_date DESC LIMIT 5";
  $ans = mysql_query($sql,$msa) or die(mysql_error());
  //Run the query held in the string $sql to mysql, using the connection resource of $msa.

  while($row = mysql_fetch_assoc($ans)){
            // Your HTML or other things here...
    $sql2 = "SELECT username FROM users WHERE user_id = ".$row['post_userid'];
     $ans2= mysql_query($sql2, $msa) or die(mysql_error());
     $user_id = mysql_result($ans2,0, "user_id");
    //More of your HTML here.

   }
  @mysql_free_result($ans);
?>

Mind you, I'm not saying you couldn't do that, it's just if you think about it, you're wasting all of the SQL time when there was a very easy way to do this from the beginning. Tables can be JOINED on. Let's take our ambitious person's example and make it a bit more streamlined. He got the idea about selecting from the users table, where the row's id was equal to our post user's, but he was just going about it a bit wrong.

<?php
$msa = mysql_connect('localhost','user_name','password') or die(mysql_error());
//Stores the connection resource. Some MySQL functions require it to run properly.
@mysql_select_db('db') or die(mysql_error());

//select a database to work on. If that should fail, quit compiling code.
$sql = "SELECT p.*,u.username 
  FROM posts p,users u 
  WHERE u.user_id = p.post_userid 
  ORDER BY post_date 
  DESC LIMIT 5";

$ans = mysql_query($sql,$msa) or die(mysql_error());
//Run the query held in the string $sql to mysql, using the connection resource of $msa.
while($row = mysql_fetch_assoc($ans)) {
  //User's name is now held in $row['username'];
  //Your HTML here.
}
@mysql_free_result($ans);
?>

Ok, ok, before you freak out. Let me explain what i changed in the SQL. MySQL, being the clever little devil that it is, is just like a close friend. Let's say you've got a friend named Onomatopoeia. You certainly won't call him Onomatopoeia all the time, you'd make a nickname for him. Well MySQL doesn't like the name posts, so we just give it the nickname of p for MySQL because MySQL can't decide on its own(after all, it is just a database). Now that comma after 'posts p' just says we're selecting from more than one table, in this case a table called users, which we'll let MySQL nick-name to 'u'. Remember, every time you use p. or u. you're referring to the table posts or users. After the "." can come any column held in that table.

Remember this, however: If we just said SELECT p.*,u.username FROM posts p,users u ORDER BY post_date DESC LIMIT 5, we would get something like this:
Post Title: Title 1, Posted By: Bob
Post Title: Title 1, Posted By: Joe
Post Title: Title 1, Posted By: Fred.
I VERY highly doubt that Bob, Joe, and Fred all posted the same topic. You see, by adding the WHERE clause, we tell MySQL to select from users where the user_id column is the same value as the post_userid column, therefore giving us the right result of(let's say)
Post Title: Title 1, Posted By: Fred.

So we just learned something, we can JOIN tables. What the syntax we just used was was called an "INNER JOIN". Think of it as "Select from this table this value, if and only if a row exists." There is another way to use inner join, which i prefer myself. All you have to do is change the $sql variable to something like this:

...
$sql = "SELECT p.*,u.username FROM posts p
    INNER JOIN users u ON u.user_id = p.post_userid";
...

Again, let me explain before you have a heart attack and i get sued. Basically the first line says "select everything posts has, and the username from users FROM posts(who i will nick-name ) p, BUT let's select the username from the users table ON THE CONDITION that the user's id is equal to the post_userid's." Make a bit more sense? However, let's say for some reason Fred here gets banned, and thusly deleted from our users table. Well, we still like Fred's post, but his user_id doesn't exist anymore, so MySQL won't allow for that row to be selected. This is where LEFT and RIGHT JOIN come in handy. Let's modify our query assuming we remember that fred was deleted.

...
$sql = "SELECT p.*, u.username FROM posts p 
    LEFT JOIN users u ON u.user_id = p.post_userid ORDER BY p.post_date DESC LIMIT 5";
...

while($row = mysql_fetc_assoc($ans)){
 $user = ($row['username'] != "NULL" or !is_null($row['username']) ) ? $row['username'] : "Guest";
 // If the row's username does not have the value of 'NULL' and the key itself isn't null, $user is 
 // set to the row's username, otherwise it is set to Guest.

}

Think of LEFT JOIN as this: we put two tables, in this case posts and users, side by side. Posts goes on the left because its the table after the FROM in the SQL, and users is goes to the right. LEFT JOIN takes the table to right, in this case users, and kind of meshes it and the table to the left together. RIGHT JOIN does pretty much the opposite, it takes posts and joins it to the table on the right, in this case users. If a row in posts didn't exist that we thought did, using RIGHT JOIN would work instead, but we know that if the post doesn't exists in the post database, it won't exist anywhere else, but rows from users can disappear and still have things point back to them we use LEFT JOIN.

Now, hopefully, you get a bit more about table joining in MySQL. Questions/ Comments, post em here! Thanks everyone for taking the time to read this tutorial.

Follow Scriptplayground on Twitter (@scriptplay)

Comments: Joining Tables in MySQL

No Comments have been made. Wanna be the first?
Add a comment
The ability to add new comments has been disabled in this archived version.

Loading
Main | Tutorials | Articles