Best way to store IP addresses in MySQL

By David Pratt / Tags: , / 26 Comments / Published: 10-08-10

It’s tempting to store IP addresses in a database as a VARCHAR(15) in the absence of a dedicated IP address field type in mysql, but that isn’t the most efficient way of doing so. The best way that I’ve come across to store an IPv4 address is to store it as an unsigned integer. In phpmyadmin you might set up an ip address field so that it looks something like:

Screenshot of the PHPMyAdmin field setup for an IP address field

Screenshot of the PHPMyAdmin field setup for an IP address field

You’ll realise I’m sure that you can’t just add the dotted IP address straight into an INT field without first converting it into a valid format. For that you’ll need a PHP function called ip2long which will convert a string containing an IP dotted address into a integer that can be stored in the INT field.

Here is a quick example of how you might go about getting the real IP address of a client and then storing and retrieving its value from the mysql DB:

//Test if it is a shared client
if (!empty($_SERVER['HTTP_CLIENT_IP'])){
  $ip=$_SERVER['HTTP_CLIENT_IP'];
//Is it a proxy address
}elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
  $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
}else{
  $ip=$_SERVER['REMOTE_ADDR'];
}
//The value of $ip at this point would look something like: "192.0.34.166"
$ip = ip2long($ip);
//The $ip would now look something like: 1073732954

Now that you have the real IP address of the client converted to the INT format, you can write it into the DB as you normally would:

$sql = "INSERT INTO user(ip) VALUES('$ip')";
$dbQuery = mysql_query($sql,$dbLink);

To retrieve the original IP address from the database you can use the mysql function INET_NTOA like so:

SELECT INET_NTOA(ip) FROM 'user' WHERE 1

Alternately you could use the PHP function longtoip to convert the returned INT value into the dotted IPv4 address in the PHP code instead, and you could even add the dotted IP address to the INT field in the db using the mysql funtion INET_ATON.

Storing IP addresses in this manner is beneficial because it takes less space than storing it as a string. The other benefit is that lookups are faster because integer comparisons are quicker than string comparisons.

Non-standard page size’s in TCPDF

By David Pratt / Tags: , / 17 Comments / Published: 23-06-10

If you need to create a PDF using TCPDF that isn’t a standard size (A4, A5, B1, LETTER etc.) then it is possible to specify a custom size even though it isn’t that well documented. To do this you need to pass an array containing the desired width and the height of the PDF instead a formatted parameter such as ‘A4’. For example:

// create new PDF document
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
 
// add a page
$resolution= array(100, 100);
$pdf->AddPage('P', $resolution);

Make sure you express the height and width in the unit of measurement (pt, px, mm, cm etc.) that you are using as well.

Using Crontab with Plesk to call PHP files

By David Pratt / Tags: , , / 36 Comments / Published: 06-05-10

Having battled with getting a php file to be called from a scheduled task (a.k.a. a crontab task) using the Plesk interface panel on a Linux box, I thought I’d share a bit of a how-to on it because I struggled to find anything helpful out there on the topic.

Here is screen shot of a completed “Schedule New Task” form so that you can see what a working configuration looks like:

Setting up Crontab from within Plesk to load a php file.

Setting up Crontab from within Plesk to load a php file.

The fields on the form look fairly self explanatory to fill out, but the first gotcha for me was how to get it so that the job runs every hour instead of on just one named hour. To get the cron to run every hour you just need to use an asterisk (*), the same technique can be applied for all other time frequencies as well.

Once you’ve set the cron to run at the required interval, you will need to tell it what command to call. This isn’t obvious either. Firstly what you need to do is call php or state the path to the php folder, then pass it the parameter “-q” (to suppress the HTTP header output), and then state the full path of the php file that you want to run e.g.

php -q httpdocs/cron.php

If you just put the path to the name of the php file that you want to run, then the cron will throw an error.

For reference, I’m using Plesk 9.5.1 & PHP 5.3

Calculate text width and height with JavaScript

By David Pratt / Tags: , , / 5 Comments / Published: 22-04-10

Occasionally when putting together an advanced layout it’s necessary to have a level of control over the UI that can’t be achieved with CSS alone.

One situation might be in displaying a place name. Say for instance that place name had to be rendered in block that has a width of 200px, with a height that can flex as necessary to accomodate the content. This won’t be a problem in 99.9% of situations, but what happens if the place name is Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch?! Yes this place does exist! In cases like this you might want to apply a special class name, or maybe dynamically adjust the font-size so that it fits within the allowed container. The consequences of not defending against this situation might be that the layout behaves unpredicatably, breaks, or perhaps some of the word could be hidden by the overflow property in the CSS being set to hidden or something.

So, should you find yourself in a situation where you need to measure the height and width of a text element, this code might help you:

HTML
<p id="place-name"> Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch</p>
CSS
#place-name{
    position: absolute;
    height: auto;
    width: auto;
}
JavaScript
var oPlaceName = document.getElementById("place-name");
if (oPlaceName){
	var iHeight = oPlaceName.clientHeight + 1;
	var iWidth = oPlaceName.clientWidth + 1;
	if (iWidth > 200){
		//Deal with the long text.
	}
}

I have prepared a quick standalone example should you want to see this in action.

A place name might have been a bad example, but it could have quite easily have been any other type of field who’s length or height you might want explicit control over. Other applications of this that I can think of quickly could be adjusting the font-size of headings so that they never wrap, perhaps adjusting letter spacings so that they have aligned edges, or whatever else the designers .psd file shocks you with! With a clever implementation, this sort of thing could be achieved without the user even noticing, but that’s a job for someone else!

A few things to consider when developing on a public domain

By David Pratt / Tags: / No Comments / Published: 14-03-10

If you ever find yourself developing a site on a public facing domain, it is always a good idea to have measures in place to prevent search engines indexing it in the event that a stray link enables a search engine spider to find it. A few of the things that you can do are…

Ensure the document head of each page has the “noindex” meta tag. This statement tells search engine spiders to not index any of the content on the current page.

<meta name="robots" content="noindex" />

Ensure the document head of each page has the “nofollow” meta tag. This statement tells search engine spiders to not follow any links on the page.

<meta name="robots" content="nofollow" />

Add a rule to the robots.txt file that prevents search engine spiders from accessing the site.

User-agent: *
Disallow: /

Just remember to remove these measures when you launch the site!