MySQL

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.

Connecting to a MySQL database in PHP is usually not a difficult thing to do, in fact it is one of the first things that many tutorials will go though. However, there are certain curcumstances that require more information than the standard host, password and username details. A good example of this is when connecting to a local MySQL server through a different (or at least non standard) socket. The normal place for the MySQL socket to be on a Linux install is /var/run/mysqld/mysqld.sock, but some hosts might change this.

Today I needed to grab some data from a table where there was more than one occurrence of a string within another string. Basically, I needed to find all URL's from a table that were more than 3 levels deep (i.e. with 3 slashes), but realised there wasn't a function to do this in MySQL. I found an alternative method, but it got me thinking on how that might be possible.

As part of debugging a bit of code I needed to know the longest possible field lengths that a record contains. You might need to know this if you are performing a database migration. The following query returns a field, along with the length of the string, and orders the results by the number of characters in that string.

There are numerous ways to print out dates and times, and many hours of programming are spent in converting dates from one format to another.

To do this in MySQL you use the STR_TO_DATE() function, which has two parameters. The first parameter is the time to be parsed and the second is the format of that time. Here is a simple example that converts one date format to a MySQL formatted date string.

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.

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

Sometimes, especially when moving data from one server to another, you might find that you have encoded your MySQL database incorrectly. This problem with first show itself if you have the database encoded in one charset and your website set to display in another. If this is the case then you will find strange characters appearing in your text, especially when using punctuation marks. If you are unable or unwilling to change the character encoding on the site then you need to change how the data is encoded in the database.

Run the following table definition to create a table called tests in the test database.

You can optimize MySQL tables using the OPTIMIZE command. This can be used if you have a table with any variable length rows and you make lots of changes to the data in that table. Variable length columns are VARCHAR, VARBINARY, BLOB, or TEXT. The OPTIMIZE TABLE will defragment the data and reclaim any unused space. This also has the effect of resetting any auto incrementing numbers to the highest value in the 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:

Gets the following data: