Talk In Code

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.

Using server side scripts to search for things can be as complex or as simple as the situation requires. However, if you have a table of results and you just want to enable a simple JavaScript search on that table then this might be the script for you.

To search a table using JavaScript you need to split the table into bits, this can be done using the getElementsByTagName() function, which takes the name of the element that you want to capture. So to grab all of the rows of a table as an array you need to pass the value of tr.

var rows = document.getElementsByTagName("tr");

We can then iterate through these rows, grabbing the column that you want to search on, with the following code.

<form action="#" method="get" onsubmit="return false;">
<input type="text" size="30" name="q" id="q" value="" onkeyup="doSearch();" />
</form>

Next, the table. Note that I have added an additional row to the end of this table. This will be used to display a note to the user if they have entered a query that isn’t found.

<table>
<tr><td>One</td></tr>
<tr><td>Two</td></tr>
<tr><td>Three</td></tr>
<tr><td>Four</td></tr>
<tr><td>Five</td></tr>
<tr><td>Six</td></tr>
<tr><td>Seven</td></tr>
<tr><td>Eight</td></tr>
<tr style="display:none;" id="noresults">
 <td>(no listings that start with "<span id="qt"></span>")</td>
</tr>
</table>

The first thing we need to do in our search function is to prepare the search term. This turns the query string to lowercase, which we can then match to the table column.

var q = document.getElementById("q");
var v = q.value.toLowerCase();

Now we can go through each row value and try to match it to the value in the query string. If it matches then we display the row, if not then we hide it.

  for ( var i = 0; i < rows.length; i++ ) {
    var fullname = rows[i].getElementsByTagName("td");
    fullname = fullname[0].innerHTML.toLowerCase();
    if ( fullname ) {
        if ( v.length == 0 || (v.length < 3 && fullname.indexOf(v) == 0) || (v.length >= 3 && fullname.indexOf(v) > -1 ) ) {
        rows[i].style.display = "";
      } else {
        rows[i].style.display = "none";
      }
    }
  }

Here is the full function, including the code to implement the no results note.

<script type="text/javascript">
//<!--
function doSearch() {
  var q = document.getElementById("q");
  var v = q.value.toLowerCase();
  var rows = document.getElementsByTagName("tr");
  var on = 0;
  for ( var i = 0; i < rows.length; i++ ) {
    var fullname = rows[i].getElementsByTagName("td");
    fullname = fullname[0].innerHTML.toLowerCase();
    if ( fullname ) {
        if ( v.length == 0 || (v.length < 3 && fullname.indexOf(v) == 0) || (v.length >= 3 && fullname.indexOf(v) > -1 ) ) {
        rows[i].style.display = "";
        on++;
      } else {
        rows[i].style.display = "none";
      }
    }
  }
  var n = document.getElementById("noresults");
  if ( on == 0 && n ) {
    n.style.display = "";
    document.getElementById("qt").innerHTML = q.value;
  } else {
    n.style.display = "none";
  }
}
//-->
</script>

Here is a working example of the code, with valid HTML.

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

In MySQL the OPTIMIZE TABLE can be used if you have made changes or have deleted large parts of the table.

Any deleted rows are kept behind the scenes in the server in order to allow the reuse of these spaces. The OPTIMIZE TABLE command reclaims the unused space and defragments the data file.

For a normal MyISAM table the OPTIMIZE command works in the following way.

1. If the table has deleted or split rows, repair the table.
2. If the index pages are not sorted, sort them.
3. If the table’s statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

You can do this automatically by using the following PHP code.

// connect to database
$con = mysql_connect("localhost","root","wibble");
// select the correct database
mysql_select_db("database");
// get a list of the tables
$alltables = mysql_query("SHOW TABLES;");
 
// record the output
$output = array();
 
while($table = mysql_fetch_assoc($alltables)){
 foreach($table as $db => $tablename){
  $sql = 'OPTIMIZE TABLE '.$tablename.';';
  $response = mysql_query($sql) or die(mysql_error());
  $output[] = mysql_fetch_assoc($response);
 };
};
// print output
print_r($output);


Here is a sample of what the output array contains

[0] => Array
(
 [Table] => database.table1
 [Op] => optimize
 [Msg_type] => status
 [Msg_text] => Table is already up to date
)
 
[1] => Array
(
 [Table] => database.table2
 [Op] => optimize
 [Msg_type] => status
 [Msg_text] => OK
)


Note that you don’t need to run this command every time you do anything. You should only run this after a major data upheaval, or after a few months or weeks of usage.

Use the following bit of code to create a web safe colour table. In order to for the name of each colour to be displayed the background colour array is reversed and used to create the foreground colours. This makes white text appear on black backgrounds and visa-versa, the only problem is that in the middle of the table it will display grey on grey.

$bclr = array('00','11','22','33','44','55','66','77','88','99','AA','BB','CC','DD','EE','FF');
$fclr = array_reverse($bclr);
echo '<table>';
for($i=0;$i<16;++$i){
 for($j=0;$j<16;++$j){
  echo '<tr>';
  for($k=0;$k<16;++$k){
   $bcolor = '#'.$bclr[$i].$bclr[$j].$bclr[$k];
   $fcolor = '#'.$fclr[$j].$fclr[$j].$fclr[$j];
   echo '<td style="background-color: '.$bcolor.';color:'.$fcolor.';">'.$bcolor.'</td>';
  }
 echo '</tr>';
 }
}
echo '</table>';

I did try to do this using all of the colours, not just the web safe colours. However, this creates a table with 16,777,216 cells, which simply makes the browser fall over due to the amount of memory required.

$bclr = array('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F');
$fclr = array_reverse($bclr);
$length = count($bclr);
echo '<table>';
for($a=0;$a<$length;++$a){
 for($b=0;$b<$length;++$b){
  echo '<tr>';
  for($c=0;$c<$length;++$c){
   for($d=0;$d<$length;++$d){
    for($e=0;$e<$length;++$e){
     for($f=0;$f<$length;++$f){
      $bcolor = '#'.$bclr[$a].$bclr[$b].$bclr[$c].$bclr[$d].$bclr[$e].$bclr[$f];
      $fcolor = '#'.$fclr[$a].$fclr[$b].$fclr[$c].$fclr[$d].$fclr[$e].$fclr[$f];
      echo '<td style="background-color: '.$bcolor.';color:'.$fcolor.';">'.$bcolor.'</td>';
     }
    }
   }
  }
 echo '</tr>';
 }
}
echo '</table>';

You can try this out if you want with a smaller array, make the colour array something like.

$bclr = array('0','6','C','F');

This creates a table with 4,096 cells, which is much more manageable.