How to put a value in multiple categories

Question

After I solved the problem to view more than 1 game category under the game_category field in my database, I'm facing another issue. My website decides which game goes in which category by the $game_category variable, but now, I inserted a game with 4 categories, so instead of "action" under game_category record, I have "action,space,strategy,shooting", so the game is sitting nowhere...

This is a part of my code that shows the logic of viewing results by game_category:

if(isset($_GET['genre'])){

 $game_category = $_GET['genre'];

 $select_games = "SELECT * FROM games WHERE game_category='$game_category' ORDER BY game_name ASC";

How can I make a system to read a record with more than one category?

Any ideas? :(

EDIT: Since there is not enough information in my question I will post here 2 php files that involved in the problem: 1. "insert_games.php" - Used to upload new games via admin panel (works perfectly). 2. "categories.php" - Used to view games on the right category they are belong to.

This is insert_games.php:

<?php
session_start();

if(!isset($_SESSION['user_name'])){

header("location:login.php");

}
else {
?>
<?php
include("../includes/connect.php");

if(isset($_POST['submit'])){

$game_name = $_POST['game_name'];
$game_category = implode(",",$_POST['game_category']);
$game_keywords = $_POST['game_keywords'];
$game_image = $_FILES['game_image']['name'];
$image_tmp = $_FILES['game_image']['tmp_name'];
$game_code = $_FILES['game_code']['name'];
$code_tmp = $_FILES['game_code']['tmp_name'];
$game_file = $_FILES['game_file']['name'];
$file_tmp = $_FILES['game_file']['tmp_name'];
$game_desc = $_POST['game_desc'];

if($game_name=='' or $game_category=='' or $game_keywords=='' or $game_image=='' or $game_code=='' or $game_file==''){

echo "<script>alert('Please enter all the fields below!')</script>";

exit();

}
else {

 $path = "../games/$game_name";

 mkdir("$path", 0777);

 move_uploaded_file($image_tmp,"../images/games_images/$game_image");

 move_uploaded_file($code_tmp,"$path/$game_code");

 move_uploaded_file($file_tmp,"$path/$game_file");

 $insert_query = "insert into games (game_name,game_category,game_keywords,game_image,game_code,game_file,game_desc) values ('$game_name','$game_category','$game_keywords','$game_image','$game_code','$game_file','$game_desc')";

 if($game_category == '3d'){

 $three_d_status = 'checked';

 }
 if($game_category == 'action'){

 $action_status = 'checked';

 }
 if($game_category == 'adventure'){

 $adventure_status = 'checked';

 }
 if($game_category == 'arcade'){

 $arcade_status = 'checked';

 }
 if($game_category == 'brain'){

 $brain_status = 'checked';

 }
 if($game_category == 'cards'){

 $cards_status = 'checked';

 }
 if($game_category == 'destruction'){

 $destruction_status = 'checked';

 }
 if($game_category == 'education'){

 $education_status = 'checked';

 }
 if($game_category == 'extreme'){

 $extreme_status = 'checked';

 }
 if($game_category == 'fighting'){

 $fighting_status = 'checked';

 }
 if($game_category == 'flying'){

 $flying_status = 'checked';

 }
 if($game_category == 'girls'){

 $girls_status = 'checked';

 }
 if($game_category == 'holidays'){

 $holidays_status = 'checked';

 }
 if($game_category == 'hidden_objects'){

 $hidden_objects_status = 'checked';

 }
 if($game_category == 'motocross'){

 $motocross_status = 'checked';

 }
 if($game_category == 'multiplayer'){

 $multiplayer_status = 'checked';

 }
 if($game_category == 'new'){

 $new_status = 'checked';

 }
 if($game_category == 'puzzle'){

 $puzzle_status = 'checked';

 }
 if($game_category == 'racing'){

 $racing_status = 'checked';

 }
 if($game_category == 'fps'){

 $fps_status = 'checked';

 }
 if($game_category == 'space'){

 $space_status = 'checked';

 }
 if($game_category == 'sports'){

 $sports_status = 'checked';

 }
 if($game_category == 'strategy'){

 $strategy_status = 'checked';

 }
 if($game_category == 'stunt'){

 $stunt_status = 'checked';

 }
 if($game_category == 'top_rated'){

 $top_rated_status = 'checked';

 }
 if($game_category == 'words'){

 $words_status = 'checked';

 }
 if($game_category == 'zombies'){

 $zombies_status = 'checked';

 }

 if(mysql_query($insert_query)){

 echo "<script>alert('The Game Uploaded Successfully!')</script>";

 echo "<script>window.open('view_games.php','_self')</script>";

 }

}

}

?>

<?php } ?>

<!DOCTYPE HTML>
<html>
<head>
<link href="css/insert_game.css" rel="stylesheet" type="text/css">
</head>
<body>

<form method="post" id="insert_form" action="insert_games.php" enctype="multipart/form-data">

<table id="insert_games_table" width="680" border="1" align="center">

<tr>
 <td id="insert_games_table_title" colspan="2" align="center">Insert New Game</td>
</tr>

<tr>
 <td id="insert_cat_name">Game name:</td>
 <td><input type="text" name="game_name" size="80"></td>
</tr>

<tr>
 <td id="insert_cat_name">Game category:</td>
 <td id="insert_cat_select"><input class="checkbox" type="checkbox" name="game_category[]" value="3d"<?php print $three_d_status; ?>/><span>3D</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="action"<?php print $action_status; ?>/><span>Action</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="adventure"<?php print $adventure_status; ?>/><span>Adventure</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="arcade"<?php print $arcade_status; ?>/><span>Arcade</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="brain"<?php print $brain_status; ?>/><span>Brain</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="cards"<?php print $cards_status; ?>/><span>Cards</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="destruction"<?php print $destruction_status; ?>/><span>Destruction</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="education"<?php print $education_status; ?>/><span>Education</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="extreme"<?php print $extreme_status; ?>/><span>Extreme</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="fighting"<?php print $fighting_status; ?>/><span>Fighting</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="flying"<?php print $flying_status; ?>/><span>Flying</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="girls"<?php print $girls_status; ?>/><span>Girls</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="holidays"<?php print $holidays_status; ?>/><span>Holidays</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="hidden_objects"<?php print $hidden_objects_status; ?>/><span>Hidden Objects</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="motocross"<?php print $motocross_status; ?>/><span>Motocross</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="multiplayer"<?php print $multiplayer_status; ?>/><span>Multiplayer</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="new"<?php print $new_status; ?>/><span>New Games</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="puzzle"<?php print $puzzle_status; ?>/><span>Puzzle</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="racing"<?php print $racing_status; ?>/><span>Racing</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="fps"<?php print $fps_status; ?>/><span>FPS</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="space"<?php print $space_status; ?>/><span>Space</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="sports"<?php print $sports_status; ?>/><span>Sports</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="strategy"<?php print $strategy_status; ?>/><span>Strategy</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="stunt"<?php print $stunt_status; ?>/><span>Stunt</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="top_rated"<?php print $top_rated_status; ?>/><span>Top Rated</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="words"<?php print $words_status; ?>/><span>Words</span><br>
 <input class="checkbox" type="checkbox" name="game_category[]" value="zombies"<?php print $zombies_status; ?>/><span>Zombies</span><br></td>
</tr>

<tr>
 <td id="insert_cat_name">Game keywords:</td>
 <td><textarea id="insert_text_area" name="game_keywords"></textarea></td>
</tr>

<tr>
 <td id="insert_cat_name">Game image:</td>
 <td><input type="file" name="game_image"></td>
</tr>

<tr>
 <td id="insert_cat_name">Game HTML:</td>
 <td><input type="file" name="game_code"></td>
</tr>

<tr>
 <td id="insert_cat_name">Game Flash File:</td>
 <td><input type="file" name="game_file"></td>
</tr>

<tr>
 <td id="insert_cat_name">Game description:</td>
 <td><textarea id="insert_text_area" name="game_desc"></textarea></td>
</tr>

<tr>
 <td colspan="2" align="center"><input type="submit" name="submit" value="Publish Game Now" id="insert_submit"></td>
</tr>

</form>

</body>
</html>

I know its a bit too long because there are many categories using the HTML form and the "if" statement...

This is the categories.php:

<!DOCTYPE HTML>
<html>
<head>
</head>
<body>

 <div class='container'> <!--Start of the container-->

 <div><?php include("includes/header.php"); ?></div>
 <div><?php include("includes/navbar.php"); ?></div>
 <div><?php include("includes/right_col.php"); ?></div>

 <div class='main_col'> <!--Start of the Main Column-->

 <div><?php echo '<img id="category_title" src="images/genres/' . $_GET[genre] . '.png"'; ?></div>

 <div class='main_content'>
 <?php
 include("includes/connect.php");

 if(isset($_GET['genre'])){

 $game_category = $_GET['genre'];

 $select_games = "SELECT * FROM games WHERE game_category IN ($game_category) ORDER BY game_name ASC";

 $run_games = mysql_query($select_games);

 echo '<table>';
$games = 0;
while($row = mysql_fetch_array($run_games)){
   // make a new row after 9 games
   if($games%9 == 0) {
      if($games > 0) {
         // and close the previous row only if it's not the first
         echo '</tr>';
      }
      echo '<tr>';
   }
   // make a new column after 3 games
   if($games%3 == 0) {
      if($games > 0) {
         // and only close it if it's not the first game
         echo '</td>';
      }
      echo '<td>';
   }

   $game_id = $row['game_id'];
   $game_name = $row['game_name'];
   $game_category = $row['game_name'];
   $game_keywords = $row['game_name'];
   $game_image = $row['game_image'];
   ?>
<div class="game_grid">
   <div id="game_name"><a href="game_page.php?id=<?php echo $game_id; ?>"><?php echo $game_name; ?></a><br /></div>
   <div id="game_image"><a href="game_page.php?id=<?php echo $game_id; ?>"><img src="images/games_images/<?php echo $game_image; ?>" width="120" height="120" /></a></div>
</div>
   <?php 
$games++;  
}

}
?>
</table>
</div>
</div> <!--End of the Main Column-->

 </div> <!--End of the container-->

 <div><?php include("includes/footer.php"); ?></div>

</body>
</html>

Note: I removed everything from the tags, because its private and its not relevant to the question :)...

Answer 1

Most likely you will need a lookup table of sorts. A table of game_categories consisting of id (unique), game_id (foreign key), and category_id (or category_name). That way, you can have the same game stored in multiple categories. Then your queries can be joined at runtime depending on what you are trying to accomplish. I hope this helps!

Answer 2

You could switch your select string to use "IN".

$cat = "'" . str_replace(",", "','", $game_category) . "'";
$select_games = "SELECT * FROM games WHERE game_category IN ($cat) ORDER BY     game_name ASC";

Answer 3

I solved the issue myself again xD, I thought about my search engine, it's using keywords to generate me results, so, I did the same with the game_category since its structure in some games is the same, for example: Game with 1 category under the game_category record within the "games" table: action

Game with multi categories under the game_category record within the "games" table: action,arcade,shooting,fps...

So I replaced this line from this:

$select_games = "SELECT * FROM games WHERE game_category='$game_category' ORDER BY game_name ASC";

Into this:

$select_games = "SELECT * FROM games WHERE game_category LIKE '%$game_category%' ORDER BY game_name ASC";

So now, if I'm under the "Action" category on my website, this game will show, if I'm under the "Arcade" category, this game will also show, Basically the game will show in any category it was assigned to by "reading" the category keywords...

Hope that helped someone ;)