Articles

Limit Number Of Rows Returned In MS SQL

To limit the number of rows returned in a MS SQL query you need to use the TOP command. This goes before you name the columns that are to be returned by the SELECT statement.

The following query returns the first 35 rows from a table.

Regular Expression To Find Single Apersands In Text

Encoding special characters in a block of HTML or other code can be a pain because there might already be ampersands there that impart encoding. This might be an ampersand that has already been encoded with a &, or it might be an ampersand in the code as an if statement or similar.

Use the following regular expression to find any ampersand that hasn't already been encoded.

([^&])&(?!#?[a-zA-Z0-9]{2,6};|\$|&)

When using replace, you can turn any ampersand into & by using the following replace.

$1&

The only problem with this statement is when the code uses a & operator as part of a statement to do bitwise operations.

Finding Missing Values In A MySQL Table

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.

Force Sorting Of VARCHAR Data In MySQL

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.

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

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.

Loop Through All Files In A Directory With DOS Batch

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.

Work Out Size In Bytes Of A PHP String

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.

Work Out Age From Date In MySQL

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.

Connect To FTP Server Using PHP

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.

Create A MooTools Slider With Spanning Element

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.