MySQL

Posts about using the database server MySQL

Work Out Age From Date In MySQL

12th September 2008

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.

Optimize A MySQL Table Using PHP

17th July 2008

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.

Insert Random Data Into Rabid Ratings

24th April 2008

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.

Change MySQL Table Name

23rd April 2008

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

Get MySQL Version Information Through PHP

13th April 2008

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.

Random Number Range In MySQL

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.

philipnorton42 Sat, 04/05/2008 - 19:35

Finding The Latest Timestamp Value From A Set Of Values In MySQL

20th March 2008

Storing sets of data with a timestamp is common practice, especially if you want to keep a history of the values that have been stored previously. Lets say you wanted to store a piece of information about two variables, each of which can have a history. Here is an example dataset.