Talk In Code

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

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.

Value2 has been updated in this table three times. To get the latest data where Value1 is equal to 1 you can use the following query.

SELECT * FROM datatable WHERE datatable.timestamp >= (SELECT MAX(timestamp) AS time FROM datatable WHERE Value1 = 1) ORDER BY Value2 ASC;

This uses a subquery as part of the WHERE clause in order to find the maximum timestamp available for Value2.

If the data items are added over a period of a few seconds you can use the TRUNCATE() function with a negative integer as the second argument to group the timestamp values in a more meaningful way. Using a negative value with TRUNCATE replaces numbers to the right of the number with zeros. Use the following SQL statement to see what the function does with the timestamp.

SELECT UNIX_TIMESTAMP(), TRUNCATE(UNIX_TIMESTAMP(),-8);

This will change the value 1206112846 to 1200000000.

Using the truncate function with the timestamp value in this way can lead to problems if your data is entered every few seconds. If it is the case then you would need to enter values into database using the truncate function so that if two values are entered at more or less the same time the new one replaces the old.