MDBitz Development Resources

Transferring data between tables is a common task of database maintenance. It is used when updating database schemas or when you need to migrate data to a log or history table. There are 2 common methods for performing the transfer the first is done by use of a WHERE NOT EXISTS sub query, while the less common known method is by setting up and utilizing a Trigger, which removes rows from the old table as soon as they get inserted into the new table.

WHERE NOT EXISTS Method

The most common method of transferring data between tables in a database that i have seen is the combination of ROWCOUNT and a WHERE NOT EXISTS clause. How this method works is that you set your ROWCOUNT to the batch size then you insert into your table to be transferred to from a select of your old table where the current record does not exist in your transfer to table. A sample code snippet showing this method is:

DECLARE @ErrorId INTEGER,
    @RowCtrInserted INTEGER
 
SET ROWCOUNT 10000
 
SELECT @RowCtrInserted = 1, @ErrorId = @@ERROR
 
WHILE (@ErrorId = 0 AND @RowCtrInserted > 0)
BEGIN			
    INSERT INTO LOG_HIST	
        (
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
        )
    SELECT 
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
    FROM LOG LOG
    WHERE NOT EXISTS (
        SELECT * 
        FROM LOG_HIST hist
        WHERE LOG.ID = hist.ID 
     )
 
    SELECT @RowCtrInserted = @@ROWCOUNT, @ErrorId = @@ERROR
END
 
IF @ErrorId=0 
BEGIN
    TRUNCATE TABLE LOG
END

As can be seen in the code this method loops while a record exists in the log table that is not present in the log_hist table. This requires an additional query every time the batch size is met, and can be a performance problem on large database tables.

Temporary Trigger Method

The second method that can be used to transfer data between two tables is the use of a temporary insert trigger on the table being transferred to. How it works is that the trigger is defined to delete the newly inserted record from the old table. What this means is that for every iteration of the insert loop as determined by the batch size a sub query to determine if a record already exists in the new table is not necessary as records are deleted as soon as they are transferred. Below is a sample sql script setting up the trigger and performing the transfer, in addition it contains error checking and print statements.

CREATE TRIGGER LOG_HIST_TR ON LOG_HIST FOR INSERT
AS
DELETE LOG FROM LOG, inserted
  WHERE 1=1
    AND LOG.ID = inserted.ID
go
 
PRINT "Transferring data from LOG to LOG_HIST"
go
 
DECLARE @err INT, @n INT
SELECT @n=0
PRINT 'Will use batch size of 10000 rows'
WHILE exists (SELECT * FROM LOG)
BEGIN
    INSERT LOG_HIST (
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
    )
    SELECT TOP 10000
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
    FROM LOG
 
    SELECT @err=@@ERROR, @n=@n+@@ROWCOUNT
 
    IF @err=0
    BEGIN
        DUMP TRAN USERACTIVITY WITH truncate_only
        PRINT '%1! rows transferred', @n
    END
    ELSE
    BEGIN
        RAISERROR 20001 'Failed to transfer data from table LOG to table LOG_HIST. Please recover data from these two tables'
        BREAK
    END
END
 
go
PRINT "Checking data transfer completion"
go
IF not exists (SELECT * FROM LOG)
BEGIN
    DROP TRIGGER LOG_HIST_TR
END
ELSE
BEGIN
    RAISERROR 20001 'Failed to transfer data from LOG into LOG_HIST'
END

In the above example after each batch iteration we output the total number of records transferred, as well as cleaning up the database log so that we don’t max out the allocated space. Once an error is thrown or no more records exists we drop our temporary trigger. You will notice that no truncating of the table we transferred data from is needed as the trigger was responsible for removing them. The additional benefit to using this method is that if a transfer is interrupted no time or data is loss and the transfer can pick up right were it left off without performing an additional WHERE NOT EXISTS sub query.

Until the release of WordPress 3.0 due out later in the year, creating a sub navigation is still a somewhat obscure and tricky task. To hopefully shed some light on how to create a Sub Navigation for your own needs I am going to walk-through how you can output a pages child or sub pages. In simple terms how to display a second level navigation in your sidebar.

How to get Sub Pages

In WordPress the easiest way to get the a page’s child pages is to use the get_pages function. This function allows you to specify that you want the children of a page by using the child_of and parent parameters. When called this function returns an array that you can utilize to iterate over and output the child pages.

$child_pages = get_pages('child_of='. $post->ID; . '&parent=' . $post->ID;);

The $post variable is used in WordPress templates to denote the current post or page. We utilize the ID property to specify we want returned all pages that are children of the current page.

How to output the pages in a list

To output a list of the sub pages we can iterate over the children and output the page titles and links in a list by using the get_page_link function and the ID and post_title properties of the returned Page object.

echo '<ul class="subNav">';
    foreach( $child_pages as $c_page ) {
    echo '<li><a href="' . get_page_link($c_page->ID) . '">' . $c_page->post_title . '</a></li>';
}
echo '</ul>';

The full example

Using the above code we will be executing the code on all website pages including posts and categories. To limit the execution to only pages we can utilize the is_page function. As we want to display the secondary navigation when we are on the parent page or child page we will need to check the current page to determine if we are on a root page or child page by using the post_parent property. Using this we can determine if we need to get the children of the current page or the children of the page’s parent. Doing so allows us to consistently output a Sub Page Navigation as can be seen here.

if( is_page() ) {
    $the_ID = $post->ID;
    $i_page = $post;
    if( $post->post_parent ) {
       $the_ID = $post->post_parent;
       $i_page = get_page($the_ID);
    }
 
    $child_pages = get_pages('child_of='. $the_ID . '&parent=' . $the_ID);
 
    if( count($child_pages) > 0 ) {
        echo '<li class="widget"><h2><a href="' . get_page_link($the_ID) . '">' . $i_page->post_title . '</a></h2><ul>';
        foreach( $child_pages as $c_page ) {
            echo '<li><a href="' . get_page_link($c_page->ID) . '">' . $c_page->post_title . '</a></li>';
        }
        echo '</ul></li>';
    }
}

For some reason I have the utmost trouble remembering how to do a single line or inline if else statement. This article is mainly for myself so hopefully I will remember the next time I want to do an inline if else statement.

The usual format for performing an if else statement is:

if( $val1 > $val2 ) {
    // code executed if true
} else {
    // code executed if false
}

If you are trying to set a variable to a value depending on a condition then it is often the case that an inline if else statement will be sufficient and easier to read. This is done by the use of ?. The full format is (condition) ? (true output) : (false output) or as an example:

$val = ($var1 > $var2) ? $var1 : $var2;

In PHP we are provided with an integrated logging system for outputting info, warnings, and errors. This is accomplished by use of the syslog function. This function outputs the specified message at the specified priority level to the default system logger. If you want to have it output the log message to a user defined log handler then you simply need to utilize the openlog and closelog functions before and after respectively before you log a message.

Example Log Statements

syslog(LOG_ERR, "message to be logged as an error");

openlog("AppLog", LOG_PERROR, LOG_LOCAL0);
 
// code
 
syslog(LOG_WARNING, "warning priority sample log message");
 
// code
 
closelog();

Resources

• PHP.net: syslog function reference

Often as a User we don’t want to use our mouse or tab to the submit button to submit a form on a Web page, instead we may just want to press the enter key on our keyboard. This functionality can be easily completed by performing a bit of JavaScript, personally I find that utilizing JQuery makes the task even easier.

Enabling Enter Button to Submit a Form

To enable the enter button for a form we will create a javascript function that check when a key is pressed when an input element of the form is in focus. This function can then either submit the form or call another javascript function to validate the form.

1. First lets we will start by defining our start-up function that will setup the appropriate keydown handlers on the input fields.

   $(document).ready( function() {
       // code from step 2
   }

2. Second we will need to set the keydown handler for all input fields in our form including select and radio fields. JQuery provides a shortcut selector :input to select these elements.

   $('#formID :input').keydown(function(e) {
       // code from step 3
   }

3. Now time to fill out the logic of the function called when the enter key is pressed. First we will want to verify that we have the event to do this we can check if e is null and get the window.event instead. To shorten this we can simply use the || operator.

   var event = e || window.event;

   Next we check if the key pressed was the enter button by use of the keyCode property.

   if( e.keyCode == 13) {
       // action here
   }

4. The last step is simply put in the action logic whether it is calling a validation script:

   return validateForm();

   or submitting the form directly

   document.formName.submit();

Full Sample Script

$(document).ready( function() {
    $('#myForm :input').keydown(function(e) {
        var event = e || window.event;
        if (e.keyCode == 13) {
           return validateForm();
        }
    });
});

Resources

• JQuery – offical website