14.09.07

WordPress Link / Blogroll error

- MySQL, PHP -

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!

17.07.07

How to use Apache to compress (gzip) HTML, CSS and Javascript files

- Apache -

In an earlier post we showed you how to use PHP to compress html files and save valuable bandwidth. Now we will choose a setup where apache will compress the files. This is a more efficient setup for your serverconfiguration.

Installing mod-deflate

When Apache 2 is installed, mod_deflate is automatically installed, but not always enabled. To enable mod_deflate on a debian or ubuntu distribution, we can do:
a2enmod deflate
On another system you have to edit Apache2’s configuration file manually. First locate mod_deflate.so then edit the config file. Add this to the LoadModule section:
LoadModule deflate_module /PATH_TO/mod_deflate.soThen restart Apache
apache2ctl restart

Enable the SetOutputFilter DEFLATE filter

Compression for APACHE 2 is implemented by the DEFLATE filter. To enable compression for documents simply put this filter in the appropriate Directory directives:

SetOutputFilter DEFLATE

But we don’t want to compress picture-files

But we do not want to compress everything! It doesn’t make any sense to compress files like gif, jpg or pdf’s. So we have to make an execption:

// Don't compress picture files
SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$ \ no-gzip dont-vary
// Don't compress compressed files
SetEnvIfNoCase Request_URI \.(?:exe|t?gz|zip|bz2|sit|rar)$ \no-gzip dont-vary
// Don't compress pdf's
SetEnvIfNoCase Request_URI \.pdf$ no-gzip dont-vary

Conclusion

Enabling compression is amazingly easy. Simply enable it in your apache directives. To sum it all up:

SetOutputFilter DEFLATE
SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$ \ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.(?:exe|t?gz|zip|bz2|sit|rar)$ \no-gzip dont-vary
SetEnvIfNoCase Request_URI \.pdf$ no-gzip dont-var

If you can’t edit your directives, you can alternatively use the .htaccess files in your webroot directory.
That’s all folks!

20.05.07

Storing IP-numbers in MySQL

- MySQL, PHP -

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.

20.12.06

PHP 5 oddity in Strrchr()

- PHP -

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!!

20.12.06

Technorati Post

- Uncategorized -

Technorati Profile
No further use. 🙂

Categories
Archives
Links