[RESOLVED] Equi-Joined mysql tables

[Xaero]

Hey guys, I know this is more of a mysql question than a php question, but here goes...

I'm trying to pull data from separate tables (all with unique id's) so that I can output it all in one page.

For example :
table 1
id
name
developer
genre

table 2
id
developer

table 3
id
genre

The first table is the root with the name of the item and then reference numbers for the developer and genre, then the 2nd and 3rd tables have their own id's along with the developer name and genre name respectively.

Here's the code I'm using (the gameid comes from a url code which is why I'm using the GET global):

    $gameid = $_GET['gameid'];
    $selectgame = sprintf("SELECT * FROM game g, developer d, genre r WHERE g.id='%s' AND g.developer=d.id AND g.genre=r.id",mysql_real_escape_string($gameid));
    $gameselect = mysql_query($selectgame, $db_connection);
    while ($row = mysql_fetch_assoc($gameselect)) {

There's something wrong just in that bit because it doesn't even get as far as retrieving any values from the rows selected and brings up loads of errors on my page due to it. The error comes in at the last line saying fetch_assoc's supplied arguement is invalid.

I'm pretty new to joins of any kind in mysql queries so I've probably just missed something 😛

[Rodders]

I haven't delved into your code yet so can't offer a solution, but I'll offer some debugging tips if I may.

1. Try running the SQL statement in phpmyadmin first

2. If it works how you want, then the problem is more likely to be in your PHP code so first of all check what the SQL looks like in your PHP:

   echo "<p>$selectgame</p>";
   [*]If that SQL looks correct (why not copy, paste and test it?) then it is a coding error.
   [*]Post your findings in this thread so others can see what you've managed to narrow it down to.
   [/LIST]

[Xaero]

Thanks for the debugging advice Rodders, just running it through phpmyadmin's sql console made me realise what I had wrong (doh stupid overlooked reason) but now that I'm getting into calling off the data from the rows how do I refer to them?

With a normal query on 1 table it was as simple as $variable = $row['name']; but then what if I have 2 fields with the same name? At the moment it doesn't recognise me calling g.information or r.genre or d.name so how does it assign names?

Thanks

[Rodders]

Glad you solved that initial problem. Debugging is as much of an art as programming itself, and those tips are ones that I use every day in my regular (non-PHP) job.

You ask a good question about ambiguous column names. It's been a while since I tried to do something similar so I'm gonna need to do some thinking. I believe my normal approach is to force the SQL to use different names, e.g.:

SELECT a.name AS CustomerName, b.name AS EmployeeName
FROM Customer a, Employee b

You would then be able to use

$row['CustomerName'];

An alternative approach might be possible, and I'm going to look it up now.

[Xaero]

Thanks for that, I'm fairly new to php programming and haven't done much other programming in quite a while either so I'm a bit out of practice with debugging but I'll get back in the habit soon enough 🙂
The syntax doesn't quite seem to work for what you suggested there, so my project has come to a sorta grinding halt, all I can find are pages on the pure mysql side showing me how to do equi-joins and not how to implement them afterwards sigh

[Rodders]

OK, so the problem you were having was where you had two field with the same name.

My suggestion is that you change your SQL so that it gives those two fields a different name temporarily. It's called ALIAS.

$selectgame = sprintf("SELECT g.id as GameID, d.id as DeveloperID FROM game g, developer d, genre r WHERE g.id='%s' AND g.developer=d.id AND g.genre=r.id",mysql_real_escape_string($gameid));

$gameselect = mysql_query($selectgame, $db_connection);

while ($row = mysql_fetch_assoc($gameselect)) {
    echo "</br>Game ID: " . $row['GameID'] . " - DeveloperID: " . $row['DeveloperID'];
}

Does that make any sense? If you try it, what errors do you get if any?

[Xaero]

That worked in the end, I think I was just being too lazy and only testing a couple of values 😛 thanks for the tip on aliasing them man 🙂 problems solved! Hurrah!