MySQL Logo

Finding Missing Values In A MySQL Table

19th September 2008 - 3 minutes read time

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.

Read the full article

MySQL Logo

Force Sorting Of VARCHAR Data In MySQL

18th September 2008 - 4 minutes read time

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.

Read the full article

PHP Logo

What To Do When get_html_translation_table() And htmlspecialchars() Doesn't Work

17th September 2008 - 2 minutes read time

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.

Read the full article

Loop Through All Files In A Directory With DOS Batch

16th September 2008 - 4 minutes read time

DOS Batch is the Windows equivalent of shell scripting and can be used to perform all sorts of different actions. Anything that you type into a DOS prompt on a Windows machine can be used in a bat file to quickly do something that you would otherwise have to repeat many times over. To create a bat file just make a file and give it the extension "bat". If you run a DOS prompt and navigate to the directory that the bat file exists in you can type the name of the file to get it to do certain actions. If you called your file "action.bat" you can run it by typing "action" or "action.bat". Starting with a simple example, if you want to print the contents of a file to screen then you need the type command, followed by the file.


type file.txt

However, this puts a lot of rubbish on the screen. If you wanted to create a backup of that file then you would write the following.

Read the full article

PHP Logo

Work Out Size In Bytes Of A PHP String

15th September 2008 - 4 minutes read time

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.

Read the full article

MySQL Logo

Work Out Age From Date In MySQL

12th September 2008 - 3 minutes read time

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.

Read the full article

PHP Logo

Connect To FTP Server Using PHP

11th September 2008 - 4 minutes read time

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.

Read the full article

Create A MooTools Slider With Spanning Element

10th September 2008 - 4 minutes read time

The MooTools slider is a good little application, and creates a reliable means of adding in a slider tool to a site. However, one thing that the MooTool slider is missing is a block that covers what is on the left hand side of the slider, before the handle.

Create a page with the following HTML.

Read the full article

WordPress Logo

Reset Your Wordpress Password

9th September 2008 - 2 minutes read time

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.

Read the full article

PHP Logo

String Equals Zero In PHP

8th September 2008 - 6 minutes read time

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.

Read the full article