SQL

Case Insensitive Like Searches In MySQL

2nd July 2011

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.

Wordpress Database Changes When Moving Site

27th March 2010

When moving a Wordpress install from one place to another there are a number of things you must be aware of. If you have created your templates properly you will have used calls to bloginfo('home') rather than using static links, but you will need to update these links to make your Wordpress install work properly.

Once the files are in place, the database connection details have been edited and the database created there are a number of things to alter in the database to make your Wordpress install work.

Delete Trailing Commas In PHP

14th April 2009

Converting an array of information into a string is easy, but when you are doing this for insertion into a database having trailing commas is going to mess up your SQL statements.

Take the following example, which takes an array of values and converts them into a string of values. This practice is quite common in PHP database manipulation.

MySQL Event Scheduler

A new feature in MySQL version 5.1.6 is the addition of events. These can be either a single event or a schedule, both of which can be given multiple commands to run.

First, you need to make sure that the event scheduler is running. To do this, open up MySQL query browser (or similar) and run the following MySQL command.

SHOW PROCESSLIST;

If the event scheduler you will see a row in the output that looks like this:

philipnorton42 Mon, 03/30/2009 - 10:05

Turn Off Wordpress Revisions

24th February 2009

Wordpress has a nice little revisions feature that will allow you to revert to a previous version of a post if you don't like the current edit. However, the drawback of this feature is that it is not always needed and it fills the post table full of stuff you will never need. Fortunately, turning this feature off isn't too much of a pain. All you need to do is add the following line of code to your wp-config file, just below the DB_COLLATE line.

Round A Number In MS SQL

10th November 2008

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.

Concatenate Strings In MS SQL

7th November 2008

If you want to concatenate the output of three different columns in MS SQL just use the + symbol.

Limit Number Of Rows Returned In MS SQL

23rd September 2008

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.

Reset Your Wordpress Password

9th September 2008

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.

Check Slash on Database Input

10th January 2008

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.