Copyright Notice

This article is Copyright (c) Al Andersen, http://www.alandersen.com/

You are free to use this code in your programs providing you adhere to the most current version of the GNU General Public License (GPL).


Disclaimers And Caveats

This code is provided, as is. I don't guarantee that it will work, and I don't guarantee that it will not destroy data on your hard disk. Use at your own risk! Backup important files!

This code requires that a server version of PHP is installed on your computer, with MySQL and/or PostgreSQL database functionality compiled in.

This program requires that a web server, capable of running PHP scripts, is installed on your computer.

This program requires access to a MySQL or PostgreSQL database.


PHP Image And Website Tracking Program

I showcase my photos on my website. However, I also like to share my photos on other websites, to exchange ideas and artistic vision with other communities, to receive critique and feedback to better my images and skills, and because it makes good business sense to advertise my work elsewhere.

The problem I find in posting images on other websites is that I lose track of which files I've placed where. I don't want to bore people with stuff they've already seen, and I certainly don't want to spend additional time and embarrassment uploading, tagging, and describing a photo that I've already put on a particular site. To complicate matters, some of the websites use forums that have scrolling content, where the posts eventually scroll off into the great big bit-bucket, never to be seen again and leaving me to wonder, "Did I post this one here already, or not?"

I need a simple utility that will help me track the photos I've uploaded and the sites I've submitted them to. I don't need anything sophisticated, just something that says, "Yes, I've put this photo on that website." After thinking about it, my requirements for this program are:

  • to run in my web browser so I can pull it up while I'm surfing the web;
  • run on my local web server or on my actual web site so I can access it on- or off-line;
  • easy to modify so I can add or remove web sites;
  • easy to use so I can add and remove photos,
  • and to toggle on or off whether a website has an image.

The program wasn't that hard to code, and because it is so simple to use, I've decided to share it for I think it might be useful to other photographers. So, let's go through the code one step at a time so you can see how it works.

Installation

There are three parts to the program: an SQL script to create a database table, a PHP database module to take care of the database functions, and a PHP page module to store, fetch, and display the information.

  1. The program is designed for MySQL, but it will also work on PostgreSQL with two minor changes: change the c_id definition in the SQL script that creates the database table (see step #2 below), and include the PostgreSQL PHP database module in lieu of the default MySQL database module (see step #3 below).
  2. Download and run the SQL script to create the database table. How you do this is up to you. I prefer the command line. For MySQL, logon to your database mysql -u username database -p and then type \. name_of_sql_file. For Postgres, logon to your database psql -U username database -W and then type \i name_of_sql_file. See the DATABASE section below if you're using PostgreSQL for instructions on how to change the definition for the c_id column.
  3. Use the appropriate database module from the PHP Database Abstraction Module tutorial. Save it as db.php and point to it in the require_once line at the top of the PHP page module source file. Get the correct file for your database: dbmysql.phps for MySQL or dbpgsql.phps for PostgreSQL
  4. Change your PHP database module $GLOBALS to use the appropriate connection data to your database (see the DATABASE section below).
  5. Download the PHP page module and save it as anything you like, as long as the extention is .php.
  6. Change the require_once line at the top of the PHP page module to point to where your PHP database module is.
  7. Change your PHP page module $GLOBALS to point to the database table you created in #2 above (see the DATABASE section below). You'll find them after the require_once line in #6 above.

How It All Works

SQL And Database Module

The first thing you need to do is create a table in your favorite database to store your information in. Here's an SQL script (see attachments below) to create the table for storing this data.

	CREATE TABLE myTable
	(c_id integer NOT NULL AUTO_INCREMENT,
	c_image varchar(32) NOT NULL default '',
	c_deviant integer NOT NULL default 0,
	c_flickr integer NOT NULL default 0,
	c_natpho integer NOT NULL default 0,
	c_natscape integer NOT NULL default 0,
	c_pbase integer NOT NULL default 0,
	c_photonet integer NOT NULL default 0,
	c_photoport integer NOT NULL default 0,
	c_smugmug integer NOT NULL default 0,
	PRIMARY KEY (c_id));

Change the table name to whatever you want and then run this script in a current database, or create a separate one to hold the table. The choice is yours. Just remember what database you put it into for you'll need that information later in order to connect to the database.

The script is MySQL specific. If you prefer PostgreSQL, simply change the column definition for c_id to

		c_id serial,

The c_ prefix for the column names is a convention that I use so I can tell if I'm refering to a database column name in my code. Feel free to remove it, but be sure you also make the appropriate removals in the attached PHP page module.

The major drawback with this table definition is that for any new site, you need to add a new column, and if you remove a site, you need to remove its column. The program does not make these modifications for you, though it shouldn't be that hard to make some wrapper functions around the following database directives to add this functionality:

	ALTER TABLE myTable ADD COLUMN c_whatever integer NOT NULL default 0;
	ALTER TABLE myTable DROP COLUMN c_whatever;

Once the table is created, you need to add the necessary code to let PHP access your database. That's done in the aforementioned PHP database module. You'll need to modify the require_once line at the top of the PHP page module to reflect where you've saved the PHP database module (db.php as cited in installation instruction #3, above).

	if (!function_exists ("db_query"))
	{
		require_once ("/full_path_to_your_web_directory/db.php");
	}

Now you need to set up some global variables for accessing your database table.

	$GLOBALS["db_table"] = "myTable";

	$GLOBALS["db_name"]       = "myDatabase";	// Database name
	$GLOBALS["db_pwd"]        = "myPassword";	// Database password
	$GLOBALS["db_server"]     = "MyIP_or_localhost";	// Database server.
	$GLOBALS["db_uid"]        = "MyUserID";		// Database user id

The first two globals are for your database table and are found near the top of your PHP page module. Change $GLOBALS["db_table"] to point to the database table you created. The rest of the listed variable can be found near the top of the PHP database module. Change them so that they provide the data necessary to connect to your database.

Page Module

As noted above, the PHP page module takes care of storing, fetching, and displaying your data. The following are the functions used to fetch and store the data:

	function data_get()
	function data_put ($p_image, $p_site)

Note that the first function takes no parameters, whereas the second function takes the name of your image file and the name of the website. When retrieving data, you are retrieving all of it, so there's no need to pass any additional information to the function. When you store data, you need to know what image you're referencing, and what website you're flagging.

These are simple fetch and store functions. The code for the functions contains detailed comments regarding how they work. The only thing you should note is that no data is stored if the image parameter for the data_put function is empty.

The page markup for displaying the data is also quite simple. It's split up into two functions, one to fill a drop-down select list and one to show the page.

	function show_page ($p_image="", $p_site=0, $p_message="")
	function options_get ($p_id_option)

The options_get function constructs a list of options for a drop-down select list. If you pass it an option ID it sets that option as being selected. The options list is constructed from an array, and it is important. The $GLOBALS["sites"] variable holds the array, and it's near the top of the PHP page module.

	$GLOBALS["sites"] = array (
						0=>"Remove Image",
						2=>"Deviant Art",
						3=>"Flickr",
						4=>"Nature Photographers",
						5=>"NatureScapes",
						6=>"PBase",
						7=>"PhotoNet",
						8=>"PhotoPortfolios",
						9=>"SmugMug"
					);

The show_page function creates an input form for storing our data, and a table for displaying it. The function takes three parameters: the name of the image, the ID of the website, and an informational message to display what actions the program performed. These parameters all have default values which will be used if you pass nothing to the function.

The input form is displayed at the top of the page and it is very simple. The following code is part of a string, so all the quotes are escaped.

	<form name=\"f_images_online\" action=\"{$_SERVER["PHP_SELF"]}\" method=\"post\">
		Image: 
		<input name=\"f_image\" type=\"text\" size=\"20\" maxlength=\"32\" value=\"$image\"/> 
		Site: 
		<select name=\"f_site\">
			{$options}
		</select> 
		<input name=\"f_submit\" type=\"submit\" value=\"Submit\" />
	</form>

There is a text box for the image name and it is limited to the same number of characters (32) found in the database column definition for c_image. You can use whatever you want for the image name. It can be a title, a file name, an ID, whatever, as long as it doesn't exceed 32 characters.

A drop-down select box contains the names of the websites, except for the first entry which is to remove the image which is the default action for this form! Make sure that you understand that if you only enter an image and click on the [Submit] button that you will delete the image from the database. Once deleted, there is no way to recover the information! There is no "Are you sure?" prompt. If you need one, you'll have to code it yourself. So, pay attention to what you select!

The form remembers what data was passed to it, meaning you don't have to re-enter the image name or re-select an option from the drop-down list after every form submission.

So, how does the form work? You have to enter an image name. As mentioned above, nothing is saved to the database if you don't provide a name for the image. For every submission, the form displays an informational message immediately above the form telling you what it did in the database. If you supply an image that is not in the database, that image will be added to the database. If you supply an image that is already in the database, the selected website is toggled on or off.

Let's work through some examples.

  1. You add "0001" for your image name and select Flickr as the website.
  2. The image doesn't exist. The image is added to the database. The program tells you it added the image and that it updated Flickr to "Yes".
  3. You again submit "0001" but this time select the PhotoNet website.
  4. The image already exists. The program tells you that it updated PhotoNet to "Yes".
  5. You again submit "0001" and indavertantly select Flickr again.
  6. The image already exists, and Flickr is already marked. The program tells you that it updated Flickr to "No".
  7. You again submit "0001" and select "Remove Image" instead of choosing a website.
  8. The image already exists and is deleted. The program tells you that it deleted image "0001".

How do you know if an image is already in the database? Use your browser's search feature to see if it's listed on the page. In Firefox, press Ctrl-F. I don't use IE, so you're on your own with that browser. As far as the program is concerned, it doesn't care. It adds images if they're not in the database, updates the website info if the image already exists, or deletes the image data if explicitly told to do so.

The data itself is fetched all at once from the database and then displayed in a table. This means that after a while the data will become large enough to cause the page to scroll. Usually, when a page scrolls a table's column headers will scroll off the screen. This is annoying if you need to look at data near the bottom of the table and no longer have any idea what the column is for that a data element resides in. We fix this problem using in-line cascading style sheets.

Some CSS has been used on the table to size the columns, color code the columns, and to prevent the column headers from scrolling off the screen. This meant that I had to use <thead> and <tbody> elements for the table layout and style the element to contain a scroll-bar. This works well in Firefox. It may not work in other browsers. If it doesn't work for you, please do some research to try and fix it. I don't have access to anything except linux and Firefox, so I can not make this fix for you, though I will be more than glad to add any fixes to the code that you might come up with.

Task Switcher

At the bottom of the PHP page module is a task switcher. It runs the code one way if you do a GET, and a different way if you do a POST. Clicking the [Submit] button will run the POST part of the code which handles putting the data into the database. A redirect is then made to re-call the page and run the GET part of the code. The GET part is also run when you call the program the first time or refresh the page. The comments in the PHP application source code explain how/why this works the way it does.

And that wraps up this tutorial. I've been using the program for several weeks now and I like it. It's made my life easier in tracking where I've shared my photos and is very simple to use, which is what wanted. Like all programs, I'm sure that I'll find new features to add to it, and I'll post them here when I do.