Talk In Code

There are numerous ways to print out dates and times, and many hours of programming are spent in converting dates from one format to another.

To do this in MySQL you use the STR_TO_DATE() function, which has two parameters. The first parameter is the time to be parsed and the second is the format of that time. Here is a simple example that converts one date format to a MySQL formatted date string.

SELECT STR_TO_DATE('[21/Apr/2009:07:14:50 +0100]', '[%d/%b/%Y:%H:%i:%S +0100]');

This outputs 2009-04-21 07:14:50.

Using this function is quite intuitive and means that you can convert between time formats very easily. Here are a few more examples of this function.

SELECT STR_TO_DATE('21/04/2009', '%d/%m/%Y'); // 2009-04-21
SELECT STR_TO_DATE('04/21/2009', '%m/%d/%Y'); // 2009-04-21
SELECT STR_TO_DATE('2009-04-21 14', '%Y-%m-%d %H'); // 2009-04-21 14:00:00

If you enter a date that can’t be translated then you will get an error, take the following code that tries to convert a minute value of 87.

SELECT STR_TO_DATE('2009-04-21 14:87', '%Y-%m-%d %H:%i');

The following error message is returned.

Incorrect datetime value: '2009-04-21 14:87' for function str_to_date

This error can be quite easy to reproduce. For example, if you wanted to parse a time that was in 24 format, you would use the %H for hours. Using the %h value for hours will produce exactly this error.

The following table lists all of the different parameters that are involved in the date formatting features within MySQL. Use this to parse your own dates.

Apache can be set up to log all sorts of information

As of Apache 2.2 the basic log file format that a fresh install of Apache will produce will have the following format:

%h %l %u %t "%r" %>s %b

Which doesn’t mean a lot to the uninitiated, so here is a short explanation of each.

• %h – The remote host. This is the IP address of the user connecting to the server.
• %l – The remote logname. This is not always present.
• %u – The remote user from auth (nothing if authentication is not used).
• %t – The time in a common log format.
• "%r" – The first line of the request, basically the method used (GET/POST) the URL that was accessed and the HTTP protocol level that was used. This is enclosed in quotes.
• %>s – %s returns the status of the original request request. For some requests Apache will internally create a secondary request, so %>s prints out the last request staus.
• %b – This is the number of bytes transmitted to the user.

This would produce the following sort out output.

127.0.0.1 - - [17/Apr/2009:14:12:20 +0100] "GET / HTTP/1.1" 200 515

This information can be converted into a database format by using the LOAD DATA command. First, lets create the table we need to store this log format.

DROP TABLE IF EXISTS `test`.`apachelog`;
CREATE TABLE  `test`.`apachelog` (
  `remote_host` varchar(17) DEFAULT NULL,
  `remote_logname` varchar(45) DEFAULT NULL,
  `remote_user` varchar(45) DEFAULT NULL,
  `time1` varchar(22) DEFAULT NULL,
  `time2` varchar(7) DEFAULT NULL,
  `first_line_of_request` text,
  `last_request_status` varchar(4) DEFAULT NULL,
  `bytes_sent` varchar(10) DEFAULT NULL
)

Here is the command that is used to convert the log file into that table.

LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Apache2.2/logs/access.log' INTO TABLE apachelog
FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY 'rn';

Note that our table contains two fields for the time. This is because of two factors. The first is that each field is defined by a space, and because the time value contains a space it is split into two fields. The second is that although we say OPTIONALLY ENCLOSED BY '"' to stop the %r output being split apart, we can’t give the LOAD DATA command more than one of these fields. As a result the time is split into the two fields, so we just create a table with more than one field for time to accommodate this.

To improve the table we can use a different output format. Take the following slight alteration to our log file format.

LogFormat "%h,%l,%u,%t,"%r",%>s,%b"

This line can be found in your http.conf file or your httpd-vhosts.conf if you have set up virtual hosts.

This will cause our output to look like the following:

127.0.0.1,-,-,[20/Apr/2009:14:42:01 +0100],"GET / HTTP/1.1",200,515

We can now change out two time columns to a single one, setting the datatype to VARCHAR(30) and using the following LOAD DATA syntax to load our data.

LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Apache2.2/logs/access.log' INTO TABLE apachelog
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY 'rn';

This gives us a much better set of data in our table.

You can also use the following three MySQL commands to convert from the old table format to the new one.

ALTER TABLE apachelog CHANGE time1 time VARCHAR(40);
UPDATE apachelog SET time = CONCAT(time,' ',time2);
ALTER TABLE apachelog DROP COLUMN time2;

The time2 column will now no longer exist.

If you are running a JavaScript example page you can use the following function that will take the last script element on the page and print it out in a code tag. It uses JQuery to do the work, so you will need to include that library before using this function.

<script type="text/javascript">//<![CDATA[
 function displaySource(name) {
  $('<code>'
   + $('#display-' + name).prevAll('script').eq(0).html()
   .replace(/^\s*|\s*$/g, '')
   .split('\n').slice(1, -1).join('\n')
   .replace(/(^|\n) /g, '$1')
   .replace(/('[^']*')/g, '<em>$1</em>')
  + '</code>')
  .insertAfter('#display-' + name);
 }
//]]></script>

The function works by selecting the current script tag and finding all script elements before it. It then selects the first one it finds and outputs the contents to a code tag. It uses a few regular expressions to convert some of the characters to a more human readable format. The function is called like this.

<script type="text/javascript" id="display-test">displaySource("test");</script>

Lots of applications require a user to input a URL and lots of problems occur as a result. I was recently looking for something that would take a URL as an input and allow me to make sure that is was formatted properly. There wasn’t anything that did this so I decided to write it myself.

The following function takes in a URL as a string and tries to clean it up. It essentially does this by splitting is apart and then putting it back together again using the parse_url() function. In order to make sure that this function works you need to put a schema in front of the URL, so the first thing the function does (after trimming the string) is to check that a schema exists. If it doesn’t then the function adds this onto the end.

function tidyUrl($url){
 // trim the string
 $url = trim($url);
 // check for a schema and if there isn't one then add it
 if(substr($url,0,5)!='https' && substr($url,0,4)!='http' && substr($url,0,3)!='ftp'){
  $url = 'http://'.$url;
 };
  parse the url
 $parsed = @parse_url($url);
 if(!is_array($parsed)){
  return false;
 }
 // rebuild url
 $url = isset($parsed['scheme']) ? $parsed['scheme'].':'.((strtolower($parsed['scheme']) == 'mailto') ? '' : '//') : '';
 $url .= isset($parsed['user']) ? $parsed['user'].(isset($parsed['pass']) ? ':'.$parsed['pass'] : '').'@' : '';
 $url .= isset($parsed['host']) ? $parsed['host'] : '';
 $url .= isset($parsed['port']) ? ':'.$parsed['port'] : '';
 // if no path exists then add a slash
 if(isset($parsed['path'])){
  $url .= (substr($parsed['path'],0,1) == '/') ?   $parsed['path'] : ('/'.$parsed['path']);
 }else{
  $url .= '/';
 };
 // append query
 $url .= isset($parsed['query']) ? '?'.$parsed['query'] : '';
 // return url string
 return $url;
}

The parse_url() function should return an array is successful, if it doesn’t then the function checks for this and returns false.

This function is also useful if you want to keep a standard format to any URL that you store. To make this easier in the long term you should store any domain URL with the trailing slash. If none is added by the user then the function adds it onto the end.

I found a good function that details how to format numbers with commas in JavaScript and thought I would reproduce it here. It basically takes any number and turns it into formatted string with the thousands separated by commas. The number_format() function is available in PHP and I have been relying on PHP to sort out the formatting before returning it via an AJAX call. I can now include this function into my JavaScript library and do this on the client side.

The function works by using regular expressions to first split the string into whole number and decimal, before splitting the number by groups of three digits.

The regular expression used is (\d+)(\d{3}), which looks for a sequence of three numbers preceded by any numbers. This is a little trick that causes the regular expression engine to work from right to left, instead of left to right.

function addCommas(nStr){
 nStr += '';
 x = nStr.split('.');
 x1 = x[0];
 x2 = x.length > 1 ? '.' + x[1] : '';
 var rgx = /(\d+)(\d{3})/;
 while (rgx.test(x1)) {
  x1 = x1.replace(rgx, '$1' + ',' + '$2');
 }
 return x1 + x2;
}

This causes a comma to be added to every third numeric character. So for the number 123456 the string 123,456 is returned. Here are some more examples.

addCommas(1000); // 1,000
addCommas(12345); // 12,345
addCommas(1234567890); // 1,234,567,890
addCommas(12345.1234); // 12,345.1234

The site also details the creation of formatted numbers with the inclusion of different parameters. This means that instead of using a comma to separate block of three numbers you can use anything.

function addSeparatorsNF(nStr, inD, outD, sep){
 nStr += '';
 var dpos = nStr.indexOf(inD);
 var nStrEnd = '';
 if (dpos != -1) {
  nStrEnd = outD + nStr.substring(dpos + 1, nStr.length);
  nStr = nStr.substring(0, dpos);
 }
 var rgx = /(\d+)(\d{3})/;
 while (rgx.test(nStr)) {
  nStr = nStr.replace(rgx, '$1' + sep + '$2');
 }
 return nStr + nStrEnd;
}

This function was created because not every number is formatted in the same way. For example, the number 1234 might be formatted as 1,234 or even 1.234.

The function takes the following arguments

• nStr : This is the number to be formatted. This might be a number or a string. No validation is done on this input.
• inD : This is the decimal character for the string. This is usually a dot but might be something else.
• outD : This is what to change the decimal character into.
• sep : This is the separator, which is usually a comma.

The function takes the input string and splits it into two parts based on the decimal point character. The same regular expression is used on the string before the decimal point as in the previous function, the major difference is that the separator is taken from the function arguments.

Here are a few examples.

alert(addSeparatorsNF(1234567890,'.','.',',')); // 1,234,567,890
alert(addSeparatorsNF(12345.1234,'.','POINT','COMMA')); // 12COMMA345POINT1234
alert(addSeparatorsNF(12345.1234,'5','POINT','COMMA')); // 1COMMA234POINT.1234
alert(addSeparatorsNF('1234,56',',','.',',')); // 1,234.56