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!
By Jeffrey Way
Premium Members
Source Files, Bonus Tutorials & More for all relevant Tuts+ sites in one subscription. Join Now
Related Posts
Premium Membership
Source Files, Bonus Tutorials & More for all relevant Tuts+ sites in one subscription
LivingDesign - More
Web Basix
Just getting started? Start with our basix tutorials on the fundamentals. View All Basix Tutorials
Other Plus Sites ...
aetuts+ audiotuts+ vectortuts+ tuts+ phototuts+ cgtuts+ nettuts+ psdtuts+ activetuts+Create a Tutorial, Get Paid!
Have something to teach the world? Want to earn money doing it? Tutorials, screencasts, and articles published here on Nettuts+ are largely contributed by readers just like you! We'll pay you great money for good content. Find Out More
I cannot imagine the time you commit to this site. Thank you very much!
I am new, so I went thoroughly through your tutorial, but I cannot seem to connect with the database for retrievals. After typing in user name and password it drops me off at the confirm_login_credentials.php page where it stays.
I’m using Dreamweaver and have my site on Yahoo with a MySQL database. I’ve done simple queries using a $link command and can retrieve database information just fine. So I think it is somewhere in the database, user, password commands on the database.php page.
Additionally I tested by using your entire file package and uploaded it in a subdirectory without my intervention. It still is hanging up at the same location.
Any suggestion?
I just discovered your tutorials and love them. Thank you again. I’ve bookmarked you and will pass you site on to all.
Kathleen
I’m new to this too and likewise had trouble with the confirm_login_credentials.php file. After an interesting afternoon searching the web I finally figured things out and this seems to work – try replacing your confirm_login_credentials.php with this code …
real_escape_string($_POST['username']);
$password = $mysqli->real_escape_string($_POST['password']);
echo $username.”.$password;
$q = “SELECT id FROM users WHERE user_name = ‘$username’ AND password = ‘$password’”;
$result = $mysqli->query($q) or die(mysqli_error());
$num_rows = $result->num_rows;
if ($num_rows == 1) {
setcookie(‘authorized’, 1, 0);
header(“Location: index.php”);
} else {
header(“Location: login.php”);
}
?>
I think this works because database.php declares a mysqli object and the subsequent code in the confirm_login_credentials.php file now uses object notation instead of a procedural flavour of coding. Hope this works for your problem.
Hi im new to this but you do the best tutorials that iv seen so far, this may sound silly but im having a lot of trouble connecting my code to the database please could you do something on that, and how do you take your database on-line once you know the code is ok?
Nice of this tutorial.It helps me lot.
Very Nice tutorial
I need more help
mail me : – rap_2008_qqq@yahoo.com
Is the Part 2 of this series released ? can someone give the url ? I was searching for past 30+ mins and am unable to find it in this site
Very nice!
Your videos are always simple and help me and other users!
Keep doing videos like this please!
Thanks!
Very cool, man! I will use it in my blog! brazillian hugs to you!
Man I sat through this whole tut waiting for the lightbox plugin portion lol.
It’s a good tut though.
Great tutorial
Wow !!!!!!!!!!! It’s a amazing tutorial……….
Can you make an upload image box? I’d really like to be able to do that
Get back to me, n00ne@live.com.au
very good tutorial it’s amazing
tks for share this is
I have some problem to connect index.php page ?
why ı don’t login ?
username= user_name
password=password
these are the defeults but I can’t login
and I see these Error
Notice: Undefined index: logout in C:\wamp\www\admin\login.php on line 3
how can I fix these ? please help
I got the same problem. Here’s what’s happening.
On login.php you are saying “If ‘logout’ equal 1 then expire the cookie”. But if “logout=1″ isn’t in the URL then $_GET['logout'] isn’t set to anything and is undefined. PHP doesn’t like this.
Here’s what I did to solve the problem…
Simply remove the IF statement from the top of login.php and just set the cookie with a negative expire time. This way the cookie is always expired when someone enters the log in screen.
Let me know if that works for you.
This is highly useful – say if I wanted to create links from the images though having already made the lightbox activate on rollover – how could I avoid just going to the image page due to the which is needed to point to the image in order to generate the picture in the lightbox?
just to reiterate on that last comment .. that fact that the page is set on a variable display that can go into a million pixals effects the scrolling control in the right side bar (which I prefer to use over the mouse) in terms of usability.
Maybe it’s just me
Sick site though, by far the best content with a nice clean feel
Hi,
This is a brilliant tutorial and I have got it all working for me except for when I click on an image it opens it in a new window rather than the lightbox. Any ideas what is causing this error?
I will definately be a regular visitor to this website
Great tutorial Jeff I had fun doing it. I have a question, do you have a source that provides a good explanation of what “mysqli” is and how I would replace “mysql_connect” with it?
I have had some of the same problems with the mysqli that have been stated.
Changing it means you have to change well quite a few things…. I actually took out the cookie verification and bypassed the login completely as i don’t need it I’m testing this local and will implement something else when i go live.
database.php
$db_name = “db”;
$db_server = “localhost”;
$db_user = “root”;
$db_pass = “***”;
$mysqli = mysql_connect($db_server, $db_user, $db_pass);
if (!$mysqli)
{
die(‘Could not connect: ‘ . mysql_error());
}
mysql_select_db($db_name, $mysqli);
and the getPhotos.php
require ‘database.php’;
$result = $result = mysql_query(“SELECT * FROM photos”);
if ($result) {
echo ” \n”;
while ($row = mysql_fetch_array($result)) {
$title = $row['title'];
$src = $row['src'];
$id = $row['id'];
echo “ \n”;
echo “$title \n”;
echo ” \n \n”;
}
echo “”;
}
John
This tutorial has helped me immensely
Thank you very much Jeffery.
very good tutorial, congratulations for all videos published in the site….
Did part 2 of this ever happen? I don’t see one on NETTUTS!
Jeffery, I think your monitor colors may be off. You mention that the background color of the body and container are browns. The hex values in the CSS look grey on my monitor. Hope it’s a company XP machine
:P
I’m having trouble getting what you got at the 26:00 minute mark. I downloaded the source file and followed along with you, created the database and everything. All I get when I preview in the browser is the title “My Photos click on the test to change the title” and the logout button. It doesn’t seem to be fetching the photos. I am very new to php and databases. Any suggestions?
wow, awesome tutorial actually i m in a learning phase of php now but i can easily understand this tutorial great work.. hats off…
PLEASE HELP I AM GETTING THE FOLLOWING ERROR M NEW TO PHP
Warning: mysqli::mysqli() [function.mysqli-mysqli]: (28000/1045): Access denied for user ‘root’@'localhost’ (using password: NO) in C:\xampp\xampp\htdocs\mpw\confirm_login_credentials.php on line 7
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user ‘ODBC’@'localhost’ (using password: NO) in C:\xampp\xampp\htdocs\mpw\confirm_login_credentials.php on line 9
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\xampp\xampp\htdocs\mpw\confirm_login_credentials.php on line 9
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user ‘ODBC’@'localhost’ (using password: NO) in C:\xampp\xampp\htdocs\mpw\confirm_login_credentials.php on line 10
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\xampp\xampp\htdocs\mpw\confirm_login_credentials.php on line 10
Warning: mysqli::query() [function.mysqli-query]: Couldn’t fetch mysqli in C:\xampp\xampp\htdocs\mpw\confirm_login_credentials.php on line 14
Warning: mysqli_error() expects exactly 1 parameter, 0 given in C:\xampp\xampp\htdocs\mpw\confirm_login_credentials.php on line 14
hey guys how do we insert pictures?what would the form look like?
alright, so i was using this tut as a major reference point…but its giving me some problems in safari. when i tab off to update the title, it doesnt work.
works great in Chrome and Firefox though…
i think the problem is with the ajax.
any help???
actually scratch that, i actually tried to TAB off this time and it works just fine. i used ENTER for the rest and it worked…but for safari it didnt. little fork on my road, but eh, not a biggie
Very, very excellent tutorial. I’m an experienced WinForms and backend services developer curious about web development and your tutorial was spot-on what I needed. My first look at jQuery–I love that fluent interface! Starting with nothing but stuff most people already know (like CSS) and working to a finished page was perfect. About an hour is the right length, too. Thanks!