Note: This post is over a year old and so the information contained here might be out of date. If you do spot something please leave a comment and I will endeavour to correct.
I needed to create a query that did a case insensitive search using the LIKE command in MySQL and I quickly realised that in order to do this I would need to alter both the parameter and the table data to be the same case. This can be done by using the MySQL UPPER() command on the table data and the strtoupper() PHP function on the input data.
To round a number in MS SQL use the ROUND() function. This function takes two parameters, the first is the number to be rounded and the second is the number of decimal places to round the number to. Here is an example of rounding the numbers in a column to the nearest whole integer.
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.
If you are querying a database you should get into the habit of sanitising your input, even if it's not coming from users at the moment it might do in the future. SQL injection attacks are all too common and they can be easily prevented.
The addslashes() function takes a string as the input and returns a string with backslashes before all characters that required quoting in database queries. The characters it acts on are quote, double quote, backslash and NUL (or a NULL byte). Magic quotes runs addslashes() on all COOKIE, GET and POST data. The important thing is not to use addslashes() and magic quotes on the same string as everything will be double escaped.
Use the get_magic_quotes_gpc() function to see if magic quotes is enabled. If it isn't then use the PHP function addslashes() to do the same thing. Use this function if you have any user input in order to sanitise anything that you are about to send to a database.
To get a random row from a Microsoft SQL database you need to use the NEWID() function. This will generate a new random number for each row and order them by that new number. This is used in conjunction with the TOP clause to limit the amount of returned rows to one.
