MySQL Logo

Case Insensitive Like Searches In MySQL

2nd July 2011 - 2 minutes read time

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.

$name = strtoupper('phil');
$query = "SELECT * FROM users WHERE UPPER(forename) LIKE '" . $name . "%'";

This will produce the following SQL query.

SELECT * FROM users WHERE UPPER(forename) LIKE 'P%';

I hope that little tip comes in handy to someone looking for the same solution.

Read the full article

MySQL Logo

Count Number Of Characters In A String With MySQL

30th September 2010 - 3 minutes read time

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.

Finding the solution wasn't too difficult really, all I needed to do was find out the length of the URL string and then subtract the length of the string with the slashes removed. This MySQL statement will give you the URL and the number of slashes in that URL in a table.

Read the full article

MySQL Logo

MySQL Order Table By Character Length

24th September 2009 - 2 minutes read time

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.

SELECT field, CHARACTER_LENGTH(field) as fieldCharacterCount
FROM table
ORDER BY fieldCharacterCount DESC


Read the full article

PHP Logo

Backup MySQL Database PHP Script

12th August 2009 - 7 minutes read time

There are quite a few scripts available on the Internet that allow you to dump data from a database into a format that can be used to replicate that database structure elsewhere. The following function is my take on this commonly occurring script.

Read the full article

MySQL Logo

Starting And Stopping MySQL Using Windows .bat Files

13th May 2009 - 3 minutes read time

I use my PC for a lot of different things, and I don't necessarily need to have MySQL server running all the time, especially when I want to play a game. So I wondered if there was a simple way in which I could start and stop the server using a simple .bat file.

I had a look at the MySQL website and found a page that details how to start MySQL from the command line. This page suggested that I use the following command.

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld"

Use the following command to stop the server. In this case the username is root and the password is wibble.

Read the full article

Apache HTTP Logo

Apache Log File Into MySQL Table

20th April 2009 - 5 minutes read time

Apache can be set up to log all sorts of information. As of Apache 2.2 the basic log file format that a fresh install of Apache will produce will have the following format:

%h %l %u %t "%r" %>s %b

Which doesn't mean a lot to the uninitiated, so here is a short explanation of each.

Read the full article

MySQL Logo

MySQL Event Scheduler

30th March 2009 - 8 minutes read time

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:

Id, User, Host, db, Command, Time, State, Info
120, 'event_scheduler', 'localhost', '', 'Daemon', 242, 'Waiting on empty queue', ''

Turning the event scheduler on and off is quite straightforward and can be done as a MySQL command, or as a parameter when starting the server, or even in an ini file. To turn the scheduler on as a MySQL command run the following:

Read the full article

MySQL Logo

Correcting Wrong Character Encoding In MySQL

16th March 2009 - 4 minutes read time

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.

The most common sort of thing you might want to do is change from iso-8859-1 (or windows-1252) to UTF-8. This can be done in one of two ways.

The first way is to simply alter the table so that the column contains a different charset.

Read the full article

MySQL Logo

MySQL Procedure To Get Incremental Numbers

9th March 2009 - 3 minutes read time

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

CREATE TABLE test.tests (
 id int(10) unsigned NOT NULL auto_increment,
 var varchar(45) NOT NULL,

Fill it with some default values:

INSERT INTO tests(var) VALUES('one'), ('two'), ('three'), ('four');

Now lets say that you want to find the values in the table, but that you want to increment a value for each row, the following procedure will do this. If you are using the MySQL Query browser then open a script table and past the following and execute, changing any database/table definitions.

Read the full article

MySQL Logo

Optimize Your MySQL Database Tables

3rd March 2009 - 3 minutes read time

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.


You can do this for every table in the database, but to save time MySQL comes with a command line script that will optimize all tables in a database. This program is called mysqlcheck and can be run like this.

mysqlcheck -o database -u user -ppassword

Note that when writing this command you should not put a space in between the -p and your password.

Read the full article