Displaying MySQL Data in a Grid

[arwebdesign]

Hi guys. I'm trying to build a games site for a friend. Currently, the front-end (HTML/CSS) of the site is done. Now, I want to make a way for him to easily add games to the site. Ideally, I'd like to make a database with the following columns: ID, Name, Category, Link, Thumbnail_Link. So, those would be the ID, name of the game, the category, a link to the game, and a link to the 50x50 thumbnail image respectively. Then, using PHP, I'd like to call the first x number (not sure what it will be yet, let's say 50) and make format it as a grid in the following way: There's the thumbnail image followed by the game name, and they're all a clickable link to the game URL. Is this possible? How would I go about doing this? I've already set up a database for a login module to the site, so each page has already opened a connection to the MySQL database. However, I've only ever done basic PHP for mail forms and am otherwise extremely new to it, and am especially new to MySQL.

 

Could anyone walk me through how to do this or even give me a quick example script to work off of?

 

Thanks, any of your time is greatly appreciated!

[BarneyJoe]

This is only really of any help if you have access top Dreamweaver, as its an extension - but looks like the sort of thing you're after...

 

http://www.tom-muck.com/extensions/help/HorizontalVerticalLooperHelp/index.cfm

[Alt_F4]

Firstly, are you asking how to easily add games or how to display them in a grid or both?

Secondly, what did you use to set up your database for the login model? phpmyadmin?

[Yesideez]

I've written a script and gone one step further - this dumps everything into a table as requested and also pageinates everything with page index links at the bottom.

<table class="boxGames">
<?php
$intPageNum=(isset($_GET['page']) ? intval($_GET['page']) : 1); //GET PAGE NUMBER FROM URL
$intPageSize=50; //SET OUR PAGE SIZE
$info=mysql_fetch_assoc(mysql_query("SELECT COUNT(id) AS total FROM games"));
$intTotal=$info['total']; //TOTAL NUMBER OF GAMES LISTED IN THE DATABASE
$intPageCount=ceil($intTotal/50); //HOW MANY PAGES OF GAMES DO WE HAVE?
if ($intPageNum<1 || $intPageNum>$intPageCount) {
  $intPageNum=1; //IF NUMBER FROM URL IS OUTSIDE OF RANGE SET PAGE NUMBER TO 1
}
$intOffset=($intPageNum*$intPageSize)-$intPageSize; //GET OFFSET FOR QUERY
echo '<tr><th>Name</th><th>Category</th><th>Thumbnail</th></tr>';
$sql="SELECT * FROM games ORDER BY name LIMIT ".$intOffset.",".$intPageSize;
$query=mysql_query($sql);
while ($row=mysql_fetch_assoc($query)) {
  echo '<tr><td><a href="mylink.php?id='.$row['id'].'">'.$row['name'].'</a></td><td>'.$row['category'].'</td><td><img src="thumbs/'.$row['thumbnail'].'" alt="Thumbnail" width="60" height="50"></td></tr>';
}
echo '<tr><td colspan="4" style="text-align:center">';
for ($i=1;$i<=$intPageCount);++$i) { //LOOP TO BUILD THE PAGE INDEX AT THE BOTTOM
  if ($i==$intPageNum) {
    echo '<strong>'.$i.'</strong> '; //CURRENT PAGE DOESN'T NEED A LINK TO ITSELF
  } else {
    echo '<a href="?page='.$i'.">'.$i.'</a> ';
  }
}
echo '</td></tr>';
?>
</table>

[arwebdesign]

@Alt_F4

Yes, I'm using PhpMyAdmin.

And I'm asking how to make a database of games set up the way I described display in a formatted grid. That being said, if there's an easier way to make an easy dynamic portion of the site that displays games, I'd love to know!

 

@Yesideez

Thanks so much! I'm going to try it right now and report back!

[arwebdesign]

Hey there. Thanks so much for your help so far. I tried the script you gave me and it works well, but it's not a grid like I envisioned, but a simple table. Here's a rough diagram of what I'm trying to achieve:

[link=http://dl.dropbox.com/u/6776265/grid_example.png]Example[/link]. Any idea on how to modify the script to get this?

 

Thanks again!

[litebearer]

it's not a grid like I envisioned, but a simple table.

 

perhaps your definition of a 'grid' vs a 'table' may be in order.

[arwebdesign]

Sorry for not being clear before. I think the confusion may be that, in practice, what I'm calling a "grid" would be coded just like a table. However, there would be several more columns and rows, because each cell would hold a thumbnail and a link. Instead of a table with labels across the top (e.g. "Name," "Category," etc.) what I'm looking for has no labels, and is of a set width and height (again, let's say a width of 5 x height of 10). So, that's 50 cells. Each cell has the thumbnail of a game in it, and the link to the game below it.

 

Here's the diagram again of what I'm trying to get:

http://dl.dropbox.com/u/6776265/grid_example.png

 

Thanks so much again,

Leon

[wildteen88]

That's pretty much what Yesideez's code does. If you don't want labels then remove this line

echo '<tr><th>Name</th><th>Category</th><th>Thumbnail</th></tr>';

 

If you understand HTML you should be able to adapt the code to your needs.

[arwebdesign]

That's pretty much what Yesideez's code does. If you don't want labels then remove this line

echo '<tr><th>Name</th><th>Category</th><th>Thumbnail</th></tr>';

 

If you understand HTML you should be able to adapt the code to your needs.

 

It's not making the grid in HTML that I'm having trouble with, it's integrating it with PHP. Can anyone lead me in the right direction? Here's my grid code in HTML:

         <div id="grid">
           <table width="875" border="0" cellspacing="10" cellpadding="0">
             <tr>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
             </tr>
             <tr>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
             </tr>
             <tr>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
             </tr>
             <tr>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
             </tr>
             <tr>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
             </tr>
             <tr>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
               <td> </td>
             </tr>
           </table>
         </div>
         <!----end grid---->
</div>

 

But obviously, it's empty. How do I fill it dynamically with the data from the database with the highest ID going in the top left cell, and then in descending order by ID. (i.e. the newest game is first, 2nd newst game is second, etc.).

 

-Thanks

 

[wildteen88]

This will be the basic code you'll need. Just modify it to your requirements.

<table>
<?php

$sql = 'SELECT game_id, game_name, game_thumbnail_image FROM games ORDER BY game_id DESC';
$result = mysql_query($sql);

$i = 0;
while(list($id, $name, $thumbnail) = mysql_fetch_row($result): ?>
    <?php if($i == 0): ?><tr><?php endif; ?>

        <td>
            <img src="<?php echo $thumbnail; ?>" />
            <a href="game.php?game_id=<?php echo $id ?>">Play: <?php echo $name; ?></a>
        </td>

    <?php if(++$i == $max_columns): ?></tr><?php $i = 0; endif; ?>
<?php endwhile; ?>
</table>

 

Add in some simple CSS to style the table

<style type="text/css">
td { width: 150px; text-align: center; }
a  { display: block; }
</style>

[arwebdesign]

Thanks so much, this works awesome! Just one quick last question: how can I limit the number of rows? I saw there's a maximum columns variable, but is it possible to make a maximum rows one too?

 

Thanks again!

[wildteen88]

Thanks so much, this works awesome! Just one quick last question: how can I limit the number of rows? I saw there's a maximum columns variable, but is it possible to make a maximum rows one too?

Yes its possible, but what will you use this variable for? Creating a new grid?

[arwebdesign]

I would just use it to limit the number of items that load into the homepage since the games section is of a fixed height.

[wildteen88]

You wouldn't need another counter for that, just define the max number of results you want returned from your query. So if you want to display 30 games (5 x 6 grid) then use

$sql = 'SELECT game_id, game_name, game_thumbnail_image FROM games ORDER BY game_id DESC LIMIT 30';

 

 

[arwebdesign]

Thanks, that's great!