Get a Paginated Grid of Results Using PHP/MySQL

Posted on by — 20 Comments ↓
php mysql

PHP/MySQL

Have you ever tried to create a nice grid of MySQL results before? Perhaps something 4 items across and 4 items down with pagination for more than 16 results? Well, let’s go ahead and build that here so you can learn how to do it! Using tables for this is tricky and ugly. I don’t recommend it. We will be using some very straight forward PHP and CSS for this! Okay, this tutorial is not difficult to follow if you are familiar with PHP and MySQL. If you are brand new to PHP and/or MySQL, you should familiarize yourself with them by going through some more basic tutorials first! This tutorial also assumes that you have access to a web server that is running PHP 5.x and MySQL server. One last disclaimer…this tutorial doesn’t cover securing any of this stuff. I will likely create another tutorial in the future for keeping your stuff secure. So, be warned, none of what I’m giving you here is secured!

First, let’s look at the basic HTML and CSS that make this work:

<style type="text/css">
#dataGrid{
  width: 800px;
  margin: auto;
}

.thumb{
  width: 150px;
  height: 100px;
  float: left;
  clear: none;
  margin: 5px;
  background: #ccc;
}
</style>

<div id="dataGrid">
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
  <div class="thumb"></div>
</div>

DEMO THE ABOVE CODE!

Okay, let’s get into the details. We need a database. I’m calling mine dbGallery. Using phpMyAdmin, or another MySQL client, create your new database and name it whatever you want. Now, inside of the dbGallery database, I’m going to create a table called tblPhotos. Again, call yours whatever you like. tblPhotos is going to have the following fields: photoID(int 11), photoName(varchar 30), category(varchar 20), active(int 1). Make sure to set photoID as the primary key and also set it to auto-increment! Next we need to add some photos to tblPhotos. So, let’s create a nice script that will make this more automated. In your web root, create a new folder called galleryScripts. In the galleryScripts folder, create a new PHP file called mysqlConnect.php and give it the following contents:

<?php  

$db_host = "";  //put your hostname in the quotes

$db_username = "";  //put your username in the quotes

$db_pass = "";  //put your password in the quotes

$db_name = "dbGallery"; //change dbGallery to whatever your db is called

mysql_connect("$db_host","$db_username","$db_pass") or die ("could not connect to mysql");
mysql_select_db("$db_name") or die ("no database");              
?>

Now, back in the web root, create a folder called galleryPhotos. Also in the web root, create a new PHP file called addPhotos.php and give it the following contents:

<?php
include "galleryScripts/mysqlConnect.php";

//Parser for Add Photo Form
if (isset($_POST["photoName"])){
	$photoName = mysql_real_escape_string($_POST["photoName"]);
	$category = mysql_real_escape_string($_POST["category"]);

	//add photo to db
	$sql = mysql_query("
			   INSERT INTO 
			   	tblPhotos (
				photoName,
				category,
				active
			   )VALUES(
				'$photoName',
				'$category',
				'1'
			   )") or die(mysql_error());
	$pid = mysql_insert_id();
	$newname = "$pid.jpg";
	move_uploaded_file($_FILES['fileField']['tmp_name'],"galleryPhotos/$newname");
	header("location: addPhotos.php");
	exit();
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Add Photos</title>

</head>
<body>

<h3>Add New Photo</h3>
<form action="addPhotos.php" enctype="multipart/form-data" name="myForm" id="myForm" method="post">
	Photo Name: <input name="photo_name" type="text" id="photoName" size="64" /><br />
	Category: <input name="category" type="text" id="category" size="64" /><br />
	Upload Image (jpg only): 
    <label>
    	<input type="file" name="fileField" id="fileField" />
    </label>
    <label>
    	<input type="submit" name="button" id="button" value="Add Photo" />
    </label>
</form>

</body>
</html>

And now, in your web root, create a new PHP file called gallery.php and give it the following contents:

<?php
//connect to db
include "galleryScripts/mysqlConnect.php";

//check for a page number. If not, set it to page 1 
if (!(isset($_GET['pagenum']))){ 
	$pagenum = 1; 
}else{
	$pagenum = $_GET['pagenum'];
}

//query for record count to setup pagination
$data = mysql_query("SELECT * FROM tblPhotos WHERE active = 1"); 
$rows = mysql_num_rows($data); 

//number of photos per page 
$page_rows = 16; 

//get the last page number 
$last = ceil($rows/$page_rows); 

//make sure the page number isn't below one, or more than last page num 
if ($pagenum < 1){ 
	$pagenum = 1; 
}elseif ($pagenum > $last){ 
	$pagenum = $last; 
}

//Set the range to display in query 
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;

//get all of the photos
$dynamicList = "";
$sql = mysql_query("SELECT * FROM tblPhotos WHERE active = 1 $max");
//check for photos
$photoCount = mysql_num_rows($sql);

if ($photoCount > 0){
	while($row = mysql_fetch_array($sql)){
			$photoID = $row["photoID"];
			$photoName = $row["photoName"];
			$category = $row["category"];

			$dynamicList .= '
							<div class="thumb">
								<a href="photo.php?id=' . $photoID . '"><img class="clip" src="galleryPhotos/' . $photoID . '.jpg" alt="' . $photoName . '" width="175" border="0" /></a>
							</div>
							';
	}
}else{
	$dynamicList = "There are no photos at this time!";
}

mysql_close();
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Tech Remedy Grid Gallery Demo</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>
	<?php
	    echo '<p style="text-align:center; font-weight:bold;">Page ' . $pagenum . ' of ' . $last . '</p>';

        if ($pagenum == 1){
          	echo '<div class="pagination" align="center"><ul>';
        }else{
            echo '<div class="pagination" align="center"><ul><li><a href="' . $_SERVER['PHP_SELF'] . '?pagenum=1">« first</a></li>';  
            $previous = $pagenum-1;
        }       

		//check if number of pages is higher than 1
		if($last != 1){
			//Loop from 1 to last page to create page number links
			for($i = 1; $i <= $last; $i++){
				echo '<li><a href="' . $_SERVER['PHP_SELF'] . '?pagenum=' . $i . '">' . $i . '</a></li>';
			}
		}

        if ($pagenum == $last){
            echo '</div>';
        }else{
            $next = $pagenum+1;
            echo '<li><a href="' . $_SERVER['PHP_SELF'] . '?pagenum=' . $last . '">last »</a></li></ul></div>';
        }
    ?>

    <div id="dataGrid">
		<?php echo $dynamicList; ?>
    </div>

</body>
</html>

Okay, we are ready to start adding some photos to our gallery! Open a browser and navigate to your addPhotos.php file. Fill out the form and add a photo. Repeat the process until you have added several photos. Make sure to add more than 16 if you want to see the pagination working!

Now, if you go to gallery.php in your browser, your images should be displayed in a vertical list. It doesn’t look all that great. So, let’s create a CSS file that will make the images appear in a grid and clip them to all fit real nicely! In the web root, create a new CSS file called style.css and give it the following contents:

#dataGrid{
  width: 800px;
  margin: auto;
}

.thumb{
  width: 175px;
  height: 100px;
  float: left;
  clear: none;
  margin: 5px;
}

.clip {
	position: absolute;
	clip:rect(0px 175px 100px 0px);
}

.pagination{
	padding: 2px;
}

.pagination ul{
	margin: 0;
	padding: 0;
	text-align: center;
	font-size: 16px;
}

.pagination li{
	list-style-type: none;
	display: inline;
	padding-bottom: 1px;
}

.pagination a, .pagination a:visited{
	padding: 0 5px;
	border: 1px solid #9aafe5;
	text-decoration: none; 
	color: #2e6ab1;
}

.pagination a:hover, .pagination a:active{
	border: 1px solid #2b66a5;
	color: #000;
	background-color: #FFFF80;
}

Now, just navigate to your gallery.php file in your browser and you should see a nice paginated grid of photos! All of the photos links are broken though! Let’s fix that. Create a new PHP file called photo.php and give it the following contents:

<?php
//connect to db
include "galleryScripts/mysqlConnect.php";

//initialize some vars
$photoID = '';
$photoName = '';

//check what photo we are looking for
if(isset($_GET['id'])){
	$photoID = mysql_real_escape_string($_GET['id']);
	//get the photo
	$sql = mysql_query("SELECT * FROM tblPhotos WHERE photoID='$photoID' LIMIT 1");
	while($row = mysql_fetch_array($sql)){
		$photoName = $row['photoName'];
		$category = $row['category'];
	}
}else{
	echo 'No photo was selected!';
	exit();	
}

mysql_close();

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Tech Remedy Gallery Demo - <?php echo $photoName; ?></title>
</head>

<body>
<div id="photo">
	<img src="galleryPhotos/<?php echo $photoID; ?>.jpg" alt="<?php echo $photoName; ?>" width="400" /><br />
    <h2><?php echo $photoName; ?></h2>
    <p><?php echo $category; ?></p>
</div>
</body>
</html>

And that’s it! Now, when you click on a photo in your gallery it will take you to a page that nicely displays your photo along with some photo information. Pretty cool, huh?

DEMO

Let me know in the comments if you come across any problems!

Written by: Tom Duffy on July 30, 2011.on January 10, 2012.

About Tom Duffy

Tom Duffy Owner - Tech Remedy Owner - Host Remedy Development and Linux geek

20 thoughts on “Get a Paginated Grid of Results Using PHP/MySQL”

  1. Hi,

    This is a great tutorial ! Thank you for sharing Tom ! (… and thanks for the demo link)
    Regards from France.

    MB

  2. Tom,

    Where is the file named “gallery.php” ? gallery.php or .html ?
    When I tried to upload pictures, nothing happened…
    Can you help me ?
    Thanks

  3. There is no gallery.php in this example….I’m not sure how I could have forgotten to post that, but it looks like I did. I will add it tonight. Thanks for pointing it out!

  5. Hi
    I am having a problem
    i am using php mysql on my pc and i followed your tutorial very attentively. But in the addphotos.php form when i type the name, category name and select photos to upload, after clicking the submit button nothing happens.
    The imageis neither uploaded to the folder nor the sql gets any data.
    I double checked the code and found no errors. Please help

  7. There isn’t really a difference here…Unless I’m missing something, why would I need to change it to $_POST["button"]?

  8. It didn’t work for me if isset was for photoName, so instead checking a input field it checks if submit button was clicked, anyways thanks for the script :)!

  9. Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/a7869388/public_html/gallery.php on line 14

  12. image information is getting added in database but the image doesnt display when we see in browser.Only name is displaying with thumbs. 

  15. Hi,
    The images i uploaded is not store in the database and the gallaryphotos. pls what will i do to it

  16. having major problem here, changed if (isset($_POST["photoName"])) TO if (isset($_POST["button"])) as it wasnt workign at all, but now its registering in the database but the browser just says “page 1 of 1″ no idea what to do?? can anyone help?

  20. very interesting, alltough the DEMO is not working. But I see a big issue of security. Is the root folder the 'public_html' folder? That is accessible to anyone, but how could be this for a file with DB sensitive info? Please correct me if I'm wrong, 10x.