Help with PHP/MySQL Left Join? (might be a double left join)
Moderator: General Moderators
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Help with PHP/MySQL Left Join? (might be a double left join)
Attached is my current project for a job interview process a few people have already been helping me out with. Posting a new thread here because I didn't want to keep posting code in the General Discussion forum (my bad).
Any way I think what I'm trying to do right now if a double left join?
Below is the table setup with the relevant data listed...
category
-> id
-> name
games
-> id
game_category
-> id
-> category_id
-> game_id
I haven't done a left join before and the DB setup seems to only further complicate things.
1.) I start with a game ID.
2.) I want to look the game ID up in the table game_category->game_id.
3.) Each row in step two will be associated with a category game_category=>category_id.
4.) I then have to look in the category->id to find the associated category->name.
5.) I need to return the category->name for each instance.
I think I've described that accurately!
My brain is so twisted right now I have no clue what to write to end my initial post here. It doesn't seem like a requirement to of the tests though for a tertiary relational database (or whatever the correct wording is) it's something I want and will need to learn. Where do I start please?
Any way I think what I'm trying to do right now if a double left join?
Below is the table setup with the relevant data listed...
category
-> id
-> name
games
-> id
game_category
-> id
-> category_id
-> game_id
I haven't done a left join before and the DB setup seems to only further complicate things.
1.) I start with a game ID.
2.) I want to look the game ID up in the table game_category->game_id.
3.) Each row in step two will be associated with a category game_category=>category_id.
4.) I then have to look in the category->id to find the associated category->name.
5.) I need to return the category->name for each instance.
I think I've described that accurately!
My brain is so twisted right now I have no clue what to write to end my initial post here. It doesn't seem like a requirement to of the tests though for a tertiary relational database (or whatever the correct wording is) it's something I want and will need to learn. Where do I start please?
- Attachments
-
- build_3.zip
- (6.58 KiB) Downloaded 50 times
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Code: Select all
SELECT
IF(`category`.`name`IS NULL, CONCAT_WS(' - ','Unknown',`category`.`id`), `category`.`name`) AS name
FROM `game_category`
LEFT JOIN `category` ON `category`.`id` = `game_category`.`category_id`
WHERE `game_category`.`game_id` = 1
ORDER BY `category`.`name` ASCCode: Select all
IF( #Opens an IF statement
`category`.`name`IS NULL, #Check to see if the category name IS NOT found
CONCAT_WS(' - ','Unknown',`category`.`id`), #Returned if the check is true ... CONCAT_WS() is like PHP's implode.
`category`.`name`) #Returned if the check is false
AS name #Alias the result of the IF() to a 'column' called name.
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Hi,
A LEFT join will return all rows from the LEFT table in a query whether or not matching data is found in the RIGHT table. A LEFT join is used when, in your scenario, you want to output ALL game names whether they have a category or not.
This is one way you could do it using an INNER JOIN (an INNER JOIN returns all data where there is a match in both tables - so it will not return games that do not have a category assigned):
Is this what you're looking for?
A LEFT join will return all rows from the LEFT table in a query whether or not matching data is found in the RIGHT table. A LEFT join is used when, in your scenario, you want to output ALL game names whether they have a category or not.
This is one way you could do it using an INNER JOIN (an INNER JOIN returns all data where there is a match in both tables - so it will not return games that do not have a category assigned):
Code: Select all
$query=mysql_query("SELECT * FROM game_category INNER JOIN category ON game_category.category_id=category.id WHERE game_category.game_id='$game_id'");
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Help with PHP/MySQL Left Join? (might be a double left join)
I need to set a specific goal to have a basis to start from so I'd like to start approaching it from the following point...
I'd like to list all the "tags" in the associative table (the other tables are "games" and "tags") for a single game. I think (not certain) that the only thing the left join would be neccesary for is to replace the tag "id" with the tag name associated with the id in the tag table.
I'm going to try to copy what I can find and post my results once I either get totally stuck or get something working (more or less).
I'd like to list all the "tags" in the associative table (the other tables are "games" and "tags") for a single game. I think (not certain) that the only thing the left join would be neccesary for is to replace the tag "id" with the tag name associated with the id in the tag table.
I'm going to try to copy what I can find and post my results once I either get totally stuck or get something working (more or less).
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Help with PHP/MySQL Left Join? (might be a double left join)
I'm trying to very carefully construct MySQL like language along with a little color coding to produce a request for sample code that I can understand because I'm looking at all these examples without a database example. If I can't see the database and it's tables I'm not really sure what is what.
So I've made the gamer database table bold and gave the three tables RGB colors. From what I've read there are different types of joins so I'm trying to grasp something I understand by constructing a working example.
Here is my database layout...
gamer
--category
----id
----name
--games
----id
----name
--game_category
----id
----category_id
----game_id
Goal: Display all tags for "Angry Gopher Golfers"
Angry Gopher Golfers is...
gamer->games->$row['id']='3'
Angry Gopher Golfers ID = "3"
Therefore in...
gamer->game_category
I want to look for * WHERE game_id == "3" as well as the $row['category_id'] on that row.
THEN I want to look up the $row['category_id'] on...
gamer->category
...and instead of echoing the ID I want to select the column "name" (gamer->category->name)
I'm trying some stuff out and I'll post it in a little bit...I seem to do better when I post it, it gives me that extra umph!
Gotta call the company president in a little bit and then I'll give it another crack. The MySQL dump giving me an error and I'll post more as soon as I can...
So I've made the gamer database table bold and gave the three tables RGB colors. From what I've read there are different types of joins so I'm trying to grasp something I understand by constructing a working example.
Here is my database layout...
gamer
--category
----id
----name
--games
----id
----name
--game_category
----id
----category_id
----game_id
Goal: Display all tags for "Angry Gopher Golfers"
Angry Gopher Golfers is...
gamer->games->$row['id']='3'
Angry Gopher Golfers ID = "3"
Therefore in...
gamer->game_category
I want to look for * WHERE game_id == "3" as well as the $row['category_id'] on that row.
THEN I want to look up the $row['category_id'] on...
gamer->category
...and instead of echoing the ID I want to select the column "name" (gamer->category->name)
I'm trying some stuff out and I'll post it in a little bit...I seem to do better when I post it, it gives me that extra umph!
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Help with PHP/MySQL Left Join? (might be a double left join)
This seems to do something (thanks to aceconcepts for coding that bit)...
...I'm going to mess with this a little more after the phone call, it's my best lead at the moment.
Code: Select all
<?php
include("_0_header_01_mysql.php");
$sql = 'SELECT * FROM game_category INNER JOIN category ON game_category.category_id=category.id WHERE game_category.game_id=".$game_id."';
$result = mysql_query($join1, $db);
$record = mysql_fetch_assoc($result);
print_r($record);
?>- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Help with PHP/MySQL Left Join? (might be a double left join)
I get the following output...
...I'm not sure what that means though I presume 1 means more so "true" than anything else? I'll work on adapting a for loop for this...Array ( [id] => 1 [category_id] => 1 [game_id] => 1 )
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Help with PHP/MySQL Left Join? (might be a double left join)
PHP
Output

...what are all the 1's? Why did the initial (single line from before the while) spit out all 1's where this has not?
Code: Select all
<?php
include("_0_header_01_mysql.php");
$join1 = "SELECT * FROM game_category# WHERE category.id = category.name";
$result = mysql_query($join1, $db);
while ($record = mysql_fetch_assoc ($result)) {echo '<div>'.print_r ($record).'</div>';}
?>This looks like what I wanted to see!Array ( [id] => 2 [category_id] => 2 [game_id] => 1 )
1
Array ( [id] => 3 [category_id] => 6 [game_id] => 1 )
1
Array ( [id] => 4 [category_id] => 8 [game_id] => 2 )
1
Array ( [id] => 5 [category_id] => 2 [game_id] => 2 )
1
Array ( [id] => 6 [category_id] => 5 [game_id] => 4 )
1
Array ( [id] => 7 [category_id] => 2 [game_id] => 4 )
1
Array ( [id] => 8 [category_id] => 1 [game_id] => 4 )
1
Array ( [id] => 9 [category_id] => 2 [game_id] => 3 )
1
Array ( [id] => 10 [category_id] => 7 [game_id] => 3 )
1
Array ( [id] => 11 [category_id] => 7 [game_id] => 5 )
1
Array ( [id] => 83 [category_id] => 4 [game_id] => 85 )
1
Array ( [id] => 82 [category_id] => 3 [game_id] => 85 )
1
Array ( [id] => 81 [category_id] => 2 [game_id] => 85 )
1
Array ( [id] => 80 [category_id] => 1 [game_id] => 85 )
1
Array ( [id] => 79 [category_id] => 7 [game_id] => 84 )
1
Array ( [id] => 75 [category_id] => 3 [game_id] => 84 )
1
Array ( [id] => 76 [category_id] => 4 [game_id] => 84 )
1
Array ( [id] => 77 [category_id] => 5 [game_id] => 84 )
1
Array ( [id] => 78 [category_id] => 6 [game_id] => 84 )
1
Array ( [id] => 86 [category_id] => 7 [game_id] => 85 )
1
Array ( [id] => 85 [category_id] => 6 [game_id] => 85 )
1
Array ( [id] => 84 [category_id] => 5 [game_id] => 85 )
1
Array ( [id] => 74 [category_id] => 2 [game_id] => 84 )
1
Array ( [id] => 73 [category_id] => 1 [game_id] => 84 )
1
...what are all the 1's? Why did the initial (single line from before the while) spit out all 1's where this has not?
Re: Help with PHP/MySQL Left Join? (might be a double left join)
The 1 is the value returned from print_r(). You really ought to be doing "echo '<div>'; print_r($record); echo '</div>';".
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Woot! I think I see the connection here!
Now I need to figure out how to access various parts of the array...
Here is an example of something I just tried...
...nothing echos out.
Once I get this working I'm going to revise the private message system for my site's project to use all of this!
Now I need to figure out how to access various parts of the array...
Here is an example of something I just tried...
Code: Select all
$join2 = "SELECT * FROM game_category INNER JOIN category ON game_category.category_id=category.id WHERE game_category.game_id=3";
$result = mysql_query($join2, $db);
echo '<div>rec= '.$record[0][name].'</div>';Once I get this working I'm going to revise the private message system for my site's project to use all of this!
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Success!
PHP
XHTML Output
PHP
Code: Select all
<?php
include("_0_header_01_mysql.php");
$join1 = "SELECT * FROM game_category# WHERE category.id = category.name";
$join2 = "SELECT * FROM game_category INNER JOIN category ON game_category.category_id=category.id WHERE game_category.game_id=3";
$result = mysql_query($join2, $db);
while ($record = mysql_fetch_assoc ($result)) {echo '<div>'; print_r($record[name]); echo '</div>';}
?>
Code: Select all
<div>Adventure</div><div>Sports</div>- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Help with PHP/MySQL Left Join? (might be a double left join)
...how do I echo the tags without using print_r though? 
- maliskoleather
- Forum Contributor
- Posts: 155
- Joined: Tue May 15, 2007 2:19 am
- Contact:
Re: Help with PHP/MySQL Left Join? (might be a double left join)
just like you were doing earlier 
Code: Select all
<?php
include("_0_header_01_mysql.php");
$join1 = "SELECT * FROM game_category# WHERE category.id = category.name";
$join2 = "SELECT * FROM game_category INNER JOIN category ON game_category.category_id=category.id WHERE game_category.game_id=3";
$result = mysql_query($join2, $db);
while ($record = mysql_fetch_assoc ($result)) {echo '<div>'.$record[name].'</div>';}
?>
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Why we should not use
?
Because
We should use the strict form:
Code: Select all
{echo '<div>'.$record[name].'</div>';}Because
Code: Select all
define('name', 'global-var-like');
....
{echo '<div>'.$record[name].'</div>';}
// WTH?!? (see error log files )
Code: Select all
{echo '<div>'.$record['name'].'</div>';}There are 10 types of people in this world, those who understand binary and those who don't
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Thanks guys, I still haven't gotten completely comfortable with arrays though practice makes perfect. I've implemented this in to the test project that helped me land the job. I'm going to try to do the reverse and use the tag as an HTTP query, and use that to look up other games on the page for the tag. I don't have to do it for work though it should help familiarize this a bit more for me. Plus I plan on writing my own blog software early next year and this should work perfectly for the tag system. 
Turning the divisible elements in to anchors I realized I am not sure how to determine when the while function ends. This is important as I'd like to not put a comma after the last tag as it would be grammatically incorrect.
So how could I approach the situation to !echo ', ' on the last while loop?
Turning the divisible elements in to anchors I realized I am not sure how to determine when the while function ends. This is important as I'd like to not put a comma after the last tag as it would be grammatically incorrect.
Code: Select all
while ($record = mysql_fetch_assoc ($result)) {echo '<a href="index.php?">'.$record['name'].'</a>, ';}