PHP for each category?

[adamlacombe]

This is a PHP/MYSQL kind of question, but I was wondering if it was possible to  show a row from each that "such and such = blah".

So for example Im making an arcade site so I want the front page to have a game from each category.

I hope that made some sense, its kind of hard to explain I think.

Thanks in advanced!

[Dathremar]

"Select distinct(category_id), game_name from table_name"

 

This will give You one of each category. Not sure if that is what You need. Post the table structure if you want more help

 

 

[adamlacombe]

ok for game its:

game_id

game_name

game_link

game_thumb

game_des

game_cat

game_type

game_views

_________________________

 

game_cat:

        cat_id

cat_name

_________________________

[Dathremar]

Well If You don't want specific games on your home page then:

"Select distinct(game_cat), game_name from game" , will do the trick.

[adamlacombe]

Hmm...

its not seeming to work.

This is what I got all together:

<table>
<?
$game="Select distinct(game_cat), game_name from game";

$game2=mysql_query($game) or die("Could not get games");

while($game3=mysql_fetch_array($game2))

{

   echo'<tr align="left" valign="top"> 
      <td align="left" width="71"><span> 
        <a href="index.php?action=game&id='.$game3[game_id].'">';

echo'
        <img src="game/thumbs/'.$game3[game_name].'.jpg" height="71" width="71" />';

echo'</a></span></td> 
      <td>
        <a href="index.php?action=game&id='.$game3[game_id].'">'.$game3[game_name].'</a>
<br />
<small>Played '.$game3[game_views].' Times</small></td></tr>';

}
?>
</table>

 

And is it possible to get the category name for each, so like it will say:

Action

--------

Game1...

 

Thanks again!

[Dathremar]

Try this:

 

"SELECT GC.cat_id, G.game_name, GC.cat_name FROM game G, game_cat GC WHERE G.game_cat = GC.cat_id GROUP BY GC.cat_id"

[adamlacombe]

nothing at all shows up now

[Dathremar]

I created those 2 tables and I assume that game_cat is foreign key of cat_id from the game_cat table and run that sql and I get expected results

[adamlacombe]

Sorry, Im not understanding.

Im kinda new at this type of thing. I know a lot about php not to much on mysql though.

[adamlacombe]

I still cant get it to work, I tried this:

<?
echo'<table>';

$r="SELECT * from game_cat";

$r2=mysql_query($r) or die("Could not get cats");

$row=mysql_fetch_array($r2);

       echo '<strong>'.$row['cat_name'].'</strong><br />';


$game="Select * FROM game GROUP BY game_cat LIMIT 0,5";

$game2=mysql_query($game) or die("Could not get games");

while($game3=mysql_fetch_array($game2))

{

   echo'<tr align="left" valign="top">
      <td align="left" width="71"><span>
        <a href="index.php?action=game&id='.$game3[game_id].'">';

echo'
        <img src="game/thumbs/'.$game3[game_name].'.jpg" height="71" width="71" />';

echo'</a></span></td>
      <td>
        <a href="index.php?action=game&id='.$game3[game_id].'">'.$game3[game_name].'</a>
<br />
<small>Played '.$game3[game_views].' Times</small></td></tr>';

}
?>
</table>

 

But it is only coming up with 3 games and it is not displaying the games from each category.