Page 1 of 1

I need help with making a browse games page

Posted: Fri Jul 16, 2004 4:02 pm
by LegoBoy
I'm working on re-writing my web site with php and right now I'm stuck.

I'm making a page to let people brows games I've written by genre. I have three tables that it gets info from, one is a list of genres (contains: id, name), one is a list of games (contains: title, id, ...), and the third is a list for what games fit under what genre (contains: id, game_id, genre_id). I do not have the genre_id under the games table because one game may fit under several genres.

My question is how do I display all of the games that fit under one genre?

Posted: Fri Jul 16, 2004 4:13 pm
by d3ad1ysp0rk
Make it easy on yourself.
genres: id | name
games: id | title | genre_id | etc..

list.php:

Code: Select all

$result = mysql_query("SELECT * FROM games WHERE genre_id = {$_GET['id']}");
while($row = mysql_fetch_array($result)){
echo "<a href="games.php?id=" . $row['id'] . "">" . $row['title'] . "</a><br />";
}
This will provide a simple list of the games. You can add formatting/more information (date added = $row['date_added'] or whatever) as you see fit.

Posted: Fri Jul 16, 2004 4:24 pm
by LegoBoy
quote from my first post
I do not have the genre_id under the games table because one game may fit under several genres.
Right now I do have the genre_id under the games table... I want to seperate it from the games table so that one game can fit under multiple genres.

I also will be doing somthing very similar to this for my news db (so that when a user of the news page signs in to edit/view/add posts he/she will only see his/her posts).

Posted: Fri Jul 16, 2004 4:25 pm
by d3ad1ysp0rk
Is it really that important? Why not go on the actual genre the game is placed under on the developer's site or large gaming site (or even the box).

Posted: Fri Jul 16, 2004 4:28 pm
by LegoBoy
Yes it is important... and I am only posting games on my web site that I made.

I'd probably have to use JOIN or UNION for this... but I don't know how to use these for this purpose (or at all). The PHP/mySQL book I have stinks.

Posted: Fri Jul 16, 2004 6:05 pm
by johnperkins21
Untested, but that should be the easiest way. I'm sure you could do it all in one query, but I don't know that much about doing querys to help you.

Code: Select all

<?php

$query1 = mysql_query("SELECT game_id FROM Genres WHERE genre_id = '$genre'");

while ($result1 = mysql_fetch_row($query1)) {
  $query2 = mysql_query("SELECT * FROM Games WHERE game_id = '$result1[0]' LIMIT 1");
  $result2 = mysql_fetch_row($query2);
  //html display results
}

mysql_free_result($query1);
mysql_free_result($query1);

?>

Posted: Fri Jul 16, 2004 6:23 pm
by feyd
if you set up the genres table so id's were powers of 2, you could combine many (up to 64) genres into 1 field.. just like bitflags passed to some php core functions like preg_replace.. :)

Posted: Fri Jul 16, 2004 8:16 pm
by LegoBoy
Yeah... I figured out how to to this in two querys, but then how would I orginize the games by title? So wouldn't that be much easier to do with one query? Because then I could just have ORDER BY title or ORDER BY date DESC.

EDIT: Feyd, how exactly would that work, and wouldn't that require more work if if I were to add another genre to the list?

Posted: Fri Jul 16, 2004 8:29 pm
by feyd
say for instance you have a genre table looking like this:

Code: Select all

genre_id              genre_name
1                     action
2                     shooter
4                     suspense
8                     platform
16                    stealth
...
512                   puzzle
now, suppose we have a game that's a puzzle/action/platformer. Combining the genre id's for those we get 521 (512 | 8 | 1). It doesn't require much additional work beyond the initial set up.

Alternately, you could use the SET column type, which probably functions much the same (internally)

Posted: Fri Jul 16, 2004 8:41 pm
by LegoBoy
Ok... I see what you mean. Basicly it's binary with 1's and 0's to say if the game fits under the genre. But would'nt I have to write an include file to figure what gentes the game fits under? If so wouldn't that make the php page load slower than just doing this in one query with the genre, game, and classification table?

BTW: 01001001 00100000 01101011 01101110 01101111 01110111 00100000 01100010 01101001 01101110 01100001 01110010 01111001 00100001

Posted: Fri Jul 16, 2004 8:48 pm
by feyd
the combination could happen off of a mutli-select drop down type box. The only thing against SET is it wouldn't use a seperate table for the genre information..

Posted: Sat Jul 17, 2004 1:07 pm
by LegoBoy
after much googleing I belive that I found what I want.

Code: Select all

SELECT DISTINCT games.*, games_class.genre_id
FROM games
LEFT JOIN games_class
  ON games.id = game_class.game_id
WHERE (game_class.genre_id = $genre
  OR games_class.genre_id = NULL)
  AND games.hidden != 0
ORDER BY games.title