I’m pleased to present you with part one of a two part series on creating a photo site using PHP, jQuery, and AJAX. Originally, I intended to fit the entire tutorial into one screencast, but that quickly became a pipe dream as I realized that there was simply too much to cover. Nevertheless, even if you only watch this first video, you should learn a great deal.
We’ll be retrieving images from a database, creating a simple login form with authentication, and will then allow for our database to be asynchronously updated. Sounds good? If so, let’s get into it.
Our Goal
For this lesson, our goal is to create a photo admin section where an administrator can retrieve photos from a database and update the title of his photos simply by clicking on the appropriate text. To make this page more secure, we’ll create a simple membership system as well. In subsequent tutorials, we’ll expand upon these concepts.
Creating Our Tables With MySQL
Open PHPMyAdmin and create a database called “db”. Within this database, we’ll need to add two tables: “photos” and “users”.
photos
Rather than hard-coding our images into our document, we’ll store them in a database. Though we could make this table as sophisticated as we want, we’ll just keep it simple for now.
- id – INT – Primary Key – Auto Increment
- title – VARCHAR(500)
- src – VARCHAR(200)
users
In order to edit our photos, we need to create a simple membership system that will allow our user to login and edit his photos. We obviously don’t want to give everybody that right!
- id – INT – Primary Key – Auto Increment
- first_name – VARCHAR(50)
- last_name – VARCHAR(50)
- email_address – VARCHAR(75)
- user_name – VARCHAR(25)
- password – VARCHAR(30)
Creating the Simple Layout
Create a new php document and add the following into your document.
<!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>
<title>My Photos Site
<link rel="stylesheet" href="css/default.css" />
<link rel="stylesheet" href="css/jquery.lightbox-0.5.css" />
<script src="js/jquery-1.2.6.pack.js" type="text/javascript">
<script src="js/jquery.lightbox-0.5.pack.js" type="text/javascript">
<script src="js/scripts.js" type="text/javascript">
</head>
<body>
<?php require_once 'photo.php'; ?>
<form action="changePhotoTitle.php" method="post">
<div id="container">
<h1>My Photos <small>click on the text to change the title.</small></h1>
<a href="login.php?logout=1" id="logout">logout
<div id="main">
<?php require 'getPhotos.php'; ?>
<div id="response" class="hidden" />
</div><!-- end main-->
</div><!-- end container -->
</form>
</body>
</html>
Don’t worry too much about some of what’s here. We’ll go over everything in time. But as a quick overview, we’re importing our jQuery library, a jQuery lightbox plugin, and our custom scripts (that we’ll create soon). Take a few seconds to review the CSS file. I won’t go over it too much in this written article – as it’s long enough already! Refer to the screencast for more information. But, I assure you it’s pretty standard and simple stuff.
The primary thing that you need to focus on right now is:
<?php require 'getPhotos.php'; ?>
This will be the section where we retrieve our photos from the database. Let’s work on that right now. Create a new page
called “getPhotos.php”.
Let’s quickly go over what we need to accomplish with this code.
- Create a connection to the database
- Retrieve all of the rows from the photos table
- If the connection was successful, create an unordered list.
- Filter through the rows and place the necessary information within list item tags.
Paste the following code into your document:
<?php
require 'database.php';
$query = "SELECT id, title, src FROM photos";
$result = $mysqli->query($query) or die(mysqli_error($mysqli));
if ($result) {
echo "<ul id='photos'> \n";
while ($row = $result->fetch_object()) {
$title = $row->title;
$src = $row->src;
$id = $row->id;
echo "<li><a title='$title' href='images/$src'><img src='images/$src' id='$id' alt='$title' /> </a>\n";
echo "<h4>$title</h4> \n";
echo "<input type='text' name='title' value='$title' /></li> \n \n";
}
echo "\n</ul>";
}
?>
The first thing we need to do is create a connection to our database. However, we’ll be doing this many times over the course of this project. So let’s create another page called “database.php” and store the information there. That way, we only need to edit one file, should the need arise. Add the following code into your database.php file.
<?php $db_name = "users"; $db_server = "localhost"; $db_user = 'root'; $db_pass = ''; $mysqli = new MySQLi($db_server, $db_user, $db_pass, $db_name) or die(mysqli_error()); ?>
We’re creating a variable called “$mysqli” and are setting it to a new instance of the “MySQLi” object. We need to pass in four parameters:
- database name
- the server
- username
- password
To keep things clean, we’ll place those values in variables and reference them. If the connection isn’t made, we’ll tell it to “die”.
Querying the Database
Now let’s move back to our “getPhotos.php” file. The next step, is to query the database.
$query = "SELECT id, title, src FROM photos"; $result = $mysqli->query($query) or die(mysqli_error($mysqli));
Our query is extremely simple. We’re essentially retrieving everything from the database. You might want to modify this
query to fit your own application. We pass in the query by using “$mysqli->query($query)”.
If that query was performed successfully, we’ll create our unordered list and add our images.
echo "<ul id='photos'> \n";
while ($row = $result->fetch_object()) {
$title = $row->title;
$src = $row->src;
$id = $row->id;
echo "<li><a title='$title' href='images/$src'><img src='images/$src' id='$id' alt='$title' /> </a>\n";
echo "<h4>$title</h4> \n";
echo "<input type='text' name='title' value='$title' /></li> \n \n";
}
echo "\n</ul>";
We create a variable called $row that will be equal to each instance of a row in the database. As long as there are rows
to be retrieved, this variable will contain that specific row’s information.
We’ll store all of the information that we need in variables. For example, let’s say that in one row, we have the following data.
- id = 4
- src = 10.jpg
- title = “My favorite photo”
By performing $row->title, we can grab that value and store it in our $title variable. So in this case, $title = “My favorite photo”; $src = “10.jpg”; $id = 4;.
All that we need to do now is add that information into our image and anchor tags. The last line will add an input field. This will eventually allow the user to update the title of the image asynchronously.
If you run “index.php” in your browser, you should see the following:
AJAX Time
Now that we have the basic layout of our site, let’s ajaxify it! We’ll start by summarizing what we need our PHP to do. I find that this helps a great deal when working.
- When the user clicks on the title of a specific image, we’ll then display an input field that allows the user to change the title.
- When he tabs away from that input field, we’ll use AJAX to perform a SQL update statement.
- To give the user some feedback, we’ll display a div that says something along the lines of “Success. The database has been updated.”
Create a PHP file called “changePhotoTitle.php” and paste in the following code:
<?php
require_once 'database.php';
$title = mysql_real_escape_string($_POST['title']);
$id = mysql_real_escape_string($_POST['id']);
$update_query = "UPDATE photos SET title = '$title' WHERE id='$id'";
$result = $mysqli->query($update_query) or die(mysqli_error($mysqli));
if ($result) {
echo "Success! <br />";
echo "The title of this photo has been changed to: <strong>$title</strong>";
}
?>
We’ve required the database file again. (Aren’t we glad that we stored that in a separate file?) Next, we’re creating two
variables. $title is equal to whatever the user enters into that input field. $id is equal to its respective id field in
the database.
To prevent some SQL injection, we’ll wrap those post values with mysql_real_escape_string().
Updating the Database
The update is rather straight forward. Update the photos table and change the title field to whatever the users enters in to that textbox; But only change the title field that has an id equal to $id. (Refer to the screencast for a better understanding.)
If the update has been performed successfully, we’ll echo a congratulations message.
Implementing the Javascript
In order to perform the update without a postback, we’ll use jQuery. Create a new Javascript file called “scripts.js” and
paste in the following:
$(function() {
$('h4').click(function() {
$(this).slideUp().next('input').slideDown();
});
$('ul#photos input').change(function() {
var id = $(this).parent('li').find('img').attr('id');
var thisParam = $(this);
var title = $(this).val();
$.ajax({
type: 'POST',
url: 'changePhotoTitle.php',
data: 'title=' + title + '&id=' + id,
success: function(response) {
$('input').slideUp();
$(thisParam).prev('h4').text(title).slideDown();
$('#response').fadeIn(1000).empty().append(response).prepend('<span id="x">X</span>');
$('span#x').click(function() {
$('div#response').fadeOut('slow');
});
}
});
});
});
- When the user clicks on the title of an image (the h4 tag), we’ll run a function. We’ll hide the h4 tag and then display the input field instead.
- We’ll create two variables. “title” will store the value of what is entered into the textbox. “id” will store its respective id.
AJAX
Using “$.ajax”, we’ll call the update. $.ajax will accept four parameters.
- Type = the type of update. In this case, it will be ‘Post’
- Url = the url of the file that will perform our update. We’ve already created that file.
- Data = the information that we’re going to pass to changePhotoTitle.php. In this case, we’re passing the value of the textbox and the id.
- Success = this will be the function that runs if the update has been performed successfully. In this function, we’ll remove the textbox because we no longer need it. We’ll go back and display the h4 tag that now has the updated value.
To provide user feedback, we’ll take take the “response” div that we created at the beginning of this tutorial and fill it with our “Congratulations” echo. Those last two lines simply allow for a way for the user to remove that “Congratulations” div. If you need to, refer to the screencast for a full explanation.
Implementing the Login Form
Now obviously, we don’t want just anyone to be able to edit the titles of these images. So, we’ll create a simple membership system.
Create a new page called “login.php” and add the following code:
<?php
if($_GET['logout'] == 1) {
setcookie('authorized', 0, time()-3600);
}
?>
<!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>
<title>Login to edit photos</title>
<style type="text/css">
h2 {
margin-top: 0;
}
body {
text-align: center;
font-family: helvetica, arial;
}
#loginForm {
padding: 1em;
background: #e3e3e3;
width: 260px;
margin: 3em auto 0;
text-align: left;
}
</style>
</head>
<body>
<div id="loginForm">
<form method="post" action="confirmLoginCredentials.php">
<h2>LOGIN</h2>
<p>Username: <input type="text" name="username" /></p>
<p>Password: <input type="password" name="password" /></p>
<p><input type="submit" name="submit" value="Login" /></p>
</form>
</div>
</body>
</html>
Within the body tag, we have a simple form that has two fields: one for the username, the other for the password. When the user clicks the “Login” button, the information from those textboxes will post to “confirmLoginCredentials.php”. Go ahead, create that page right now and add the following.
<?php
$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
require 'database.php';
$q = "SELECT first_name, last_name FROM users WHERE user_name = '$username' AND password = '$password'";
$result = $mysqli->query($q) or die(mysqli_error($mysqli));
if (!mysqli_num_rows($result) == 1) {
header("Location: login.php");
}
else {
setcookie('authorized', 1, 0);
header("Location: index.php");
}
?>
Per usual, we are storing the values from those textboxes in variables called $username and $password, respectively. We once again pull in our database.php file. Our query retrieves the row from the database where the user_name and the password are equal to what was entered into those two textboxes. Only one row should be returned. If that doesn’t happen, the user will redirected back to “login.php”. Otherwise, we’ll send them to the main “index.php” page and set a cookie.
setcookie('authorized', 1, 0);
The name of our cookie will be “authorized”; the value will be equal to 1; and the expiration date will be 0. 0 essentially means that when the user closes his browser, that cookie will expire.
When ther user is directed to the index.php page, we need to write a quick bit of code that ensures that there is the required cookie on the user’s computer. Go back to index.php and add the following code to the very top of your document.
<?php
// Verifies if you have a cookie - credentials.
if (!$_COOKIE['authorized'] == "1") {
header("Location: login.php");
}
?>
If the user has a cookie named “authorized” on his computer, and that cookie has a value equal to 1, then display the page. But if they DON’T, send them back to login.php.
Logging Out
The final step in this tutorial is to allow the user to log out. Find the tag on your index.php page that looks like this:
<a href="login.php?logout=1" id="logout">logout</a>
I’m adding a key-value pair to this url. That value will be passed to ‘login.php’. Go to this page and add your last bit of code to the very top.
<?php
if($_GET['logout'] == 1) {
setcookie('authorized', 0, time()-3600);
}
?>
If, in the querystring, the key of ‘logout’ is equal to 1, set a cookie that has an expiration date of an hour ago. This will remove the cookie from the user’s computer.
Finally, you’re done…for now!
So we’ve created quite a bit. Unfortunately, to do everything I’d like would take pages and pages. Perhaps next week, Part 2 will be released. In Part 2, we’ll implement the following:
- Write some code that allows the administrator to edit the photos. If they aren’t logged in, they can view the photos, but aren’t allowed to edit them.
- Implement Lightbox functionality
- Create insert and delete pages
- Secure the site more
- Add more validation
- Make the site prettier
- And plenty more…
Thanks so much for watching the screencast and reviewing the article. There’s more to come!
Enjoyed this Post?
Subscribe to our RSS Feed, Follow us on Twitter or simply recommend us to friends and colleagues!
Pingback: 20 Useful PHP & jQuery Components & Tuts for Everyday Project | Noupe
Pingback: 克兰印象 » 20款PHP+JQuery应用
Pingback: WebNet » PHP Gallery Admin
Pingback: Weekly Bookmarks #1 (Diigo) | Blog de Maïs.ch
Pingback: Editors Choice » Create a Photo Admin Site Using PHP and jQuery
Pingback: 53 Jquery Tutorials, Resources, Tips And Tricks: Ultimate Collection | 1stwebdesigner - Love In Design
Pingback: 2009 Web Design Trends and How-To’s | Design Tips | 有限会社 インスピレーション
Pingback: 50 Best jQuery Resources For Designers - Tutorials, Tips and Tricks « Wisdom Plug
Pingback: Youblogoff.com » Blog Archive » 2009 Web Design Trends and How-To’s
Pingback: Tendencias del diseño web 2009 | Futuro de la Web Magazine
Pingback: Weekly Bookmarks #1 (Diigo) - Blog de Maïs
Pingback: Useful PHP & jQuery Components « Developer Suite
Pingback: 55 Decisive Useful jQuery Tutorials « my mcLife