Write to UsCreating a Simple Photo Album with PHP and MySQL (Part 1)
Introduction
The Database
Getting Access to the Database
The Photo Album
Online photo albums are everywhere these days. Some of them are dedicated to landscapes, others are dedicated to celebrities, and the list goes on and on. This tutorial is the first in a series of tutorials that will teach you how to use PHP and MySQL to create a photo album that will allow you to upload new pictures, delete old pictures, and even update any information linked to your pictures.
The first version of our photo album is a shadow of what the last version will be. You can't update the pictures, and you can't insert new pictures, and you won't be able to click on any links. In other words, this photo album ia as simple as a photo album can get, and that makes it easier for us to understand what's going on.
The first thing we need to create our simple photo album is a MySQL database. The name of the database will be Landscapes, and the name of its only table will be pictures. If you are using Xampp or Appserv, you won't have any trouble creating a new database. If you want to create the database using MySQL syntax, here's what you need to type:
create database Landscapes;
Now, this database needs a table. The syntax for creating the table is this:
`id` int(5) NOT NULL auto_increment,
`filename` char(25) NOT NULL,
`name` varchar(50) NOT NULL,
`width` int(3) NULL,
`height` int(3) NULL,
`description` varchar(250) NULL,
PRIMARY KEY (`id`)
)
If you are using PHPAdmin or a similar script, and you want to create the table pictures, click on SQL, copy/paste the SQL Query shown above and then click on Go. If you want to add pictures to that table use this SQL script:
INSERT INTO `pictures` VALUES (2, 'landscape_2.jpg', 'The Snow in the Distance', 300, NULL, 'A picture of a mountain');
INSERT INTO `pictures` VALUES (3, 'landscape_3.jpg', 'A Clear Sky', 300, NULL, 'The Ultimate Landscape');
INSERT INTO `pictures` VALUES (5, 'landscape_4.jpg', 'Black and White Clouds', 300, NULL, 'The Mountain Landscape');
INSERT INTO `pictures` VALUES (6, 'landscape_5.jpg', 'Blue Land', 300, NULL, 'The Fifth Landscape');
INSERT INTO `pictures` VALUES (7, 'landscape_6.jpg', 'The Rainbow Behind the Mountain', 300, NULL, 'The Landscape Mountain');
Table pictures has six fields. The first field is id, and it will be the primary key. Unlike other fields, id cannot be modified by you. Each time you insert a new picture file into the database, the id number will be auto-incremented. A primary key is similar to a social security number. Each picture will have a unique id number which can be used to access that picture.
The second field is filename. It is unlikely that a filename will have more than 25 characters, so char(25) will do the job. NOT NULL means this field needs to have a value. If this field is empty, no picture will appear on the page. Name is also set to NOT NULL because every picture needs a name. Remember, this is a photo album.
Unlike filename, name is of type varchar. There isn't much of a difference between char and varchar. However char can store up to 30 characters, so it isn't good enough for a long picture name. Varchar, on the other hand, could store up to 255 before MySQL 5.0.3, and in later versions, it can store up to 65,535.
Unlike previous fields, width, description, and height are allowed to have null values. A few users may not see the need to change the width or height of a picture file, and they may not have a description for their pictures. A picture can appear on a webpage without any of these things.
Getting Access to the Database
PHP has several functions you can use to interact with a MySQL server. Start by opening a connection to a server using the function mysql_connect(). For example, mysql_connect('example.net:3300', 'user', 'password'); would allow you to to connect to a server in example.net through the 3300 port. You can't get access to the server without the proper user and password. However, you may have the MySQL server in your own computer, which makes things a lot easier. Assuming that's the case, create a file called access_db.php and paste this in the file:
if (!defined('prover'))
{
die("File not found");
}
mysql_connect("localhost","root");
mysql_select_db("landscapes");
?>
The first three lines may seem a little confusing. The function defined checks whether a constant exists. The variable prover is not defined in the PHP script, so, if you try to access access_db.php directly, all you'll see are the words "File not found" right on top, preventing people from looking at your code and discovering what your login/password are, if any. In this case, mysql_connect lacks a password. That's because there isn't one in the local server. The second function we use is mysql_select_db, which allows us to select the database landscapes. You may wonder why we don't make this small script part of a bigger script. Trust me, you don't want a PHP script containing a login/password to be part of an accessible script.
| $items['filename']<? define('prover', TRUE); include_once("access_db.php"); ?> <html> <head> <title>Landscape Pictures</title> </head> <body> <h1>Landscape Pictures</h1> <p>These are some pictures I found while surfing the internet. None of them belong to Carbotek. They are part of a simple tutorial on how to use databases.</p> <? $if = 2; $selec_t = mysql_query("Select * From pictures"); ?> <table style="background: #ffa; border: #000 1px solid;" border="0" cellpadding="5" cellspacing="5" align="center"> <? while($items = mysql_fetch_array($selec_t)) if ($u == 0) echo "<td valign='top'><img src='images/".$items['filename']."'"; if ($items['width'] != "") if ($items['height'] != "") if ($items['description'] != "") echo "/>"; echo "</td>"; if ($u == 3) if ($u != 0) ?> </table> |
The first thing we do is defining the variable prover as TRUE, so we can include the script access_db.php without any trouble. If we lack the file access_db.php, there's no connection to the database server. What follows is basic HTML. Then we see another part of the script. The function mysql_query, as its name implies, sends a query to the database. The * stands for "everything", meaning all the fields in the table pictures will be stored in the variable $selec_t. We create a table that will contain all the pictures. Inside the table, you'll see the last part of the PHP script. And inside the while loop, there's a function called mysql_fetch_array. Notice that $select_t is enclosed by the function's brackets. What this function does is turning every row in $select_t as an associative array called $items. Every line contained in the while loop will be executed as there are rows in table pictures that can be read. Before the while loop, the variable $u is given a value of 0. Inside the loop, a <tr> will be echoed if $u equals 0. Then the value of $u is increased by 1. The purpose of this is giving the HTML Table rows that will have three pictures each. Scroll down, and you will see that $u is set to 0 once it reaches a value of 3. Through the function mysql_fetch_array, we turn every row coming from $selec_t into an array, and that array will be stored in $items. If you want to get the value of every field in the pictures table, you will need these five variables: $items['id']: if you use this, you will get the id of the picture/row. It is not used in the Photo Album script. Notice that in the script we have this line: $items['filename']: this one will fetch the filename. echo "<td valign='top'><img src='images/".$items['filename']."'"; Since we are dealing with PHP here, it follows that the server will turn this line into HTML. Thus $items['filename'] will become a filename. For a picture to appear on the webpage, you must upload them all into a directory called images. |
$items['name']: using this, you can get the name of the picture.
$items['width']: as you already know, the value of width can be null. That's why inside the while loop, you have if ($items['width'] != "") making sure the value will not be null. If it isn't, then the picture will have the width value assigned to it in the pictures table of the landscape database.
$items['height']: this is not different than $items['width'], but it is used to give the picture a height. If it is null, the picture will have the height given to it by the browser.
$items['description']: this one can also be a null value.
Once there are no more rows in the pictures table, you need to close the last row of the HTML Table, if it is opened. Inside the while loop, you have these lines:
if ($u == 3)
{
echo "</tr>";
$u = 0;
}}
The problem here is that $u may not equal 3 by the time the last row of the database table is fetched. In a case like this, a few extra lines of coding are required to close the table, which explains the last four lines of the script. Notice that the server will put a </tr> tag on the webpage only if $u is not 0.
The rest is basic HTML. Once you are done, create a directory called images and put it inside the directory where the PHP files for the Photoalbum are. Enter the images directory and upload pictures whose names are the same as those stored in the pictures table (you could save some time by downloading the ones I used), and you are all set.