Talk In Code

Converting an array of information into a string is easy, but when you are doing this for insertion into a database having trailing commas is going to mess up your SQL statements.

Take the following example, which takes an array of values and converts them into a string of values. This practice is quite common in PHP database manipulation.

$values = array('one', 'two', 'three', 'four', 'five');
$string = '';
 
foreach ( $values as $val ) {
    $string .= '"'.$val.'", ';
}
 
echo $string; // prints "one", "two", "three", "four", "five",

Obviously we need to strip the trailing comma from the end of this string. To do this you can use the following function.

function deleteTrailingCommas($str)
{
    return trim(preg_replace("/(.*?)((,|s)*)$/m", "$1", $str));
}

This function uses a regular expression to match for one or more commas or spaces after the main bulk of text and before the end of the string and prints out the main bulk of text. The trailing commas are not returned.

Here is another example:

$string = '"one", , ,  , , , ,,';
echo $string;
$string = deleteTrailingCommas($string);
echo $string;

This prints out the following:

"one", , ,  , , , ,,
"one"

A new feature in MySQL version 5.1.6 is the addition of events. These can be either a single event or a schedule, both of which can be given multiple commands to run.

First, you need to make sure that the event scheduler is running. To do this, open up MySQL query browser (or similar) and run the following MySQL command.

SHOW PROCESSLIST;

If the event scheduler you will see a row in the output that looks like this:

Id, User, Host, db, Command, Time, State, Info
120, 'event_scheduler', 'localhost', '', 'Daemon', 242, 'Waiting on empty queue', ''

Turning the event scheduler on and off is quite straightforward and can be done as a MySQL command, or as a parameter when starting the server, or even in an ini file. To turn the scheduler on as a MySQL command run the following:

SET GLOBAL event_scheduler = ON;

Conversely, to turn it off, run the following:

SET GLOBAL event_scheduler = OFF;

To turn it off when you start the MySQL server use the following parameter. Exchange DISABLED with ENABLED to turn it on.

--event-scheduler=DISABLED

To turn it off in the ini file use the following. Exchange DISABLED with ENABLED to turn it on.

event_scheduler=DISABLED

For all instances of controlling the event scheduler you can also use 0 to turn it off and 1 to turn it on instead of DISABLED and ENABLED. You can add and alter events when the scheduler is turned off, but the events will not be run until the scheduler is enabled.

To create an event you need to use the CREATE EVENT command. Lets start off by creating a single event that adds a row to a table called test in the test database at a specified time in the future. The event is called addTimestamp and the command that inserts data is after the DO command. This doesn’t have to be on a separate line, but it looks better and will makes sense when you start adding multiple commands.

CREATE EVENT addTimestamp
 ON SCHEDULE AT '2009-03-30 10:20:00'
 DO
  INSERT INTO test.test(timestamp) VALUES (UNIX_TIMESTAMP());

Note that you need to use

If you create an event in the past you will get the following message.

Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.

Note that this does not run your command, MySQL will just throw away your event and do nothing. The ON COMPLETION NOT PRESERVE setting tells MySQL to either save the event when it has been run, or to throw it away when complete. To save the event after it has been run add the following line underneath the ON SCHEDULE line.

ON COMPLETION PRESERVE

If you set this and try to create another event of the same name you will get an error stating that the name already exists, even if the old even is in the past.

To insert multiple MySQL commands you need to use the BEGIN and END commands. The following command builds upon the previous example, except this time the table is truncated (cleared) and a new timestamp is added.

delimiter |
 
CREATE EVENT addTimestamp
 ON SCHEDULE AT '2009-03-30 10:28:00'
 DO
  BEGIN
   TRUNCATE TABLE test.test;
   INSERT INTO test.test(timestamp) VALUES (UNIX_TIMESTAMP());
  END |
 
delimiter ;

The delimiter tags tell MySQL to pass the entire block in between BEGIN and END to the server before resetting this to the default and parsing each MySQL command separately.

If you want to run the event in an hour, and don’t want to be tied down to times, then change the ON SCHEDULE line to read the following:

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

It is easy enough to see how to change the value of the interval here so I will leave this as an exercise to the reader.

To create an event that occurs every hour you need to change the syntax of the command slightly. Rather than give the SCHEDULE command a time or future interval you use the EVERY command.

delimiter |
 
CREATE EVENT addTimestamp
 ON SCHEDULE
  EVERY 1 HOUR
 DO
  BEGIN
   TRUNCATE TABLE test.test;
   INSERT INTO test.test(timestamp) VALUES (UNIX_TIMESTAMP());
  END |
 
delimiter ;

This can be taken a step further by restricting the window of execution. Lets say we wanted to run a command or set of commands every hour for the next 5 hours. The following commands would be added to the ON SCHEDULE command.

EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
ENDS CURRENT_TIMESTAMP + INTERVAL 5 HOUR

This will cause the commands to be run a total of 5 times.

If you want to know what events or schedules you are currently running the run the MySQL command SHOW EVENTS, this will give you a table full of information. This table will also contain any events that are in the past, but which have had the ON COMPLETION PRESERVE setting added to their creation.

To drop an event you need to run the DROP EVENT command, followed by the name of the event you created.

DROP EVENT addTimestamp;

For more information on the use of the CREATE EVENT syntax please see the MySQL manual page on the subject. There is also more information available on the event scheduler.

Wordpress has a nice little revisions feature that will allow you to revert to a previous version of a post if you don’t like the current edit. However, the drawback of this feature is that it is not always needed and it fills the post table full of stuff you will never need. Fortunately, turning this feature off isn’t too much of a pain. All you need to do is add the following line of code to your wp-config file, just below the DB_COLLATE line.

define('WP_POST_REVISIONS', 0);

You can also set the autosave interval here to something greater than the default of 60 seconds. It is possible to do this in the wp-config file since version 2.5.0.
define('AUTOSAVE_INTERVAL', 123);

If you want to get rid off all of the post revisions from your post table then you can use the following SQL query.

DELETE FROM wp_posts WHERE post_type = 'revision';

To round a number in MS SQL use the ROUND() function. This function takes two parameters, the first is the number to be rounded and the second is the number of decimal places to round the number to. Here is an example of rounding the numbers in a column to the nearest whole integer.

SELECT ROUND(table.column1,0) rentValue FROM table

If you want to concatenate the output of three different columns in MS SQL just use the + symbol.

SELECT table.column1 + ' ' + table.column2 + ' ' + table.column3 output FROM table

The resulting column will be the values of the three columns under the name of output