MySQL

Posts about using the database server MySQL

MySQL Logo

Finding Missing Values In A MySQL Table

19th September 2008 - 2 minutes read time

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.

MySQL Logo

Force Sorting Of VARCHAR Data In MySQL

18th September 2008 - 3 minutes read time

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.

MySQL Logo

Work Out Age From Date In MySQL

12th September 2008 - 2 minutes read time

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.

MySQL Logo

Manage MySQL Databases With phpMyAdmin

7th August 2008 - 2 minutes read time

phpMyAdmin is a tool, written in PHP, that allows you to handle the administration of a MySQL database server. You could always download the MySQL GUI tools, but the problem there is that you need to give external access to a user account, which isn't always possible to do. This is where phpMyAdmin steps in.

Manage MySQL Databases With phpMyAdmin

The tool is easy to use and I have done some tricky stuff with it in the past. It can do everything that you need it to do with MySQL.

MySQL Logo

Optimize A MySQL Table Using PHP

17th July 2008 - 2 minutes read time

In MySQL the OPTIMIZE TABLE can be used if you have made changes or have deleted large parts of the table.

Any deleted rows are kept behind the scenes in the server in order to allow the reuse of these spaces. The OPTIMIZE TABLE command reclaims the unused space and defragments the data file.

For a normal MyISAM table the OPTIMIZE command works in the following way.

  1. If the table has deleted or split rows, repair the table.
  2. If the index pages are not sorted, sort them.
  3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

You can do this automatically by using the following PHP code.

MySQL Logo

Get Information About A MySQL Database With PHP

11th June 2008 - 3 minutes read time

By using the MySQL command.

SHOW TABLE STATUS FROM database;

You can get all sorts of information about a database. The query returns each table as a row and gives lots of information about each table. Using this query it is possible to work out some usage data for the database as a whole. The following function will take a database name and a database resource handle and return how big that database is, the number of tables, and the number of rows in those tables. You will probably have a maximum limit to the amount of data that you can store in your database, so this function is useful to make sure that you don't exceed this limit. This function also uses another function found on the #! code site called readableFileSize() to give more meaningful data sizes.

MySQL Logo

Insert Random Data Into Rabid Ratings

24th April 2008 - 3 minutes read time

Rabid Ratings is a neat ratings script written with the Mootools JavaScript framework that makes adding a rating function to any site very easy. The only problem is that when you first install the script it looks like no-one has ever visited your site to leave a rating. To combat this you can create lots of phoney data that makes it look like your site is well visited and interesting. One way to do this is by getting lots of people to vote on every rating on the site. However, a much easier way is to do this with a few handy MySQL commands.

The following query will create a random vote of between 50 and 100 for each rateable value.

MySQL Logo

Change MySQL Table Name

23rd April 2008 - 1 minute read time

To change a table name in MySQL you can use the ALTER TABLE command with the parameter RENAME TO. Here is an example of a query that will rename the table "atable" to "newtable".

ALTER TABLE atable RENAME TO newtable;

You could also use the RENAME TABLE command with the same effect.

RENAME TABLE atable TO newtable;

This is also useful for renaming all of the tables in the database, just separate each table rename command with a comma.

RENAME TABLE atable TO newtable, anothertable TO anothername;

This should enable you to rename all tables in the database, perhaps with a new prefix.

MySQL Logo

Get MySQL Version Information Through PHP

13th April 2008 - 1 minute read time

There is little syntactical difference between MySQL 4 and MySQL 5, but sometimes finding that difference can pinpoint a bug. The mysql_get_server_info() function will tell you what version of MySQL you are using. You can call it with no parameters, in which case it picks the most recently created MySQL resource, or with the resource handle created with mysql_connect().

Here is an example of how to use it.

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
echo mysql_get_server_info();

You can achieve the same effect with a simple MySQL query.

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$query = mysql_query('SELECT VERSION() as mysql_version');
MySQL Logo

Random Number Range In MySQL

5th April 2008 - 1 minute read time

To create a number between one value and the next you can use the following formula, where i is the lower end of the range and j is the higher end of the range.

FLOOR(i + RAND() * (j – i))

Rather than put in (j-i) in your query you should put in the result. So for a number between 1 and 10 you would make i = 1 and j = 11. 11-1 = 10 so the query would run like this.

SELECT FLOOR(1 + (RAND() * 10));

For a number between 64 and 104 you would use the following query.

SELECT FLOOR(64 + (RAND() * 41));