Help with PHP/MySQL Left Join? (might be a double left join)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
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)

Post by JAB Creations »

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?
Attachments
build_3.zip
(6.58 KiB) Downloaded 50 times
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Help with PHP/MySQL Left Join? (might be a double left join)

Post by onion2k »

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` ASC
Just to explain that IF() statement because I imagine if you've not used a left join you won't be into SQL programming...

Code: 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.
 
You could just use `category`.`name` if you were happy with having empty strings returned in the results where categories aren't found.
User avatar
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)

Post by aceconcepts »

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):

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'");
 
Is this what you're looking for?
User avatar
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)

Post by JAB Creations »

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).
User avatar
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)

Post by JAB Creations »

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! :mrgreen: 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...
User avatar
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)

Post by JAB Creations »

This seems to do something (thanks to aceconcepts for coding that bit)...

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);
?>
...I'm going to mess with this a little more after the phone call, it's my best lead at the moment.
User avatar
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)

Post by JAB Creations »

I get the following output...
Array ( [id] => 1 [category_id] => 1 [game_id] => 1 )
...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...
User avatar
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)

Post by JAB Creations »

PHP

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>';}
?>
Output
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
This looks like what I wanted to see! :mrgreen:

...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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Help with PHP/MySQL Left Join? (might be a double left join)

Post by onion2k »

The 1 is the value returned from print_r(). You really ought to be doing "echo '<div>'; print_r($record); echo '</div>';".
User avatar
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)

Post by JAB Creations »

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...

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>';
...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! :mrgreen:
User avatar
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)

Post by JAB Creations »

Success!

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>';}
?>
 
XHTML Output

Code: Select all

<div>Adventure</div><div>Sports</div>
User avatar
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)

Post by JAB Creations »

...how do I echo the tags without using print_r though? :|
User avatar
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)

Post by maliskoleather »

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>';}
?>
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Help with PHP/MySQL Left Join? (might be a double left join)

Post by VladSun »

Why we should not use

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 )
 
We should use the strict form:

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
User avatar
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)

Post by JAB Creations »

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. :mrgreen:

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>, ';}
So how could I approach the situation to !echo ', ' on the last while loop?
Post Reply