I need help with making a browse games page

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
LegoBoy
Forum Newbie
Posts: 9
Joined: Fri Jul 16, 2004 4:02 pm

I need help with making a browse games page

Post 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?
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post 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.
User avatar
LegoBoy
Forum Newbie
Posts: 9
Joined: Fri Jul 16, 2004 4:02 pm

Post 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).
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post 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).
User avatar
LegoBoy
Forum Newbie
Posts: 9
Joined: Fri Jul 16, 2004 4:02 pm

Post 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.
User avatar
johnperkins21
Forum Contributor
Posts: 140
Joined: Mon Oct 27, 2003 4:57 pm

Post 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);

?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.. :)
User avatar
LegoBoy
Forum Newbie
Posts: 9
Joined: Fri Jul 16, 2004 4:02 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)
User avatar
LegoBoy
Forum Newbie
Posts: 9
Joined: Fri Jul 16, 2004 4:02 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
LegoBoy
Forum Newbie
Posts: 9
Joined: Fri Jul 16, 2004 4:02 pm

Post 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
Post Reply