If you have a table of incremental values it can be hard to find out which ones are missing. The only solution might be to write a script to get all the data from the database and see which ones are missing. However, there is a way of doing this without using a script.

Using a standard select query like this:

SELECT * FROM table;

Gets the following data:

1
3
10
23

We can see that values are missing, but which ones? The following query will show us where the gaps are in the data of the table.

SELECT t1.id+1 as Missing
FROM table as t1
LEFT JOIN table  as t2 ON t1.id+1 = t2.id
WHERE t2.id IS NULL
ORDER BY t1.id;

Produces the following result.

If you find that you are having trouble sorting data in a VARCHAR column in a MySQL database then you can try the following trick.

Lets say that you had the values 1,200,30,4000 and 5 and that you inserted them into the database in that order. When the following query is run on this data:

SELECT numbers FROM table ORDER BY numbers;

The following output is seen.

1
200
30
4000
5

This is clearly not the correct order, although it represents the order of. You can force a natural order to the sort by using a "+0" after the colum you are trying to sort by.

SELECT numbers FROM table ORDER BY numbers+0;

This produces the following output, which is sorted as you expect a set of numbers to be sorted.

I found a little problem today when processing a bit of text from a non-english site. I found that the text was being loaded properly, but because it was in UTF-8 encoding PHP couldn't use htmlspecialchars() or apply get_html_translation_table() to the string to properly encode the foreign characters. These methods just don't have any effect. This is because PHP (before version 5.2.x) doesn't natively support unicode character encoding and is therefore not able to translate characters in UTF-8 format.

To get around this just use the utf8_decode() function on the string to convert it into a usable format.

I found this very handy function on the php.net site in the user comments for the strlen() function. It accepts a string in ASCII or UTF-8 format and finds out how long that string is in bytes.

The function works by going through the string and adding how many bytes each character represents. For normal ASCII values this is a single byte so 1 is added to the total. Unicode characters can be up to 6 bytes and so the rest of this function works out how many bytes the character takes up by using AND calculations.

Rather than work out how many years have passed since and event, or how old something is, after you get the data from a MySQL database you could use the following query to convert the date on the MySQL side. It works by subtracting the current date from the given date and formatting it in years. Adding the given value to 0 casts the string given by DATE_FORMAT() into an interger.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS('2000-07-23 09:20:59')), '%Y') + 0;

This gives the result of 8. You can also pass in just the year, month and day string to the function.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS('2000-07-23')), '%Y') + 0;

This gives the result of 38.

The function requires that a properly formatted MySQL date string is found or it will produce an error. The following string used in this function.

FTP connection functions have been built into PHP since version 4 and make transferring files through FTP very easy.

The main function involved is called ftp_connect() which takes a FTP host as a parameter and attempts to connect to it. The port and a timeout limit can also be added to the function if needed.

Once a connection has been made then the ftp_login() function is used to attempt a login. This function returns true on success and false if it fails. The following snippet of code will attempt to connect and login to an FTP server, if any step fails then the code will print out a message saying so.

If for some reason you can't remember your Wordpress password and you can't use the "lost your password" function that comes with Wordpress, due to problems with email, then you can use the following SQL command to reset your password.

UPDATE wp_users SET user_pass = md5('newpassword') WHERE user_login = 'admin';

This can be useful if you have a local web server that you are trying things out on before they go live on the Internet. These servers often don't have access to email as they are just testing platforms and will therefore fail if you try to use the "lost your password" function.

This command has been tested on Wordpress version 2.6.2.

Due to the weakly-typed nature of PHP you can do some odd things, some of which are good, and some of which will enable you to shoot yourself in the foot. Take the following little snippet.

echo '1' + 5;

In some languages this might cause the program to fall over, but PHP will try to evaluate any string into an integer. In this case it converts the string to an integer 1 and adds this to 5 to make 6.

As an aside, if you did this in JavaScript then you would find the opposite result. Because the concatenation character is the same as the addition character JavaScript will always try to truncate the value if any of the present values are a string. So the result in JavaScript would be "15".

If we change the string to a string of "one" and then did the same then the result is 5.