Help with my query

[Darkmatter5]

This post actually comes from a PHP post I made a few days ago that has turned into a MYSQL post, so I'll reference the PHP post.

 

http://www.phpfreaks.com/forums/index.php/topic,233725.0.html

 

 

Okay I've got the below query mostly working, but I'm getting a result I don't know how to fix.

 

So I have the following data in the following tables:

 

games

game_idtitle

1Fable 2

2Fallout 3

 

game_systems

game-idsystem-id

16

46

45

414

 

systems

system_idname

5Playstation 3

6Xbox 360

14PC

 

 

Here's the query

SELECT games.*,
GROUP_CONCAT(DISTINCT developers.name ORDER BY developers.name ASC SEPARATOR ', ') AS devnames,
GROUP_CONCAT(DISTINCT genres.genre ORDER BY genres.genre ASC SEPARATOR ', ') AS genres,
GROUP_CONCAT(DISTINCT publishers.name ORDER BY publishers.name ASC SEPARATOR ', ') AS pubnames,
GROUP_CONCAT(DISTINCT systems.name ORDER BY systems.name ASC SEPARATOR ', ') AS sysnames
FROM games
INNER JOIN game_developers
ON game_developers.game_id=games.game_id
INNER JOIN developers
ON developers.developer_id=game_developers.developer_id
INNER JOIN game_genres
ON game_genres.game_id=games.game_id
INNER JOIN genres
ON genres.genre_id=game_genres.genre_id
INNER JOIN game_publishers
ON game_publishers.game_id=games.game_id
INNER JOIN publishers
ON publishers.publisher_id=game_publishers.publisher_id
INNER JOIN game_systems
ON game_systems.game_id=games.game_id
INNER JOIN systems
ON systems.system_id=game_systems.system_id
WHERE game_systems.system_id=6
AND title LIKE 'f%'
GROUP BY games.title
ORDER BY games.title ASC

 

I'm getting this as my result

game_idtitledesccase_imagedevnamesgenrespubnamessysnames

1Fable 2[/td]Lionhead StudioAction Role-PlayingMicrosoft Game StudiosXbox 360

4Fallout 3Bethesda Game StudiosRole-PlayingBethesda Softworks, ZeniMax MediaXbox 360

 

I'm wanting this as my result

game_idtitledesccase_imagedevnamesgenrespubnamessysnames

1Fable 2Lionhead StudioAction Role-PlayingMicrosoft Game StudiosXbox 360

4Fallout 3[td]Bethesda Game StudiosRole-PlayingBethesda Softworks, ZeniMax MediaPC, Playstation 3, Xbox 360

 

This query works almost perfect, but I do need to have the query select all the systems the game is on.  But I also need to make sure I can display games only on specific systems.  By putting the "WHERE game_systems.system_id=x" line in the query I think I'm changing what the query outputs as systems the game is on.  If I remove the line it'll show all the systems, but doesn't let me narrow the search by systems.

 

Thanks!

[ngreenwood6]

I was looking at it and I was thinking that the WHERE clause was what was causing it as well. Why don't you have it make that dynamic? For example, if the user selects xbox 360 and playstation 3 to enter that in the query.

 

$sql = "";
if(empty($sql))
{
$sql = "game_systems.system_id=6";
}
else
{
$sql += $_POST['game_system'];
$sql += $_POST['game_system2'];
}

 

You could then just call that in the query so that it would search by the systems that they selected. This code is untested and just used as an example.

[fenway]

Or using HAVING.