One day I noted that my blog was showing an SQL-error in the LINKS section. What a surprise? How come?

I had no idea what action caused this fault, was it a stupid edit somewhere, a bad internet connection while updating my blog or a half succeeded hacking attempt?

SQL-syntax errror

The error was this:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1]
SELECT cat_id, cat_name FROM

And it appeared under the Link/Blogroll section.

What could have caused the problem?

Well, what I knew was that I had updated MyBelovedPHP to the newest version of WordPress 2.2 a few weeks ago. The update seemed to work without problems, but probably I hadn’t scrutinized my blog for a 100%.

I could remember that 2.2 was only suited for PHP 5 version, PHP 4 was no longer supported. OK, but I had PHP 5 on my server. What then?

SQL is database related, what were the changes form WordPress 2.0 tot 2.2. And what didn’t change?

Of course my theme was still the same, that wasn’t updated! Could there be somewhere an issue introduced?

Why was the SQL syntax truncated? It ended at FROM. Where was the tablename and the rest of the query?

Finding the solution

I decided to examine my theme files looking for the Links/Blogroll part and stumbled upon an query in the Links section in my sidebar.php. I opened phpMyAdmin and look for the table, it was not in the database! It seems that WordPress 2.2 joined a few tables, and referencing a non-existing table in a query will of course cause an error. This is the query:
< ?php
$link_cats = $wpdb->get_results("SELECT cat_id, cat_name FROM $wpdb->linkcategories ");
foreach ($link_cats as $link_cat) {
?>

This table disappeared: linkcategories
A quick look into my database showed a table called: categories.

Fixing the code

I changed the code to:
< ?php
$link_cats = $wpdb->get_results("SELECT cat_id, cat_name FROM $wpdb->categories");
foreach ($link_cats as $link_cat) {
?>

It helped, the error-message disappeared, but it showed a lot of empty categories. So i changed the code to:
< ?php
$link_cats = $wpdb->get_results("SELECT cat_id, cat_name FROM $wpdb->categories where link_count > '0'");
foreach ($link_cats as $link_cat) {
?>

That was all. Now it’s working again!

Internet standard format (dotted string)

At first it doesn’t seem that dificult, most people simply store an IP-number as a string in their database tables. It simple and when you have a rather limited amount of addresses a simple VARCHAR 11 wil suit you rather well.
We all understand `192.168.1.10` and we use it everywhere, in our firewalls, routers and information about the internet.

Why not as long integer format?

But since an ip-address is a number, it can be stored much more effecient. In PHP there is a function ip2long that will convert an IPv4 Internet network address from its Internet standard format (dotted string) representation. The resulting integer requires only a third of the original space and furthermore look-ups and indexing will be faster.

$ip='192.168.1.10';
 echo ip2long($ip);

will output -1062731510

Note: Because PHP’s integer type is signed, and many IP addresses will result in negative integers, you need to use the “%u” formatter of sprintf() or printf() to get the string representation of the unsigned IP address.

So

$ip='192.168.1.10';
 echo ip2long($ip);
printf("%u\n", ip2long($ip));

results 3232235786 and this can be stored in an INT(11) field in MySQL.

To my surprise I did not get any negative number on my Linux server in the former example, this in contrast to my windows server. It seems that Linux 64 systems automatically converts the ip long address to the needed unsigned version, or more precisely integers on a 64 bit system are 64 bit instead of 32 bit so integers are not longer limited to 2147483647. I can’t confirm this on a Windows 64 bit system.

This means that we can cut the printf command, but for compatibility reasons I will leave it there, it will do no harm.

Converting to dotted string format

To function long2ip() will do the opposite and can be used to format a stored ip number in long format into a string in Internet standard dotted format for output on the screen. It doesn’t matter if the variable is an unsigned or signed integer:

echo long2ip(ip2long($ip));
echo long2ip(sprintf("%u\n", ip2long($ip)));

Will both print 192.168.1.10;

Converting with MySQL.

An alternative way and probably the most efficient is to let MySQL do the converting:

INET_ATON(expr) 

Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4- or 8-byte addresses.

INSERT INTO `ip_addresses` INET_ATON('192.168.1.10');
 will store 3232235786

The generated number is always in network byte order. For the example just shown, the number is calculated as 192*256*256*256 + 168*256*256 + 1*256 + 10.

Retrieving the IP-number

The function to retrieve the ip-number in dotted string format is INET_NTOA()

SELECT INET_NTOA(3232235786);
will result '192.168.1.10'

The two functions were added in MySQL 3.23.15, so it can be used on nearly all systems.

Just noticed that my PHP 5 mirrors the behaviour of php 4 in the functioning of strrchr.

string strrchr ( string haystack, string needle )

This function returns the portion of haystack which starts at the last occurrence of needle
 and goes until the end of haystack.

Like PHP4, only the first character of the needle is used instead of the whole string. Annoying and a reintroduced bug somewhere?

My PHP version is 5.1.2

Buggy, buggy, bug!!

Why?

Compressing weboutput can save valuable bandwidth. The other advantage is that clients will load your websites faster. Gzip compression is supported by all modern browsers, so why not use it. It will compress your pages between 60% and 80%. So you will lower your bandwidth-costs by an average of 70%. Also your server can push more requests in case you have limited connection speed. Of course it will not work for images.

Apache

Your (Apache)server can do the job, but only when it is configured for it. There was a Apache 1 Module called mod_gzip that compresses the HTML as it sends it out. For Apache 2 this module is called mod_deflate and it also compresses content before it is delivered to the client.

PHP

If you have no control over the Apache config files there is another way to solve the problem. Let PHP compress the content. If you have PHP > 4.3 you can use the zlib.output_compression.

It’s simple, change this setting to ON in your PHP ini file:
zlib.output_compression = On

That’s all. Every page will now send compressed content to every browser that understands the compression method.

NOTE: Don’t forget to disable gzip-compression in CMS-packages like Joomla in the administration section, otherwise you will spoil not only CPU resources but also you can run into trouble with the double compressed content that will show in some browsers only strange unreadable icons on the screen.

You can also adjust the compression level by setting this between 1 and 9:
zlib.output_compression_level = 6

Make your own choice between CPU time to compress on a higher level and the less data that will be sent.

Compression test

I ran a test, without gzip the content of my page is 20162 bytes. Now with different levels of compression, I came to the following results:

1. 6779 bytes
2. 6690 bytes
3. 6591 bytes
4. 6264 bytes
5. 6147 bytes
6. 6134 bytes
7. 6127 bytes
8. 6127 bytes
9. 6125 bytes

I reduced the load of my webpage with 70%. The default value is 6, which is in my test the highest setting with some noticeable compression gains. So their is no real need for setting the level at all in the PHP ini:

zlib.output_compression_level

Just delete the line, if it’s there, PHP info will show -1 which is fine, because the default compression level is 6.

Measuring CPU load is nearly impossible and should not be considered to be an issue until you run into problems with that. In most cases bandwidth is limited and CPU time is plentiful.

Alternative method 1

There was another way of reaching the same by puting this at the start of every PHP-script:
< ? ob_start("ob_gzhandler"); ?>

This still works in PHP 5, but according to the manual is not recommended.

Note: You cannot use both ob_gzhandler() and zlib.output_compression. Also note that using zlib.output_compression is preferred over ob_gzhandler().

Alternative method 2

If you have no access to your PHP.ini file you can set the option in the .htaccess file in the root directory of your website.

php_flag zlib.output_compression on

CSS

When CSS is embedded it’s automatically compressed as well. For enabling CSS files to be compressed, simply rename your style.css files to style.php and it’s done automatically. Of course not forget to refer from your html to the new name when linking.

When you don’t want to adjust the HTML, or for use with WordPress or Joomla that work with different templates that are automatically referred to from the code, the following solution will work and it’s simple again:

1. Copy yourstylesheet.css to yourstylesheet.php
2. Replace the content of yourstylesheet.css to

   @import url(yourstylesheet.php);

Done!

Edited 06-12-12:
For Mozilla/Firefox you need to add

< ? header("Content-type: text/css; charset: UTF-8"); ?>

to yourstylesheet.php at the start of the page. Otherwise the CSS is not recognised as CSS. In Opera/MSIE it was working correctly.