Talk In Code

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.

1
5
30
200
4000


This is essentially the same as casting the number column, as in the following query.

SELECT number FROM test.tests ORDER BY CAST(number AS UNSIGNED);

However, what happens if you added some textual data? Lets take the original dataset and add two text items to it. These are "1,000" and "text". Using the default sort these two text items are unaffected and appear in the order in which they were added. When using the "+0" method anything that is slightly numeric will be placed into the list and anything else will get put at the start of the data.

text
1
1,000
5
30
200
4000


When using the cast method the order is exactly the same, but in this case MySQL throws some exceptions when trying to cast a non numeric value.

If you are storing numbers in your database table and have to use this query to get the correct order then you might want to consider altering the table so that these values are stored as integers.

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('1979-07-13')), '%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.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS('13/01/1970')), '%Y') + 0;

As well as producing a NULL return value will also give the following error.

Truncated incorrect datetime value: '13/01/1970'

Finally, this query only works with the %Y format string for the year in 4 digits, I have had no success with other date format strings.

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 phony 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.

INSERT INTO rabid_ratings (ratable_id, ip_address, rating, timestamp)
SELECT rabid_ratables.id AS ratable_id,CONCAT(FLOOR(1 + (RAND() * 255)),'.',FLOOR(1 + (RAND() * 255)),'.',FLOOR(1 + (RAND() * 255)),'.',FLOOR(1 + (RAND() * 255))) AS ip_address,FLOOR(50 + (RAND() * 51)) AS rating,NOW() AS timestamp
FROM rabid_ratables
ORDER BY RAND();

Test this data with the following:

SELECT ratable_id,count(ratable_id),ROUND(SUM(rating)/count(ratable_id)) AS vote FROM rabid_ratings GROUP BY ratable_id ORDER BY vote DESC;

The only problem with this is that all of the ratings have the same amount of votes, which makes it look very contrived. Even though it is, we don’t want to make it look like that. The solution to this is to delete a random number of votes for each ratable value. The only problem is that you can’t use the RAND() function within the LIMIT clause of a MySQL statement. So therefore you can use the following bit of PHP to delete a random number of votes.

echo 'DELETE FROM rabid_ratings WHERE ratable_id = 1 ORDER BY RAND() LIMIT '.rand(1,100).';';

By incorporating this into a mysql_connect() function call you can run it for every rateable value and make it look like some items have more votes than others.

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.

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));