In this tutorial will be going through it as though we were about to create a browser game that involves units, players, stats, etc.
How Does an Inner Join Work?
Before we get to far into this and start making our own tables you need to first know how the tables are going to do what they do. We are going to be using a join to connect two tables, this join is called an inner join, with this join type we will be able to link the tables together on a similar column. In this case it will be an ID.
In this game we are going to be needing some players and some units that they will be able to control. So, from within the units table we will have an id that will match the players id. This will allow us to merge the tables on a specific column.
What Will the Inner Join Tables Look Like?
Before we can get to the inner join table we will first need the players table. This will have a name, password and id. The id will have to be auto incremental in order to keep track of the id’s and keep them all unique.
Now that we have our users table we will need to merge them onto the units table to keep track of what units the player owns. The inner join will be performed on the user_id on the table below to the id on the table above.
Once the tables are merged together you will get the following results.
As you can see the table now consists of multiple players and the units. This method of table construction can be very powerful since you can then do another inner join from the units table to the singular unit table. This will allow you to link the individual health, strength, etc of a unit to the player.
SQL Query of an Inner Join
The sql query looks a little odd when you first look at it but once you go through what is going on you will begin to understand it a lot better.
1
2
3
4
5
| SELECT
p.id p_id, p.name p_name,
u.user_id u_user_id, u.unit_id u_unit_id, u.id u_id
FROM players p
INNER JOIN units u ON p_id = u_user_id |
You can also select all of the users based on the user id. This will involve using the previous statement but it will just require you to add the “WHERE” statement. Your code will now look like this.
1
2
3
4
5
6
| SELECT
p.id p_id, p.name p_name,
u.user_id u_user_id, u.unit_id u_unit_id, u.id u_id
FROM players p
INNER JOIN units u ON p_id = u_user_id
WHERE u_id = '54' |
This is now where the true one to many statement happens. one player to many units. And if you actually work on a game like this you can expand onwards and use a one to one unit or many to many using linking tables.
Query One To Many Using PHP
In order to query a one to many using PHP will just need to use the following code.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| //store the sql query in the variable $sql
$sql = "SELECT
p.id p_id, p.name p_name,
u.user_id u_user_id, u.unit_id u_unit_id, u.id u_id
FROM players p
INNER JOIN units u ON p_id = u_user_id
WHERE u_id = '2'";
//query the database and store the results into $t1. If query fails display the error and kill the page.
$t1 = mysql_query($sql)or die(mysql_error());
//This will loop through the results and display them for you
while($row = mysql_fetch_array($t1)){
echo 'unit id: '.$row['u_unit_id'].'<br>';
} |
Additional Notes:
When you are creating the tables keep in mind that every id field should be stored as an integer and should match the limit. If you expect to have a large game down the road make sure that all of your id’s have a length of 11 or 15. That number is up to you but it is best to keep them all at the same length.