Page 1 of 2
Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Thu Oct 16, 2008 4:24 pm
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?
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Thu Oct 16, 2008 4:41 pm
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.
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Thu Oct 16, 2008 6:05 pm
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?
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Tue Oct 21, 2008 3:13 pm
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).
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Tue Oct 21, 2008 6:43 pm
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!

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...
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Tue Oct 21, 2008 6:50 pm
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.
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Tue Oct 21, 2008 8:26 pm
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...
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Tue Oct 21, 2008 8:34 pm
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!
...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)
Posted: Wed Oct 22, 2008 2:52 am
by onion2k
The 1 is the value returned from print_r(). You really ought to be doing "echo '<div>'; print_r($record); echo '</div>';".
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Wed Oct 22, 2008 12:02 pm
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!

Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Wed Oct 22, 2008 12:40 pm
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>
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Wed Oct 22, 2008 1:09 pm
by JAB Creations
...how do I echo the tags without using print_r though?

Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Wed Oct 22, 2008 1:15 pm
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>';}
?>
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Wed Oct 22, 2008 1:33 pm
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>';}
Re: Help with PHP/MySQL Left Join? (might be a double left join)
Posted: Wed Oct 22, 2008 1:54 pm
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.
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?