Wouldn't it be great to create a photo gallery to showcase all that bunch of jpegs that we collect on our hard drives over the time? Utilizing PHP and MySQL enables us to create such photo gallery using only one page of code! Sounds good? Let's get started.

.

Database Creation
Actually, we are only going to create a table since the database you probably already have.

Let's first dust off all your jpegs and place them in one single directory on your server. Once you're done, all that left is to build one mySQL table that contains information about our photos.

Start up MySQL and do the following:

    CREATE TABLE photo_gallery (
        id int not null auto_increment primary key,
        title varchar(30) not null,
        photo varchar(30) not null
    );

Confused yet? Patience, let me go over it and the things will soon become to you very clear:

Table name
We called our table photo_gallery. Actually, you can name your table whatever you wish although we've chosen it to be descriptive so it reflects the best our table's purpose.

ID
As in every table design we need something to be unique so it helps us finding the information in the table faster and more efficiently. In our case we create a unique field called id. Note, that we made it integer and have also added a Primary Key parameter because mySQL looks up integers quicker then what it would do with text strings. Nevertheless, id is also "auto incremented", meaning if the first entry is 1 then the next one will be 2. Good start, no?

title
The "Title" is a name or a simple description of the photo as we want remember it later on.

photo
Yup, this is and the actual file name of the image. We surely can't avoid this one.



Filling it up
With this we created a simple table that will store all our information and, as it logically happens, we now must fill it up with some data.

    INSERT INTO photo_gallery values(NULL,"Barn Photo","barn.jpg");

Check out the info I'm inserting into my table. The first value is
NULL. I'm using NULL for the value of id. NULL has no value, and will create trigger the table give it what it hold there as default - in this case the current value of id plus 1.

Our image title is "Barn Photo" and its file name is barn.jpg. In theory, you could use any type of image as long you tailor your script to support that file format. But, since jpeg is our case we will stick to it as the example for this tutorial.

Now I'm going to dump more data into my gallery table. I just clone over and over the previous line a few times into MySQL changing them accordingly to what files I have. (No, we won't cover here how to do that automatically)

    INSERT INTO photo_gallery values(NULL,"Tree Photo","Tree.jpg");
    INSERT INTO photo_gallery values(NULL,"House Photo","House.jpg");
    INSERT INTO photo_gallery values(NULL,"Sister Photo","Sister.jpg");
    INSERT INTO photo_gallery values(NULL,"Cat Photo","Cat.jpg");
    INSERT INTO photo_gallery values(NULL,"Girlfriend Photo","Girlfriend.jpg");
    INSERT INTO photo_gallery values(NULL,"Wife Photo","Wife.jpg");

This is how our table looks after doing all those INSERTs:

    mysql> SELECT * FROM photo_gallery;
    +----+------------------+----------------+
    | id | title            | photo          |
    +----+------------------+----------------+
    |  1 | Barn Photo       | barn.jpg       |
    |  2 | Tree Photo       | Tree.jpg       |
    |  3 | House Photo      | House.jpg      |
    |  4 | Sister Photo     | Sister.jpg     |
    |  5 | Cat Photo        | Cat.jpg        |
    |  6 | Girlfriend Photo | Girlfriend.jpg |
    |  7 | Wife Photo       | Wife.jpg       |
    +----+------------------+----------------+
    7 rows in set (0.00 sec)

Our Gallery Killer-App!

So here we go: gallery.php - our famous one-page script that will show off the photos.
I will be commenting each line from now on. So read carefuly!

<?php


    // Connects to sql server and logs in with username and password
    // if successful, then the script moves on to displaying the gallery.

    $connect=@mysql_connect("localhost","username","password")
      or die("couldnt connect to sql server");
      
    $db= @mysql_select_db("database_name") or die ("couldnt select database");

    // Ive set the number of photos per page, in this case I only have 7 photos
    // so I want to display 1 photo per page.
     
    $per_page = 1; 

    // Selects all of the data from database 

    $sql_text = ("SELECT * from photo_gallery ORDER BY id DESC");  

    // Sets page number, if no page is specified, it will create page 1  

    if(!$page) {  
        $page = 1;  
    }  
    $prev_page = $page - 1;
    $next_page = $page + 1;


    $query = @mysql_query($sql_text); 

    // Sets up specified page 
     
    $page_start = ($per_page * $page) - $per_page; 

    $num_rows = @mysql_num_rows($query);  

    if($num_rows <= $per_page) {  
        $num_pages = 1;  
    }
    else if (($num_rows % $per_page) == 0) {  
        $num_pages = ($num_rows / $per_page);  
    }
    else {  
        $num_pages = ($num_rows / $per_page) + 1;  
    }  
    $num_pages = (int) $num_pages;  

    if (($page > $num_pages) || ($page < 0)) {  
       error("You have specified an invalid page number");  
    }  

    $sql_text = $sql_text . " LIMIT $page_start, $per_page";  
    $query = mysql_query($sql_text); 

    
?>

<!-- This starts the web page with HTML and PHP embedded. -->
<!-- It also counts how many photos are present in the photo gallery. -->

<title>My Photo Gallery</title>
<body bgcolor="#ffffff">
<blockquote> There are currently<font color="red">

<?php
 echo "$num_rows"; 
?>

</font> &nbsp;photos in My Photo Gallery<p>

<!-- It loops through the rows and obtains the data that we created in the table. -->

<?php


    while ($result = mysql_fetch_array($query)) {
        $title= $result["title"];
        $photo= $result["photo"];


        // Using HTML "img" tags for the photos, it selects the photo by it's "id" number

        echo "$title<br>";
        echo ("<img src="$photo" border=0><P><HR width=400 align=left>");
    }

    // This displays the "Previous" link  

    if ($prev_page)  { 
        echo "<a href="$PHP_SELF?page=$prev_page">&lt;&nbsp;Prev</a>";  
    } 

    // This loops the Pages and displays individual links corresponding
    // to the photos. 


    for ($i = 1; $i <= $num_pages; $i++) {  
        if ($i != $page) {  
            echo " <a href=$PHP_SELF?page=$i>$i</a>";  
        } else {  
            echo " $i ";  
        }  
    }  

    // This displays the "Next" link.

    if ($page != $num_pages) {  
       echo "&nbsp;|<a href="$PHP_SELF?page=$next_page">&nbsp;Next&nbsp;&gt;</a>" ; 
    } 


?>

</blockquote>
</body>
</html>


I hope this helps you get your feet wet by utilizing a database and images. Other ideas might be, using thumbnail images instead of larger images and when the viewer clicks on the thumbnail it takes them to a larger image. But all these inventions are up to you (let us know them, though)

Joel Philip,
a web developer and graphic artist from Southern California.