Making the web

Hey there! Welcome to Making the Web - my personal blog about website development. Feel free to subscribe to my RSS feed to keep up with the latest. Alternatively, subscribe by email. Hope you enjoy this article!

It is too often that developers use the wrong data types in their databases. As queries to databases take a major amount of time in any application, it is essential that the database and its tables are as optimised as possible to ensure the best efficiency, especially in high-demand applications.

In MySQL, the most simple optimisation is to select the correct, most appropriate, and therefore most efficient data types. But many MySQL developers, and other database developers, sometimes fail to do this; non-MySQL developers - such as PHP programmers who use MySQL as just a necessity for their database-driven website - often fail miserably.

To help you understand the data types available, and hopefully use them correctly, I have written about some of the most common - the ones which you probably use every day:

SIGNED or UNSIGNED?
For people who don't know what signed or unsigned is, I'll give a brief explanation:

With signed integers, one bit is reserved so the numbers can be made negative. With unsigned, a bit is not reserved and numbers can only be positive. If you had a SIGNED INT, the value can be between -2,147,483,648 and 2,147,483,647. But, with an UNSIGNED INT, the value can range from 0 to 4,294,967,295.

If you will never use a negative number in a field, then you should use UNSIGNED. If you have an auto-incrementing primary key, then you'd want to use UNSIGNED because it allows for more rows.

Too big?
You should think carefully before creating fields. If you have a 'users' table on a small website, with UNSIGNED INT as the primary key, then ask yourself, "Will I ever get 4,294,967,295 users?" The most likely answer to this question is no. You've got to think about what the highest value is ever likely to be. Then, select the right numeric data type which can cover that amount. I've made a little numeric data type table with the minimum and maximum values:

Type	Minimum	Maximum
SIGNED TINYINT	-128	127
UNSIGNED TINYINT	0	255
SIGNED SMALLINT	-32,768	32,767
UNSIGNED SMALLINT	0	65,535
SIGNED MEDIUMINT	-8,388,608	8,388,607
UNSIGNED MEDIUMINT	0	16,777,215
SIGNED INT	-2,147,483,648	2,147,483,647
UNSIGNED INT	0	4,294,967,295
SIGNED BIGINT	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
UNSIGNED BIGINT	0	18,446,744,073,709,551,615

What's the difference between CHAR and VARCHAR?
The difference between CHAR and VARCHAR is that CHAR is fixed length, while VARCHAR is variable length. This means that CHAR is always the same size and takes up the same amount of bytes, while VARCHAR varies. VARCHAR also adds an extra byte. When should you use CHAR?

To store MD5 passwords, which are always 32 characters long (when stored in hexadecimal form; it may be better to store them as 16 bytes in a BINARY field?).
To store same-length barcodes.

When should you use VARCHAR?

To store email addresses, which vary in size
To store peoples names, which vary in size

You'd usually want to use VARCHAR when storing most user input, because their input can vary in size.

Store as numbers
When possible, you should store data in numbers, because it can be searched and ordered more quickly. For example, IP address can be stored like this:

123.123.123.123

using VARCHAR(15). If you first translated it to an IPv4 Internet network address (in PHP, you can use the ip2long() function), you could store it as:

2071690107

using a SIGNED INT.

Choosing the right date/time type
In MySQL, there are 5 different date and time types:

DATE
TIME
DATETIME
TIMESTAMP
YEAR

DATE gives the date to the nearest day, TIME gives the time (but not the date) to the nearest second, DATETIME gives the date and time to the nearest second, TIMESTAMP gives the number of seconds since epoch, and YEAR gives the date to the nearest year.

YEAR only requires 1 byte of storage, so is the best option if you only need to know the year.
DATE and TIME are the next smallest type of date/time storage, requiring just 3 bytes.
TIMESTAMP requires 4 bytes.
DATETIME requires 8 bytes.

In almost any case, it is probably best to use TIMESTAMP, which gives the date and time to the nearest second.

__('Read the rest of this entry »')

This post is part 1 of a 3 part series. For the other parts, visit these posts:

More Tips for faster PHP scripts
Even More Tips for faster PHP scripts

I have listed my top tips for writing faster (optimized) PHP code:

1. Multiple arguments with echo
When you use echo, you probably use something like this:

echo $variable1 . 'string1' . $variable2 . $variable3;

But, you may be forgetting that echo can take multiple arguments. So you can write it like this:

echo $variable1 , 'string1' , $variable2 , $variable3;

Passing multiple arguments to echo is faster than joining the strings first, and then passing them to echo. I done a little test in PHP. I echoed 10 different strings 500 thousand times, firstly with the concatenation (joining) method, and then with the multiple arguments method. Here are my results:

Time for concatenation method: 37.83755 seconds
Time for multiple arguments method: 37.68789 seconds
Time saved: 0.15966 seconds; 0.396%

As you can see, the difference is very small. It is not worth going over all your old PHP scripts and changing the dots to commas, unless you are extremely desperate for speed. It is more a case of preference, than there being a best way.

Oh, and another small tip (without it's own number): use echo instead of print - it's faster!

__('Read the rest of this entry »')

This article is provided for information only. Only use the information within this article to create "hacker-proof" applications.

SQL Injection is a way that hackers can execute SQL code, by exploiting a security vulnerability in an application. It allows hackers to get email addresses, passwords and other information from databases.

Before you include any input into a SQL query, the data should be correctly escaped.

Image a MySQL query, in PHP like this:

mysql_query('SELECT email FROM `users` WHERE username = "'.$_POST['username'].'" AND password = "'.$_POST['password'].'"');

If you are using input data like this in a query, then you have a huge security vulnerability with your website. Everything should be fine if $_POST['username'] and $_POST['username'] are something like these:

mike16 , password

But, image what would happen if $_POST['username'] and $_POST['password'] contained this:

none" OR "" = "

That input would make the query like this (input highlighted with bold):

mysql_query('SELECT email FROM `users` WHERE username = "none" OR "" = "" AND password = "none" OR "" = ""');

If this was a login form, a hacker would be able to access anyones account, without the need for their password or username. Once in their account, the hacker could get personal information, delete their account or edit it.

Luckily, there is a way to make the data safe before using it in a query. The input has to be escaped. With MySQL in PHP you can use the mysql_real_escape_string() function:

mysql_query('SELECT email FROM `users` WHERE username = "'.mysql_real_escape_string($_POST['username']).'" AND password = "'.mysql_real_escape_string($_POST['password']).'"');

If a hacker did then insert none" OR "" = " or something else, it would look similar to this:

mysql_query(SELECT email FROM `users` WHERE username = "none\" OR \"\" = \"" AND password = "none\" OR \"\" = \"");

This query is now safe, and, as you can see, it protects from SQL Injection.

Sometimes it can be difficult to get backlinks to a web site. This is especially true if the domain is new, because everything starts from scratch. But, some expired domains already have backlinks. There are a few websites on the internet which list expired domains which have PR. All you have to do is select a domain and put your own website on it.

So, now you have a domain with an excellent PR of 1, 2, 3 or even 4. Now, you just have to maintain this figure (or even increase it).

Don’t be tricked

Make sure you do your research when buying expired domains with PR. PR can be falsified by tricking search engines and the domain you think is great may, in fact, be worthless. Although you’ve only got the cost of the domain to loose, it can still be a dissappointment to find that the domain you bought has a faked PR level.

Before buying a domain name, go to http://google.com and search info:domain.ext (replace domain.ext with the domain). If it lists another website, then the PR belongs to that website.

You should also search link:domain.ext, and check some of the backlink pages. If the PR is faked, you’d probably notice that your link isn’t on any of the pages.

Useful Websites

Domainleft.com - This is a great resourse which I find very useful. It lists expired domains with valid PR, as well as providing tips for buying and selling domains, and obtaining and maintaining PR.

Checkpagerank.net - You can use this great tool for checking whether domains have a valid PR.

Xinureturns.com - Just enter your website’s address into this tool and it’ll give you loads of information, including a diagnosis of page title, keywords and description, ranking from search engines, and backlinks.

I just thought that I'd write to tell you about a 1 to 1 messaging system I have released under a Creative Commons license.

The messaging system is the first version and my first ever attempt (but, still pretty good I think). Version 2 of this system can be seen at http://22talk.com. Version 1 is similar in some ways and allows users to communicate with each other easily, as well as send emoticons and nudges.

It's coded in PHP and requires MySQL.

Download it now from http://planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=2284&lngWId=8