Talk In Code

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.

A good security tip when installing your WordPress blog is to change the database table prefix, the idea is that this will hide the tables from any hackers looking to compromise your blog. This can be done in the wp-config.php file and the variable $table_prefix. Changing this value from the default wp_ to, for example, blog_ will change the table wp_posts to blog_posts, making it more difficult for hackers to find it. Using blog_ is only an example, you should treat the prefix like a password, include letters and numbers to make it more difficult to find.

But what happens if you have installed your system and want to change the prefix? Well you need to change the $table_prefix variable in your wp-config.php file and alter the tables in your WordPress database. Which one you do first is up to you, but you should go them both as fast as you can. Here are some MySQL statements that should help you to do this.

ALTER TABLE wp_comments RENAME TO blog_comments;
ALTER TABLE wp_links RENAME TO blog_links;
ALTER TABLE wp_options RENAME TO blog_options;
ALTER TABLE wp_postmeta RENAME TO blog_postmeta;
ALTER TABLE wp_posts RENAME TO blog_posts;
ALTER TABLE wp_term_relationships RENAME TO blog_term_relationships;
ALTER TABLE wp_term_taxonomy RENAME TO blog_term_taxonomy;
ALTER TABLE wp_terms RENAME TO blog_terms;
ALTER TABLE wp_usermeta RENAME TO blog_usermeta;
ALTER TABLE wp_users RENAME TO blog_users;

This just renames the default tables so that they have the new prefix, just do a find/replace on the text ‘blog_’ to be something unique. Note that this doesn’t include any tables created for any plugins that you have, so you will have to add SQL statements for each of these tables.

This allows the front page of your blog to function properly, but when you try to log in to the admin section you will see the following error.

You do not have sufficient permissions to access this page.

This occurs because of some user settings that are created when the database is created and when new users are added still reference tables with the old prefix. The tables wp_usermeta and wp_options need to be changed as they both reference tables by name and will not produce the desired results if you change the table prefix.

To change the wp_usermeta (or blog_usermeta if you used the above SQL statements) table use the following SQL query.
UPDATE blog_usermeta SET meta_key = REPLACE(meta_key,'wp_','blog_');
This will change values contained in the meta_key column. The values that are changed look something like wp_capabilities, wp_autosave_draft_ids and wp_user_level, although wp_autosave_draft_ids won’t be there unless you have saved drafts of posts while editing them.

To change the wp_options table (again, this would be blog_options) use the following SQL query.
UPDATE blog_options SET option_name = 'blog_user_roles' WHERE option_name = 'wp_user_roles' AND blog_id =0;

You should now be able to access your admin system.

This post applies to WordPress version 2.3.3.

To get a random row from a PostgreSQL database you need to use the RANDOM() function. This is similar to the MySQL function RAND() and will generate a new random number for each row and order them by that new number. This is used in conjunction with the LIMIT clause to limit the amount of returned rows to one.
SELECT value FROM table ORDER BY RANDOM() LIMIT 1

To get a random row from a Microsoft SQL database you need to use the NEWID() function. This will generate a new random number for each row and order them by that new number. This is used in conjunction with the TOP clause to limit the amount of returned rows to one.
SELECT TOP 1 value FROM table ORDER BY NEWID()