Tag Archives: php

Update 2/22/2010: It looks like changing .htaccess is no longer necessary. After you select PHP 5.x, your site will begin using version 5.2.5 without any further configuration.

The following applies to older domains. As of early 2009, newly purchased linux hosting plans are running PHP 5.2.8, while older plans, once updated, only go up to PHP 5.2.5. I’ve had Ardamis.com hosted at GoDaddy since 2005, and quite awhile ago I thought I had upgraded to PHP version 5 from 4.3.11, but tonight I happened to check with phpinfo and found I was still on version 4.

In the unheard of ten minutes that I was on hold waiting for technical support, I figured out how to really run my pages on PHP 5.x (in this case, 5.2.5).

Log in and go to your Hosting Control Center. You must be running Hosting Configuration 2.0 to go any further, so if you haven’t touched your domain in years, do that first.

Click on Content, then Add-On Languages. Next to PHP Version, select PHP 5.x and click Continue. You’ll get a message that “Changing to PHP 5.x may make your PHP files run incorrectly.” Highly unlikely these days, but OK, you’ve been warned. Notice, too that it says “PHP 5.x will be activated“. Click Update.

It may take awhile for this change to be processed by the server, but once your Account Summary is displaying PHP Version: 5.x, it’s time for the really important part.

You see, you’ve only made PHP 5.x available at this point. Your *.php files are still running in 4.x. Go ahead and check phpinfo again.

Now, you could simply edit .htaccess to change the extensions, like so:

AddHandler x-httpd-php5 .php
AddHandler x-httpd-php .php4

More details at http://help.godaddy.com/article/1082

But if you’re squeamish about changing .htaccess yourself, there’s another way to set 5.x to be the default handler for *.php files. All the following does, strangely enough, is to add the AddHandler x-httpd-php5 .php to the beginning of your .htaccess file.

Back in the Hosting Control Center, click on Settings, then File Extension. If the change to 5.x has been completed, you’ll see at the bottom of the available extensions list, “Extension -> .php | Runs Under -> PHP 5.x” If it’s not there, stop here and come back in an hour or so.

Click on Custom Extensions at the left. This should be empty, with a message stating “No custom extensions have been created.”

Click on Default Extensions and then click on the Edit button (it looks like a piece of paper and a pencil) to the right of .php | PHP 5.x. Click on Continue.

Click again on the Custom Extensions button on the left, and you should now see “Extension -> .php | Runs Under -> PHP 5.x”. Check your phpinfo page one more time, and it should report PHP 5.x.

It’s unfortunate we even have to do this for our older domains, but I asked the tech support guy if I could somehow get on to PHP 5.2.8, and he said nope, that the newer servers have the more recent version but the older servers are stuck back in 2007.

A little over a year ago, I wrote a post about a PHP script I had created for protecting a download using a unique URL. The post turned out to be pretty popular, and many of the comments included requests to extend the script in useful ways. So, I’ve finally gotten around to updating the script to generate multiple URLs (up to 20) at a time, to allow different files to be associated with different keys, and to allow brief notes to be attached to the download key.

I’ve also added a simple page that prints out a list of all of the keys generated the date and time that each key was created, the filename of the download on the server that the key accesses, the number of times the key was used, and any attached note. This should make it easier to generate gobs of keys, drop them into an Excel spreadsheet, and help the files’ owner keep track of who’s getting which file, and how often.

The scripts themselves are a little more involved this time around, but the general idea is the same. A unique key is generated and combined with a URL that allows access to a single file on the server. Share the URL/key instead of the URL to the file itself to allow a visitor to download the file, but not to know the location of the file. The key will be valid for a certain length of time and number of downloads, and will stop working once the first limiting condition is met. This should prevent unauthorized downloading due to people sharing the keys.

How it works

There are six main components to this system:

  1. the MySQL database that holds each key, the key creation date and time, the maximum age of the key, the number of times the key has been used, the maximum times the key may be used, the file associated with the key, and the note attached to the key, if any
  2. a generatekey.php page that generates the keys and outputs the corresponding unique URLs
  3. a download.php page that accepts the key, checks its validity, and either initiates the download or rejects the key as invalid
  4. a report.php page that returns all of the data in the database
  5. a config.php file that contains variables such as number of downloads allowed, the maximum allowable age of the key, and the filenames of the downloads, along with the database connection information
  6. the .zip file(s) to be protected

The files

The files, along with two example downloads, are available for download as a .zip file.

Download the protecting multiple downloads PHP script

The MySQL database

Using whatever method you’re comfortable with, create a new MySQL database named “download” and add the following table:

CREATE TABLE `downloadkeys` (
  `uniqueid` varchar(12) NOT NULL default '',
  `timestamp` INT UNSIGNED,
  `lifetime` INT UNSIGNED,
  `maxdownloads` SMALLINT UNSIGNED, 
  `downloads` SMALLINT UNSIGNED default '0',
  `filename` varchar(60) NOT NULL default '',
  `note` varchar(255) NOT NULL default '',
  PRIMARY KEY (uniqueid)
);

How to use the scripts

The scripts require a little setup before they’re ready to be used, so open config.php in your text editor of choice.

Change the values for $db_host, $db_username, $db_password, $db_name to point to your database.

Set the variable $maxdownloads equal to the maximum number of downloads (actually, the number of page loads).

Set the variable $lifetime equal to the keys’ viable duration in seconds (86400 seconds = 24 hours).

Set the variable $realfilenames to the real names of actual download files on the server as a comma-separated list (this is optional; you can also use a single filename or just leave it as empty double-quotes: “”). If you have more than one file to protect, enter the names as a comma-separated list and the script will create a drop-down menu as the Filename field. If you leave the variable blank, the form will display an empty input box as the Filename field.

Set the variable $fakefilename to anything – this is what the visitor’s file will be named when the download is initiated.

I would strongly recommend renaming generatekey.php, as anyone who can view it will be able to create unlimited numbers of keys, and worse, they’ll be able to see the filenames (if you set them in config.php). I would also recommend that the directory you put these files into, and each directory on your site (/images, /css, /js, etc.), contain an index.html file. This is a simple security measure that will prevent visitors from snooping around a directory and viewing its contents (though access to the directory contents is usually prohibited by a setting on the server).

Place all the PHP scripts and your .zip file(s) into the same directory on your server.

That’s all there is to it. Whenever you want to give someone access to the download, visit the generatekey.php page and fill out the form. It will generate a key code, save it to a database, and print out a unique link that you can copy and paste into an email or whatever. The page that the unique link points to checks to see if the key code is legitimate, then checks to see if the code is less than X hours old, then checks to see if it has been used less than X times. The visitor will get a descriptive message for the first unmet condition and the script will terminate. If all three conditions are met, the download starts automatically.

Errors and issues

Note: The download will not initiate automatically, and will actually be output as text on the page, if the download.php page is changed to send headers or any output to the browser. Be careful when making modifications or incorporating this script into another page.

Check the HTTP headers (Google for an online service that does this, or install the LiveHTTPHeaders Firefox plugin) of the download link. If the script is working correctly, you should see Content-Transfer-Encoding: binary and Content-Type: application/octet-stream in the headers. If you’re getting a page of text instead of the zip file, you’ll probably see Content-Type: text/html.

Example HTTP headers for a correctly working download

If the script is working correctly, the HTTP headers will look something like this:

HTTP/1.1 200 OK
Date: Sun, 20 Jun 2010 13:31:50 GMT
Server: Apache
Cache-Control: must-revalidate, post-check=0, pre-check=0, private
Content-Disposition: attachment; filename="bogus_download_name.zip"
Content-Transfer-Encoding: binary
Pragma: public
Content-Length: 132
Keep-Alive: timeout=15, max=100
Connection: Keep-Alive
Content-Type: application/octet-stream

I’ve finally opened a Twitter account, so you can follow me at http://twitter.com/ardamis. As a social experiment, it’s interesting to watch and discuss, but I haven’t really participated much. On the other hand, I’m very interested in the phenomenon of URL shortening. So, without too much trouble, I put together yet another URL shortening service: Minifi.de (‘minified’, in the jargon). It does pretty much the same thing as tinyurl.com, bit.ly and is.gd – you enter a long URL and it returns a shorter one. I’m about 50% done with the API (it works, so long as the URL valid), so if anyone knows of any clients that allow the user to specify a shortening service, I’d like to test that functionality. I have plans to make it account-based, so that you can track usage statistics and such, but that will only happen after I’m confident that the thing will survive in the wild.

So, feel free to give it a whirl, but know that it’s still in development and that I’ll probably have to wipe the database a few more times before I get it just right.

Well, I’ve been sitting on this adaptation of my Broadway Plogger theme for too long. It hasn’t been fully tested, but it seems to work. It’s a dark theme that uses the popular Lightbox 2 JavaScript script to open medium-sized images over the thumbnails page. This means that there is no getting to the full-sized image.

Plogger 3 theme: Broadway screenshot

Anyway, if you’ve been looking for a Lightbox 2 supporting Plogger theme, please give this one a try.

Download the “Broadway 2 + Lightbox” Plogger theme

Update 12/19/09: I’ve updated the theme to be compatible with the recently-released Plogger v.1.0. If you’re running an earlier version, I would recommend that you update to the current stable release.

I was installing PHP 5 on an IIS 6 server when I ran into what turns out to be a pretty common problem. PHP appeared to be installed correctly, but browsing to any page with a .php extension returned a 404 Page Not Found error. While the steps below fixed this for me, I had to piece them together from a few different sources, and a number of other suggestions (like copying the php.ini file to C:/WINDOWS/) didn’t work and were not necessary.

Open your IIS management console at C:\WINDOWS\system32\inetsrv\iis.msc.
Drill down to your web site, right-click and select Properties.
Select the Home Directory tab, then click on the Configuration button.
Select the Mappings tab. If you don’t see a .php extension listed, click the Add button. Browse to the PHP 5 DLL (which may be at C:\Program Files\PHP\php5isapi.dll). Type .php into the Extension field and leave everything else at the default values. Click OK. The extension and executable path will be filled out and under Verbs you should see “All”.

I should point out that I didn’t have anything listed under the ISAPI Filters tab.

Stop and restart your IIS server and browse to a .php file. (To restart your IIS server, open the IIS management console, right-click the local computer in the left pane, hover on All Tasks and choose Restart IIS.) Chances are, you’re no longer getting the 404 error, but are now seeing a 403.1 message, like:

The page cannot be displayed
You have attempted to execute a CGI, ISAPI, or other executable program from a directory that does not allow programs to be executed.

Please try the following:

* Contact the Web site administrator if you believe this directory should allow execute access.

HTTP Error 403.1 – Forbidden: Execute access is denied.
Internet Information Services (IIS)

Open iis.msc again, go back to the Home Directory tab, and select the “Scripts only” option from the Execute Permissions menu. Restart the server.

The server should now be correctly processing .php files.

Update 6/25/09: I’ve updated the script to include a number of suggestions made in the comments. The new script supports multiple files, up to 20 URLs can be created at a time, and a brief note can be attached to each key. If these features sound useful, please check out the new post at:

Protecting multiple downloads using unique URLs.

A client asked me to develop a simple method for protecting a download (or digital product) by generating a unique URL that can be distributed to authorized users via email. The URL would contain a key that would be valid for a certain amount of time and number of downloads. The key will become invalid once the first of those conditions is exceeded. The idea is that distributing the unique URL will limit unauthorized downloads resulting from the sharing of legitimate download links.

In addition, once the key has been validated, the download starts immediately, preventing the visitor from seeing the actual location of the download file. What’s more, the file name of the download in the “Save as” dialogue box isn’t necessarily the same as the file name of the file on the server, making the file itself pretty much undiscoverable.

How it works

There are five main components to this system:

  1. the MySQL database that holds each key, the key creation time, and the number of times the key has been used
  2. the downloadkey.php page that generates the unique keys and corresponding URLs
  3. the download.php page that accepts the key, verifies its validity, and either initiates the download or rejects the key as invalid
  4. a dbconnect.php file that contains the link to the database and which is included into both of the other PHP files
  5. the download .zip file that is to be protected

Place all three PHP scripts and the .zip file into the same directory on your server.

The MySQL database

Using whatever method you’re comfortable with, create a new MySQL database named “download” and add the following table:

CREATE TABLE `downloadkey` (
  `uniqueid` varchar(255) NOT NULL default '',
  `timestamp` varchar(255) NOT NULL default '',
  `downloads` varchar(255) NOT NULL default '0',
  PRIMARY KEY (uniqueid)
);

The downloadkey.php page

This page generates the key, creates a URL containing the key, and writes the key to the database. Never give out the location of this page – this is for only you to access.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Download Key Generator</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta name="author" content="//ardamis.com/" />
<style type="text/css">
#wrapper {
	font: 15px Verdana, Arial, Helvetica, sans-serif;
	margin: 40px 100px 0 100px;
}
.box {
	border: 1px solid #e5e5e5;
	padding: 6px;
	background: #f5f5f5;
}
</style>
</head>

<body>
<div id="wrapper">

<h2>Download Key Generator</h2>

<?php
// A script to generate unique download keys for the purpose of protecting downloadable goods

require ('dbconnect.php');

	if(empty($_SERVER['REQUEST_URI'])) {
    	$_SERVER['REQUEST_URI'] = $_SERVER['SCRIPT_NAME'];
	}

	// Strip off query string so dirname() doesn't get confused
	$url = preg_replace('/\?.*$/', '', $_SERVER['REQUEST_URI']);
	$folderpath = 'http://'.$_SERVER['HTTP_HOST'].'/'.ltrim(dirname($url), '/').'/';


// Generate the unique download key
	$key = uniqid(md5(rand()));
//	echo "key: " . $key . "<br />";
	
// Get the activation time
	$time = date('U');
//	echo "time: " . $time . "<br />";
	
// Generate the link
	echo "<p>Here's a new download link:</p>";
	echo "<p><span class=\"box\">" . $folderpath . "download.php?id=" . $key . "</span></p>";

	
// Write the key and activation time to the database as a new row
	$registerid = mysql_query("INSERT INTO downloadkey (uniqueid,timestamp) VALUES(\"$key\",\"$time\")") or die(mysql_error());
?>

<p>&nbsp;</p>
<p>Each time you refresh this page, a unique download key is generated and saved to a database.  Copy and paste the download link into an email to allow the recipient access to the download.</p>
<p>This key will be valid for a certain amount of time and number of downloads, which can be set in the download.php script.  The key will expire and no longer be usable when the first of these conditions is exceeded.</p>
<p>The download page has been written to force the browser to begin the download immediately.  This will  prevent the recipient of the email from discovering the location of the actual download file.</p>

</div>
</body>
</html>

The download.php page

The URL generated by downloadkey.php points to this page. It contains the key validation script and then forces the browser to begin the download if it finds the key is valid. 

<?php
// Set the maximum number of downloads (actually, the number of page loads)
$maxdownloads = "2";
// Set the key's viable duration in seconds (86400 seconds = 24 hours)
$maxtime = "86400";

require ('dbconnect.php');

	if(get_magic_quotes_gpc()) {
        $id = stripslashes($_GET['id']);
	}else{
		$id = $_GET['id'];
	}

	// Get the key, timestamp, and number of downloads from the database
	$query = sprintf("SELECT * FROM downloadkey WHERE uniqueid= '%s'",
	mysql_real_escape_string($id, $link));
	$result = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_array($result);
	if (!$row) { 
		echo "The download key you are using is invalid.";
	}else{
		$timecheck = date('U') - $row['timestamp'];
		
		if ($timecheck >= $maxtime) {
			echo "This key has expired (exceeded time allotted).<br />";
		}else{
			$downloads = $row['downloads'];
			$downloads += 1;
			if ($downloads > $maxdownloads) {
				echo "This key has expired (exceeded allowed downloads).<br />";
			}else{
				$sql = sprintf("UPDATE downloadkey SET downloads = '".$downloads."' WHERE uniqueid= '%s'",
	mysql_real_escape_string($id, $link));
				$incrementdownloads = mysql_query($sql) or die(mysql_error());
				
// Debug		echo "Key validated.";

// Force the browser to start the download automatically

/*
	Variables: 
		$file = real name of actual download file on the server
		$filename = new name of local download file - this is what the visitor's file will actually be called when he/she saves it
*/

   ob_start();
   $mm_type="application/octet-stream";
   $file = "actual_download.zip";
   $filename = "bogus_download_name.zip";
 
   header("Cache-Control: public, must-revalidate");
   header("Pragma: no-cache");
   header("Content-Type: " . $mm_type);
   header("Content-Length: " .(string)(filesize($file)) );
   header('Content-Disposition: attachment; filename="'.$filename.'"');
   header("Content-Transfer-Encoding: binary\n");
 
   ob_end_clean();
   readfile($file);

			}
		}
	}
?>

The dbconnect.php script (database connection)

This is the PHP include referenced by both scripts that contains the database link.

<?php
// Connect to database "download" using: dbname , username , password 
    $link = mysql_connect('localhost', 'root', '') or die("Could not connect: " . mysql_error());
    mysql_select_db("download") or die(mysql_error());
?>

This file will almost certainly require some editing. You will need to specify a host name for your MySQL server and a MySQL username and password in that file at mysql_connect('localhost', 'root', '') so that you can connect to the database you’ve set up. It’s extremely unlikely that your production MySQL database will be installed on localhost with a user “root” and no password.

That’s all there is to it. Whenever you want to give someone access to the download, visit the downloadkey.php page. It will generate a unique key code, save it to a database, and print out a URL that you can copy and paste into an email or whatever. The page at that URL checks to see if the key code is legit, then checks to see if the code is less than X hours old, then checks to see if it has been used less than X times. The visitor will get a descriptive message for the first unmet condition and the script will terminate. If all three conditions are met, the download starts automatically.

Security Note:
As the commenter Bruno Ng points out, submitting information via the form fields when using this script sends your database connection info in plain text, and that’s a bad thing.

Therefore, the form field method should only be used for testing local databases.

If you need to test production databases, your database connection info should be hard coded in the PHP script (which isn’t hard to do – look around lines 72-75).

A simple page for testing and troubleshooting a connection to a MySQL database. The PHP script will test the server address, username and password. If the database field is left empty, it will return a list of available databases. Testing a specific database is optional, but if a database name is supplied, it will return a list of the tables in that database (if any exist).

Due to problems with the Syntax Highlighter plugin producing invalid code when copying and pasting, here is a link to the code in plain text. The code below is just for reference.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>MySQL Connection Test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<style type="text/css">
#wrapper {
	width: 600px;
	margin: 20px auto 0;
	font: 1.2em Verdana, Arial, sans-serif;
}
input {
	font-size: 1em;
}
#submit {
	padding: 4px 8px;
}
</style>
</head>

<body>

<div id="wrapper">

<?php
	$action = htmlspecialchars($_GET['action'], ENT_QUOTES);
?>

<?php if (!$action) { ?>

	<h1>MySQL connection test</h1>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>?action=test" id="mail" method="post">

	<table cellpadding="2">
		<tr>
			<td>Hostname</td>
			<td><input type="text" name="hostname" id="hostname" value="" size="30" tabindex="1" /></td>
			<td>(usually "localhost")</td>
		</tr>
		<tr>
			<td>Username</td>
			<td><input type="text" name="username" id="username" value="" size="30" tabindex="2" /></td>
			<td></td>
		</tr>
		<tr>
			<td>Password</td>
			<td><input type="text" name="password" id="password" value="" size="30" tabindex="3" /></td>
			<td></td>
		</tr>
		<tr>
			<td>Database</td>
			<td><input type="text" name="database" id="database" value="" size="30" tabindex="4" /></td>
			<td>(optional)</td>
		</tr>
		<tr>
			<td></td>
			<td><input type="submit" id="submit" value="Test Connection" tabindex="5" /></td>
			<td></td>
		</tr>
	</table>

</form>

<?php } ?>

<?php if ($action == "test") {

// The variables have not been adequately sanitized to protect against SQL Injection attacks: http://us3.php.net/mysql_real_escape_string

	$hostname = trim($_POST['hostname']);
	$username = trim($_POST['username']);
	$password = trim($_POST['password']);
	$database = trim($_POST['database']);

	$link = mysql_connect("$hostname", "$username", "$password");
		if (!$link) {
			echo "<p>Could not connect to the server '" . $hostname . "'</p>\n";
        	echo mysql_error();
		}else{
			echo "<p>Successfully connected to the server '" . $hostname . "'</p>\n";
//			printf("MySQL client info: %s\n", mysql_get_client_info());
//			printf("MySQL host info: %s\n", mysql_get_host_info());
//			printf("MySQL server version: %s\n", mysql_get_server_info());
//			printf("MySQL protocol version: %s\n", mysql_get_proto_info());
		}
	if ($link && !$database) {
		echo "<p>No database name was given. Available databases:</p>\n";
		$db_list = mysql_list_dbs($link);
		echo "<pre>\n";
		while ($row = mysql_fetch_array($db_list)) {
     		echo $row['Database'] . "\n";
		}
		echo "</pre>\n";
	}
	if ($database) {
    $dbcheck = mysql_select_db("$database");
		if (!$dbcheck) {
        	echo mysql_error();
		}else{
			echo "<p>Successfully connected to the database '" . $database . "'</p>\n";
			// Check tables
			$sql = "SHOW TABLES FROM `$database`";
			$result = mysql_query($sql);
			if (mysql_num_rows($result) > 0) {
				echo "<p>Available tables:</p>\n";
				echo "<pre>\n";
				while ($row = mysql_fetch_row($result)) {
					echo "{$row[0]}\n";
				}
				echo "</pre>\n";
			} else {
				echo "<p>The database '" . $database . "' contains no tables.</p>\n";
				echo mysql_error();
			}
		}
	}
}
?>

</div><!-- end #wrapper -->
</body>
</html>

I’ve been developing a PHP/MySQL web application that will be accessed by multiple users. These users will be both viewing and editing records in the database. Obviously, any situation in which multiple users may be performing operations on the same record puts the integrity of the data at risk.

In the case of my application, there is a very real possibility (a certainty, actually) that two or more people will open the same record at the same time, make changes, and attempt to save these changes. This common concurrent execution problem is known as the “lost update”.

User A opens record “A”.
User B opens record “A”.
User A saves changes to record “A”.
User B saves changes to record “A”.

In this example, User A’s changes are lost – replaced by User B’s changes.

I started to look for a method of preventing this sort of data loss. At first, I wanted to lock the record using a sort of check-in/check-out system. User A would check-out record “A”, and then have exclusive write access to that record until it was checked back in as part of the saving process. There were a number of problems with this method, foremost that User A may decide to not make any changes and so not save the record, which would leave the record in a checked-out state until further administrative action was taken to unlock it.

For awhile, I tried to come up with some ingenious way around this, which usually boiled down to somehow automatically unlocking the record after a period of time. But this is not a satisfactory solution. For one thing, a user may have a legitimate reason to keep the record checked-out for longer periods. For another, User B shouldn’t have to wait for a time-out event to occur if User A is no longer in the record.

So what I eventually came up with is a method of checking whether a record has been changed since it was accessed by the user each time a save is initiated. My particular way of doing this involves comparing timestamps, but other ways exist.

Here’s how I’m implementing my solution to the lost update concurrency issue:

User A creates record “A” and saves it at 9:00 AM. A “last-saved timestamp” of 9:00 AM is generated and saved to the record.

User A opens record “A” at 10:00 AM. An “opened timestamp” of 10:00 AM is generated and written to a hidden (or readonly) input field on the HTML page.
User B opens record “A” at 10:00 AM. An “opened timestamp” of 10:00 AM is generated and written to a hidden (or readonly) input field on the HTML page.

At 10:30 AM, User A attempts to save the record. The “last-saved timestamp” is retrieved from the record. The “opened timestamp” of 10:00 AM is compared to the “last-saved timestamp” of 9:00 AM. Because the record has not been changed since it was opened, the record is saved. A new “last-saved timestamp” of 10:30 AM is generated and saved to the record.

At 11:00 AM, User B attempts to save the record. The “last-saved timestamp” is retrieved from the record. The “opened timestamp” of 10:00 AM is compared to the “last-saved timestamp” of 10:30 AM (User A’s timestamp). Because the record has been changed since it was opened by User B, User B is not allowed to save the record.

User B will have to re-open record “A”, consider the effect that User A’s changes may have, and then make any desired changes.

Unless I’m missing something, this assures that the data from the earlier save will not be overwritten by the later save. To keep things consistent, I’m using PHP to generate all of my timestamps from the server clock, as JavaScript time is based on the user’s system time and is therefore wholly unreliable.

The main drawback, that I see, is extra work for User B, who has to now review the record as saved by User A before deciding what changes to make. But this is going to be necessary anyway, as changes made between when User B opened and attempted to save the record may influence User B’s update.

The strange thing is that I haven’t seen this offered as a solution on any of the pages I found while Googling for solutions to the access control, lost update and other concurrency-related data loss problems. Lots of people acknowledge the problem and potential for data loss, but few offer solutions on the application level – preferring to rely on a database engine’s ability to lock rows.

In this post, I’ll illustrate how to use the IP-to-Country database available from http://ip-to-country.webhosting.info/ to identify the real-world geographic location of visitors to a web page (geolocate) based on their IP addresses. Once you know where a visitor is physically located, you can do all sorts of nifty things, such as send them location-aware content (think language, currency, etc.).

The IP-to-Country Database

There are a number of databases that associate IP address ranges with countries. I’ll be using the one at http://ip-to-country.webhosting.info/. It is available as a CSV file, so the first step will be getting the contents migrated into a MySQL database.

The MySQL Part

First, create a MySQL database. I like phpMyAdmin, but use whatever method you are comfortable with. Name the database ip2country.

Once the database has been created, we need to create a table. In phpMyAdmin, click on the SQL tab and enter the following lines:

CREATE TABLE `ip2country` (
  `ipFrom` int(15) NOT NULL default '0',
  `ipTo` int(15) NOT NULL default '0',
  `country2` char(2) NOT NULL default '',
  `country3` char(3) NOT NULL default '',
  `country` varchar(25) NOT NULL default ''
);

This creates a table, also called ip2country, and five fields (ipFrom, ipTo, country2, country3, and country) to hold the data from the CSV.

The next step is to get the contents of the CSV file into the MySQL database, and there are two ways to do this.

If you are using MySQL version 5.0 or greater, the fastest way is to use the LOAD DATA INFILE statement. The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. (For details, visit http://dev.mysql.com/doc/refman/5.0/en/load-data.html.) On my computer, it takes less than half a second to get all 79,000+ rows into the MySQL database. To use this method, click on the SQL tab and enter the following lines, changing the path to “ip-to-country.csv” to the actual path to the file on your local computer.

LOAD DATA INFILE 'D:/PATH/TO/FILE/ip-to-country.csv' INTO TABLE ip2country
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY """"
LINES TERMINATED BY "\n";

The other way to populate the MySQL database is by running a PHP script that writes the records from the CSV one line at a time. To use this method, upload the CSV to a directory on your web site, then create a PHP file with the following lines, editing the script to use your database address, username, and password:

<?php

	// Adjust for the database address, username, password 
	$link = mysql_connect('localhost', 'root', '');
	if (!$link) {
		die('Could not connect: ' . mysql_error());
	}

	$db = mysql_select_db("ip2country") or die(mysql_error());

	// Set the variable $row to zero to begin counting entries
	$row = 0;

	// The ip-to-country.csv must be in the same directory as this php file
	$handle = fopen("ip-to-country.csv", "r");

	// Required to prevent timeout
	set_time_limit(300);

	// While rows exist, write each into the database
	while ($data = fgetcsv($handle, 1000, ",")) {
		$query = "INSERT INTO ip2country(`ipFrom`, `ipTo`, `country2`, `country3`, `country`) VALUES('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '". addslashes($data[4]) ."')";
		$result = mysql_query($query) or die("Invalid query: " . mysql_error().__LINE__.__FILE__);
		$row++;
	}

	// Close the database connection
	fclose ($handle);

	// Print a confirmation
	echo "All done! " . $row . " rows added to database.";
	
?>

Upload the PHP script to the same directory as the CSV file and then browse to it. It will migrate the contents of the CSV file into the MySQL database and then give a little confirmation of how many rows were added when it is done.

If your script times out, you may need to either increase the value of set_time_limit(300); or move that part of the script inside the while loop.

The PHP Part

Now that the database is in place, it’s time to do something with it. The following PHP script will get the visitor’s IP address and print out the corresponding country.

<?php

	// Figure out the visitor's IP address
	$ip = $_SERVER['REMOTE_ADDR'];

	// Establish a database connection (adjust address, username, and password)
	$dbh = mysql_connect("localhost", "root", "") or die("Could not connect: " . mysql_error());
	mysql_select_db("ip2country");

	// Create a query string
	$country_query = "SELECT country2, country FROM ip2country WHERE ipFrom<=INET_ATON('" . $ip . "') AND ipTo>=INET_ATON('" . $ip . "')";
		  
	// Execute the query
	$country_exec = mysql_query($country_query);

	// Fetch the record set into an array
	$ccode_array = mysql_fetch_array($country_exec);

	// Close the database connection
	mysql_close($dbh);

	// Get the country code from the array and save it as a variable
	$country_code = $ccode_array['country2'];

	// Get the country name from the array and save it as a variable
	$country_name = $ccode_array['country'];
	
	// If the database contains a match, print out the country name and country code, otherwise print the IP address
	if ($country_code != "") { 
	echo '<p>The IP-to-Country database contains a match for your ip address: ' . $ip . '</p>';
	echo '<p>You are located in ' . $country_name . ', and the country code is ' . $country_code . '</p>';
	}else{
	echo '<p>Sorry. The IP-to-Country database does not contain a match for your ip address: ' . $ip . '</p>';
	}

?>

Summary

That’s it. You now have a way to determine each visitor’s physical location. Use geolocation carefully, and always provide a fall-back in the event the database does not contain a given IP. IP addresses are constantly being assigned and revoked, so keeping your database up-to-date is critical.