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!
If you have ever had to run a series of SQL statements that alter the table or tables, then you know the fear of encountering an error in the middle of the script! Let’s say you need to run a series of Insert statements and it errors out before it finishes. You now have extraneous rows in your table or tables that immediately affect your data integrity. So, how do we prevent this? Well, the CFTRANSACTION tag saves the day by allowing you to store all of the SQL changes in an array and not actually committing them to the DB until the script completes error free! We use the CFTRANSACTION tag in conjunction with CFTRY and CFCATCH. The basic syntax is this:
Recent Discussion