Getting data from joined databases

Arkane

Hey guys,

I'm struggling with MySQL. I pretty much only have the basics of it understood, and a little flimsily at that, but I'm still trying to do more and hopefully jumpstart my knowledge.

I have a games table with bits of data - id, game tile, image etc, but only one thing for each so I've never really had any hassles with it. Now though I'm trying to add reviews to it, which will not be limited, so I'm trying to use a second table for it. This has a unique id, gameid which matches the first tables id, and the review.

$id=mysql_result($result,0,"id");
$gametitle=stripslashes(mysql_result($result,0,"title"));

I've always used this to get the data I want, but when I use a join in my query, instead this stops working. Obviously I have it wrong.

$query="SELECT * FROM games JOIN reviews ON games.id=reviews.gameid WHERE id='$id'";

This is my query line, which would normally just be select * from games WHERE id='$id'

I want to setup the page to automatically list the information, and give one of the reviews, chosen at random. Can anyone tell me what I should be doing differently?

paulnaj

Hi,

Assuming you already have a value for $id ...

<?php
$result = mysql_query('
SELECT * 
FROM games 
WHERE id = "'.$id.'"
');
$game = mysql_fetch_assoc($result);
$game_title = $game['title'];

$result = mysql_query('
SELECT * 
FROM reviews 
WHERE reviews.gameid = "'.$id.'" 
ORDER BY RAND()
LIMIT 1
');
$review = mysql_fetch_assoc($result);
$review_text = $review['text'];
?>

... is probably the most straightforward way to go.

johanafm

A probable reason your query fails is that you have a column called id in both tables, hence

WHERE id=

is ambigous unless id is qualified by its table name

WHERE games.id=

And unless id is a string type, you should drop the string delimiters around $id.

Finally, the JOIN should (most likely) be changed from JOIN (implies LEFT INNER JOIN) to LEFT JOIN (implies LEFT OUTER JOIN), since otherwise you wouldn't get any results for games that has no reviews as of yet.