Mar200919

Top free applications to aid in web design and development

FileZilla FTP Client – http://filezilla-project.org/
A FTP client to upload documents from your computer to your web server. Also supports SFTP for secure transfering of files.

xampp – http://www.apachefriends.org/
A bundled installer of Apache, PHP, MySQL and a few other programs. This allows you to set up a server on your computer accessible through http://localhost which saves you having to upload the files to a server during development.

Notepad++ – http://notepad-plus.sourceforge.net/
An extension of notepad in Windows with more features including syntax highlight for PHP, HTML, javascript and many more languages.

GIMP (GNU Image Maninpulation Program) – http://www.gimp.org/
A worthy alternative to the hugely expensive Photoshop.

Paint.net – http://www.paint.net/
Based on the simplicity of MS Paint, Paint.net takes this a lot further with the introduction of many tools that are present in GIMP and Photoshop.

Oct200812

[PHP + MySQL] Making a basic product database – Part 1

Firstly you need a server which supports PHP and Mysql. If you haven’t got one, you can set up your computer as a local web server. If you are on Windows, you can follow this guide to help you Set up your PC as a test environment using Apache, PHP and MySQL.

Create the database and tables

Open up your database management tool (PHPMyAdmin or command prompt or MySQL Query Browser) and create a new database, I will call mine product_db.

Then we want to create 3 tables. Product, companies and ownership.

The products table will contain 3 fields:

pid – A unique ID that is automatically generated for us.
name – The name of the product.
weight – The weight of the product.

This is the SQL for the table I am creating:

CREATE TABLE products (
pid INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR( 100 ) NOT NULL ,
weight FLOAT NOT NULL
)

Next up is the companies table which will contain 3 fields.

cid – A unique ID that is automatically generated for us.
name – The name of the company.
email – A contact email address for the company.

This is the SQL for the table I am creating:

CREATE TABLE companies (
cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR( 100 ) NOT NULL ,
email VARCHAR(100) NOT NULL
)

The 3rd table is the ownership table which will link up products to companies.

This will contain 2 fields, pid and cid which relate to the automatic ID of the 2 other tables.

This is the SQL for the table I am creating:

CREATE TABLE ownership (
cid INT NOT NULL,
pid INT NOT NULL
)

Now that we have the tables set up we can write the PHP to interact with the tables.

Connecting to the database using PHP

In your web folder, create a file and call it db_config.php.

In this file we will make the connection to the database.

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_pass = 'password';
$db_name = 'product_db';
mysql_connect($db_host,$db_user,$db_pass) or die(mysql_error());
mysql_select_db($db_name) or die(mysql_error());
?>

Change the $db_xxxx variables to your database settings.

Save the file and load it up in your browser. If you get a blank white screen, everything is OK.

Possible Errors

Some possible errors you may get are…

Unknown database 'product1_db'

This will be because $db_name is wrong.

Warning: mysql_connect() [function.mysql-connect]: Unknown MySQL server host 'loc1alhost' (11001) in D:\xampp\htdocs\product\db_config.php on line 3
Unknown MySQL server host 'loc1alhost' (11001)

This is because the $db_host is wrong.

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'ro1ot'@'localhost' (using password: YES) in D:\xampp\htdocs\product\db_config.php on line 3
Access denied for user 'ro1ot'@'localhost' (using password: YES)

This could be because $db_user is wrong or $db_pass is wrong.

Inserting new products in to the database

Create a new file and call it index.php and put it in the same location as the db_config.php file and paste the following code in it:

<?php require_once 'db_config.php'; ?>

Previewing the page should result in a blank white page. This means that everything is OK.

Now we will create a function which will used for all our modifying database interactions such as inserting data, updating data and deleting data.

function db_action($data = array()) {
	switch ($data['action']) {
		case 'insert':
			$keys = array(); $values = array();
			foreach ($data['data'] as $key => $value) {
				$keys[$key] = $key;
				$values[$key] = "'".$value."'";
			}
			$keys = implode(', ', $keys);
			$values = implode(', ', $values);
			mysql_query("INSERT INTO ".$data['table']." (".$keys.") VALUES (".$values.")");
			break;
		case 'update':
			mysql_query("UPDATE ".$data['table']." SET ".$values." WHERE ".$key." = ".$id);
			break;
		case 'delete':
			mysql_query("DELETE FROM ".$data['table']." WHERE ".$key." = ".$id);
			break;
	}
	return;
}

This is set up for inserting data only at the moment, both update and delete would fail at the moment, unless the data was passed on properly.

To insert data into the database we will pass all the information the db_function needs in an array like the one below.

$array = array(
  'action' => 'insert',
  'table' => 'products',
  'data' => array(
    'name' => 'Product Name',
    'weight' => '12.5',
  ),
);

By looking at the db_action function you can see that the action part of the array defines what we want to do in the database (insert, update or delete), then we have the table name (products, companies, ownership) and a data array which is in the format of the key (column name in the database) and the value we want to put in to the database.

We draw this data out the array in to a workable format using the following code:

$keys = array(); $values = array();
foreach ($data['data'] as $key => $value) {
  $keys[$key] = $key;
  $values[$key] = "'".$value."'";
}

This goes through the data array within our main array and spits out the $keys and $values that we will use for the database.

Once we have the $keys and $values arrays we need to organise these in a way that MySQL will know to insert correctly.

$keys = implode(', ', $keys);
$values = implode(', ', $values);

This sets the arrays in to strings with each field separated with a comma. Perfect for MySQL.

We then insert the data in to the database using the following code.

mysql_query("INSERT INTO ".$data['table']." (".$keys.") VALUES (".$values.")");

From the above array, the information sent to MySQL would be:

INSERT INTO products (name, weight) VALUES ('Product Name', '12.5')

Inserting new companies in to the database

By changing the array we can completely change the data that is going in to the database. Now lets modify the array to insert a new company.

$array = array(
  'action' => 'insert',
  'table' => 'companies',
  'data' => array(
    'name' => 'Something Enterprise',
    'email' => 'info@somethingenterprise.com',
  ),
);

So with the following code, by viewing the page we will insert data in to both tables.

<?php require_once 'db_config.php'; ?>
<?php
$action = $_POST['action'] ? $_POST['action'] : $_GET['action'];
$pid = $_POST['pid'] ? $_POST['pid'] : $_GET['pid'];
$cid = $_POST['cid'] ? $_POST['cid'] : $_GET['cid'];
 
function db_action($data = array()) {
	switch ($data['action']) {
		case 'insert':
			$keys = array(); $values = array();
			foreach ($data['data'] as $key => $value) {
				$keys[$key] = $key;
				$values[$key] = "'".$value."'";
			}
			$keys = implode(', ', $keys);
			$values = implode(', ', $values);
			mysql_query("INSERT INTO ".$data['table']." (".$keys.") VALUES (".$values.")");
			break;
		case 'update':
			mysql_query("UPDATE ".$data['table']." SET ".$values." WHERE ".$key." = ".$id);
			break;
		case 'delete':
			mysql_query("DELETE FROM ".$data['table']." WHERE ".$key." = ".$id);
			break;
	}
	return;
}
 
$product = array(
	'action' => 'insert',
	'table' => 'products',
	'data' => array(
		'name' => 'Product Name',
		'weight' => '12.5',
	),
);
 
$company = array(
	'action' => 'insert',
	'table' => 'companies',
	'data' => array(
		'name' => 'Something Enterprise',
		'email' => 'info@somethingenterprise.com',
	),
);
 
db_action($product);
db_action($company);
?>

Inserting new ownership in to the database

Now that we have both a company and a product we can try linking them together. Although we will automate this later, check your database, both the product and company should have an ID of 1.

$ownership = array(
	'action' => 'insert',
	'table' => 'ownership',
	'data' => array(
		'pid' => 1,
		'cid' => 1,
	),
);

Then by running

db_action($ownership);

We have the entry added to the ownership table.

Updating existing data

To update data we just need to modify our function a little to get the data in the right format and we can update with ease.

To do this we update the ‘update’ case in our switch statement to the following:

case 'update':
			$items = array();
			foreach ($data['data'] as $key => $value) {
				$items[] = $key .' = "'.$value.'"';
			}
			$values = implode(' , ', $items);
			$key = $data['key'];
			$id = $data['id'];
			mysql_query("UPDATE ".$data['table']." SET ".$values." WHERE ".$key." = ".$id);
			break;

We are using a similar foreach statement but have changed the 2 arrays into 1 array as the SQL format for UPDATE is different to INSERT.

For the update statement to work we need to pass on the values for the WHERE statement. After a minor change to our array we have the following:

$updateproduct = array(
	'action' => 'update',
	'table' => 'products',
	'data' => array(
		'name' => 'New Product Name',
	),
	'key' => 'pid',
	'id' => 1,
);

We have changed the action and added 2 new array keys, 1 called key which is the column name that we will use in our SQL statement and the id which is the cid or pid that needs to be updated.

Deleting data

The DELETE case in our function is even easier than our UPDATE as we don’t need to much around with any data.

case 'delete':
			$key = $data['key'];
			$id = $data['id'];
			mysql_query("DELETE FROM ".$data['table']." WHERE ".$key." = ".$id);
			break;

Our DELETE array is almost identical to our UPDATE array.

$deleteproduct = array(
	'action' => 'delete',
	'table' => 'products',
	'key' => 'pid',
	'id' => 1,
);

All we need is the key and id as no data is needed to delete a row.

In part 2 we will create the HTML to do our functions.

Sep200825

[PHP] Operators

There are a few different types of operators in PHP and I will cover the most commonly used ones.

Arithmetic Operators

These are list basic maths. The 4 most common are:

Addition

Sum of $a and $b.

<?php
$a = 4;
$b = 3;
echo $a + $b;
?>

Outputs:

7

Subtraction

Difference of $a and $b.

<?php
$a = 4;
$b = 3;
echo $a - $b;
?>

Outputs:

1

Multiplication

Product of $a and $b.

<?php
$a = 4;
$b = 3;
echo $a * $b;
?>

Outputs:

12

Division

Quotient of $a and $b.

<?php
$a = 4;
$b = 3;
echo $a / $b;
?>

Outputs:

1.33333333333

Assignment

An assignment operator assigns a value to a variable name.

<?php
$a = ($b = 10) + 5;
?>

With this $a equals 15 and $b is set to 10.

<?php
$a = 10;
$a += 5;
$b = "hello ";
$b .= "world!";
?>

$a is initially set to 10, then is changed to 15. $b is initially set to ‘hello ‘ then ‘hello world!’ by using the period before the equals (.=).

Comparison

Comparison operators compare 2 values. These are often used in if statements, for example: if $a equals $b then do this.

$a == $b

Returns TRUE if $a is equal to $b.

<?php
$a = 3;
$b = 3;
$c = 2;
var_dump($a == $b); // 3 == 3 is true
var_dump($b == $c); // 3 == 2 is false
var_dump(0 == "s"); // 0 == 0 is true
var_dump("1" == "01"); // 1 == 1 is true
var_dump("1" == "1e0"); // 1 == 1 is true
?>

$a === $b

Returns TRUE if $a is identical to $b.

<?php
$a = 3;
$b = 3;
$c = 2;
var_dump($a === $b); // 3 === 3 is true
var_dump($b === $c); // 3 === 2 is false
var_dump(0 === "s"); // 0 === 0 is false
var_dump("1" === "01"); // 1 === 1 is false
var_dump("1" === "1e0"); // 1 === 1 is false
?>

$a != $b, $a <> $b

Returns TRUE if $a is not equal to $b.

<?php
$a = 3;
$b = 3;
$c = 2;
var_dump($a != $b); // 3 != 3 is false
var_dump($b <> $c); // 3 <> 2 is true
var_dump(0 != "s"); // 0 != 0 is false
var_dump("1" != "01"); // 1 !=1 is false
var_dump("1" <> "1e0"); // 1 <> 1 is false
?>

$a !== $b

Returns TRUE if $a is not identical to $b.

<?php
$a = 3;
$b = 3;
$c = 2;
var_dump($a !== $b); // 3 !== 3 is false
var_dump($b !== $c); // 3 !== 2 is true
var_dump(0 !== "s"); // 0 !== 0 is true
var_dump("1" !== "01"); // 1 !== 1 is true
var_dump("1" !== "1e0"); // 1 !== 1 is true
?>

$a < $b

Returns TRUE if $a is less than $b.

<?php
$a = 3;
$b = 3;
$c = 2;
var_dump($a < $b); // 3 < 3 is false
var_dump($b < $c); // 3 < 2 is false
var_dump(0 < "s"); // 0 < 0 is false
var_dump("1" < "01"); // 1 < 1 is false
var_dump("1" < "1e0"); // 1 < 1 is false
?>

$a > $b

Returns TRUE if $a is greater than $b.

<?php
$a = 3;
$b = 3;
$c = 2;
var_dump($a > $b); // 3 > 3 is true
var_dump($b > $c); // 3 > 2 is true
var_dump(0 > "s"); // 0 > 0 is false
var_dump("1" > "01"); // 1 > 1 is false
var_dump("1" > "1e0"); // 1 > 1 is false
?>

$a <= $b

Returns TRUE if $a is less than or equal to $b.

<?php
$a = 3;
$b = 3;
$c = 2;
var_dump($a <= $b); // 3 <= 3 is true
var_dump($b <= $c); // 3 <= 2 is false
var_dump(0 <= "s"); // 0 <= 0 is true
var_dump("1" <= "01"); // 1 <= 1 is true
var_dump("1" <= "1e0"); // 1 <= 1 is true
?>

$a >= $b

Returns TRUE if $a is greater than or equal to $b.

<?php
$a = 3;
$b = 3;
$c = 2;
var_dump($a >= $b); // 3 >= 3 is true
var_dump($b >= $c); // 3 >= 2 is true
var_dump(0 >= "s"); // 0 >= 0 is true
var_dump("1" >= "01"); // 1 >= 1 is true
var_dump("1" >= "1e0"); // 1 >= 1 is true
?>

Incrementing/Decrementing

Incrementing/Decrementing operators come in 2 forms, pre- and post-increment and decrement.

<?php
echo "Postincrement\n";
$a = 5;
echo "Should be 5: " . $a++ . "\n";
echo "Should be 6: " . $a . "\n";
 
echo "Preincrement\n";
$a = 5;
echo "Should be 6: " . ++$a . "\n";
echo "Should be 6: " . $a . "\n";
 
echo "Postdecrement\n";
$a = 5;
echo "Should be 5: " . $a-- . "\n";
echo "Should be 4: " . $a . "\n";
 
echo "Predecrement\n";
$a = 5;
echo "Should be 4: " . --$a . "\n";
echo "Should be 4: " . $a . "\n";
?>

Outputs:

Postincrement
Should be 5: 5
Should be 6: 6
Preincrement
Should be 6: 6
Should be 6: 6
Postdecrement
Should be 5: 5
Should be 4: 4
Predecrement
Should be 4: 4
Should be 4: 4

Logical

Logical operators return TRUE when variables match given the operator.

$a and $b, $a && $b

<?php
$a = 3;
$b = 3;
$c = 0;
var_dump($a and $b); // Returns true
var_dump($a and $c); // Returns false
var_dump($b && $c); // Returns false
?>

$a or $b, $a || $b

<?php
$a = 3;
$b = 3;
$c = 0;
var_dump($a or $b); // Returns true
var_dump($a or $c); // Returns true
var_dump($b || $c); // Returns true
?>

$a xor $b

<?php
$a = 3;
$b = 3;
$c = 0;
var_dump($a xor $b); // Returns false
var_dump($a xor $c); // Returns true
var_dump($b xor $c); // Returns true
?>

!$a

<?php
$a = 3;
$b = 3;
$c = 0;
var_dump(!$a); // Returns false
var_dump(!$b); // Returns false
var_dump(!$c); // Returns true
?>

String

String operators are the same as some of the assignment operators.

<?php
$a = "Hello ";
$b = $a . "World!"; // now $b contains "Hello World!"
 
$a = "Hello ";
$a .= "World!"; // now $a contains "Hello World!"
?>

The first use the concatenation operator (period) to concatenate both strings together. The second uses the concatenation assignment operator which appends the 2nd argument to the first.

For more information about operators in PHP, visit the PHP.net website which lists more operators and their uses.

Sep200824

[PHP] Introduction

Syntax

When writing PHP code it is best to use the following opening and closing statements:

<?php
?>

On some websites and scripts you may see it written like this:

<?
?>

This is not good practice as not all servers will be set up to allow this shorthand tag.

File extension

Files with PHP in should generally have the file extension .php but depending on the server configuration, other extensions can be used such as .php3, .php4, .php5, .html and more.

Closing statements

All commands and statements in PHP have to be closed before you can make another so that PHP knows where one item ends and the next one starts.

For example, an echo statement should end with a semi-colon:

<?php
echo "hello";
?>

Where as an if statement just needs curly brackets around it:

<?php
if ($item > 4) {
  echo $item.'<br />';
}
?>

Comments

Comments in PHP can be done in a few ways.

Single line comments

Single line comments are comments that only span one row, in our echo statement above we could add a comment after the echo to explain what our node does.

<?php
echo "hello"; // prints out hello on the screen
?>

All the user will see on their screen and the source code (when viewing source from their browser) for this is:

hello

We can also stop code from showing by adding comments before the line of code.

<?php
// echo "hello";
?>

In this case the user won’t see anything as we have commented out the line.

You can also use the following for single line comments:

<?php
# echo "hello";
?>

Multi line comments

Multi line comments are comments that span more than one line by using:

<php
/* This is the first line of our comment
  followed by another line
  and finally a third line */
echo "hello";
?>

You can also use this commenting way to take out blocks of code temporarily, like so:

<?php
/*
if ($item > 4) {
  echo $item.'<br />';
}
*/
?>

Whitespace

Much like HTML, whitespace is not important and is ignored and is purely for the presentation to the developer. The following 2 bits of code both produce the same HTML.

<?php
echo "hello";
echo "hello";
?>

Outputs:

hellohello
<?php
echo "hello";
 
 
 
 
 
echo "hello";
?>

Outputs:

hellohello

Variables

Variables are what you can use to store bits of information to reuse them later on in your script. For example:

<?php
$variable_name = 'value';
echo $variable_name;
?>

Outputs:

value

Variable names have certain restrictions that you must keep to for your script to work as expected.

  • Must start with either a letter or an underscore (_)
  • Must only contain alphanumerics (A-z (upper and lower), 0-9 and underscore (_)
  • Variables are case sensitive. $myVar is different to $myvar

Outputting data

There are 2 basic ways of outputting strings and variables. These are echo and print. For the sake of argument they are exactly the same until you get more advanced in PHP and even then you may not have any use for the extra ability of print.

<?php
print 'hello<br />';
echo 'hello';
?>

Outputs:

hello
hello

I tend to stick to using echo.

Echoing strings and variables

The format of echoing strings and variables does confuse a lot of people and early on it is understandable.

1
2
3
4
5
<?php
echo "This is a statement with \"a quote in\".<br />";
echo 'This is a statement with "a quote in".<br />';
echo 'This is a statement with \'a quote in\'.<br />';
?>

Take note of the format of both of the above lines. Line 2 is enclosed using ” ” whereas line 3 is using ‘ ‘. If you have to use quotation marks within a string then you should either enclose the string with the alternative quotes or escape them with the backwards slash (\).

Echoing strings can have it’s moments too. Example:

<?php
$myVar = 'this is a string';
echo 'Variable: $myVar<br />';
echo 'Variable: '.$myVar.'<br />';
echo "Variable: $myVar<br />";
echo "Variable: ".$myVar."<br />";
?>

Outputs:

Variable: $myVar
Variable: this is a string
Variable: this is a string
Variable: this is a string

From the output we can see the last 3 echo statements are correct and we get the result we expect. But the first one does not. This is because variables can’t be called from within ‘ ‘ quotes but they can be called from within ” ” quotes.

To get around this we close the ‘ ‘ and use a . (dot, full stop, period) to concatenate the variable to the string.

Double quotes are also more helpful if using certain escaped characters.

<?php
echo "this\nis\na\nnew\nline";
echo 'this\nis\na\nnew\nline';
?>

Outputs:

this is a new line
this\nis\na\nnew\nline

Source code:

this
is
a
new
line<br />this\nis\na\nnew\nline

Other enclosed characters include:

  • Newline: \n
  • Carriage return: \r
  • Tab: \t
  • Dollar: \$
  • Double quote: \”

In the case of a backwards slash being used before a character not shown in the list above the backwards slash will be shown as well as the letter succeeding it, example:

<?php
echo "\s";
?>

Outputs:

\s
Sep200822

Set up your PC as a test environment using Apache, PHP and MySQL

You can approach the set up of a test environment in 2 seperate ways. You can manually set up Apache/IIS, PHP and MySQL seperately then configure them to work together or you can use a package such as WAMP (Apache, MySQL, PHP) or XAMPP (Apache, MySQL, PHP, Perl).

There are benefits to manually setting up each bit of software for the purpose of the test server we will just use Xampp.

XAMPP Installation

Go to the XAMPP website and download the correct version for your operating system. Depending on your operating system the download can vary from ~20MB to ~100MB.

Windows users

Download page

Linux users

Download page

Mac OS X users

Download page

Download the Installer package and install using the instructions on the download page.

Once installation has finished, start Apache and MySQL and set up the admin password’s to make it secure using the instructions on the download page.

XAMPP Configuration

First we need to change a few settings in Apache and PHP to make Drupal work a bit better.

Locate the directory you installed XAMPP to and open the apache folder, followed by the conf folder. Open up the httpd.conf file in your favourite plain text editor.

Around line number 118, you should see a line similar to the following:

#LoadModule rewrite_module modules/mod_rewrite.so

If you search for rewrite_module you should be able to find it. 

Change this to:

LoadModule rewrite_module modules/mod_rewrite.so

Make note that we have removed the hash (#) which commented out the line.  We need the rewrite module so that we can have friendly URLS such as http://www.domain.com/this/is/friendly rather than http://www.domain.com/?first=this&second=is&third=friendly.

Save and close the file, then location the php folder within the XAMPP directory. In this directory is a file called php.ini. Open this file in your favourite text editor.

Find the line (should be around line 246):

memory_limit = 32M      ; Maximum amount of memory a script may consume (16MB)

Change this to:

memory_limit = 96M      ; Maximum amount of memory a script may consume (16MB)

Also make sure safe_mode is set to Off.

safe_mode = Off

This should be around line 168.

Save and close the file. 

In the XAMPP control panel click the Stop button next to Apache and wait around 10-15 seconds. Then press Start. Go to http://localhost or http://127.0.01, if it loads up properly then we haven’t broken anything yet.

XAMPP Testing

Back again in the XAMPP installation directory, create a folder called test. In this folder, create a file called index.php. In this file, type the following:

<?php print 'Hello World!!'; ?>

Now direct your browser to http://localhost/test/ or http://127.0.0.1/test/.

You should see the text:

Hello World!!

If so, success.  You have now set up XAMPP correctly to enable you to install most open source PHP software.

Sep200822

[Drupal] Overview

What is Drupal?

Drupal is an open source CMF (Content Management Framework) created by Dries Buytaert.

What is the difference between a CMS and a CMF?

Not a lot but Drupal is refered to as a CMF due to is configurability and customisation that you are able to do through it’s excellent back end and through the use of third party contributed modules as well as modules created by yourself.

How do I get Drupal?

Go to http://drupal.org and download which ever version you want.

Which version do I want?

Version 6 is the latest stable release but isn’t widely used due to a lot of the contributed modules not being available.  But given time this has changed and using 6 on a live site has become more feasible.

What do I need to run Drupal?

You need a web server that supports PHP and either a MySQL or PostgreSQL database.

Drupal supports both IIS and Apache, with Apache being the prefered and most tested web server.

Which version of PHP do I need?

It is recommended for Drupal 5.1 onwards to use PHP 5.2 to make use of certain new PHP functions.

What PHP configuration do I need to run Drupal properly?

To run Drupal with good performance the PHP memory limit should be set to no less than 8MB, with 16MB or more being prefered.  Some contributed modules can need up to 96MB memory limit.

register_globals needs to be off.

session.save_handler set to user.

error_reporting set to E_ALL & ~E_NOTICE.

Additionally session.cache_limiter is recommened to be set to nocache.

If you have any other questions about the requirements of Drupal, please read the System requirements on the Drupal.org website.

Sep200822

[MySQL] Using MySQL to analyze web usage from Windows ISA logs

This tutorial will teach you how to import a tab delimited file in to a MySQL database. I built a program to do this for Windows ISA Server logs which contained over 500,000 lines which was way too many for Microsoft Excel to handle and too much for notepad to handle properly. What MySQL functions will we be using:

  • Load Date Local Infile
  • Select
  • Delete
  • Drop

Firstly I will show you how to put a tab delimited log from Windows ISA Server in to the MySQL database. With the logs that I have been working with a days worth of information has been between 500,000 and 700,000 which is around 150-200MB in size alone.

You will find that if you use the default PHP timeout settings that you will timeout with large operations such as importing such a large file in to MySQL. 

I suggest that you open a command prompt and run through the process below to import the file and see how long it takes to import. If you have previous logs, choose the biggest one so you can get a worst case scenario. 

Before we can import the data into a MySQL database we need a database and table to import the data in to. I am not going to go in to much depth for this as it is quite simple to do using PHPMyAdmin or a similar program.

For the ISA log table you can use my code that I have here:

CREATE TABLE `tbl_log1` (
`id` int(11) NOT NULL auto_increment,
`ip` varchar(100) collate latin1_general_ci NOT NULL,
`username` varchar(100) collate latin1_general_ci NOT NULL,
`browser` varchar(100) collate latin1_general_ci NOT NULL,
`date` varchar(100) collate latin1_general_ci NOT NULL,
`time` varchar(100) collate latin1_general_ci NOT NULL,
`isa` varchar(100) collate latin1_general_ci NOT NULL,
`blank` varchar(100) collate latin1_general_ci NOT NULL,
`proxy` varchar(100) collate latin1_general_ci NOT NULL,
`proxyip` varchar(100) collate latin1_general_ci NOT NULL,
`proxyport` varchar(100) collate latin1_general_ci NOT NULL,
`num1` varchar(100) collate latin1_general_ci NOT NULL,
`num2` varchar(100) collate latin1_general_ci NOT NULL,
`num3` varchar(100) collate latin1_general_ci NOT NULL,
`protocol` varchar(100) collate latin1_general_ci NOT NULL,
`method` varchar(100) collate latin1_general_ci NOT NULL,
`url` varchar(255) collate latin1_general_ci NOT NULL,
`type` varchar(100) collate latin1_general_ci NOT NULL,
`num4` varchar(100) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci 

Feel free to change the encoding type if you wish. 

Now for the code. To import the data we will use the Load Data Local Infile ability of MySQL. This will load a file that is locally on the machine to the MySQL database given the right parameters.

load data local infile 'C:\\logfile.log'
into table tbl_log1
fields terminated by '\t'
lines terminated by '\n'
(ip,username,browser,date,time,isa,blank,proxy,proxyip,proxyport,num1,num2,num3,protocol,method,url,type,num4)

The last line is a list of the columns in order that are in the log. The only columns we really want are username, date, time and url, although you may want ip as well if you are monitoring network activity on static IP address so you can see where most of the bandwidth is being used. If you are importing a different file then change the columns to the names of your columns in your delimited file. The names of the columns within the brackets are the names that you gave them in the table. 

For example if you have a list of usernames, passwords and emails you will want 3 columns and a last line looking like this:

(username,password,email)

The column names have to be in order. If email is the first column in the file then make it the first word within the brackets. 

The 1st line should be the same for everyone except for the location of the file. Of course using the load data local infile function the file has to be on the machine itself and can not be done through the network. Also note the 2 backward slashes, the first backslash is to espace the following backslash.

The 2nd line is the table that we are importing to, make sure you have set up a table for your data to go in to. 

The 3rd line sets the delimiter type. \t is the delimiter for tab and \r is for a space. I am unaware of what the others are so if anyone knows please let me know.

The 4th line sets the delimiter for the end of the line. In this case each line was a new row on the file in excel so this is \n for new line.

The 5th line is the list of columns that we are importing into. 

The only problem is if you are importing a large log file like I was. What I had to do was cut out all the rubbish that I didn’t need to make the databases faster for queries and the like.

First of all I deleted all the columns I didn’t need.

ALTER TABLE `tbl_log1`
DROP `ip`,
DROP `browser`,
DROP `isa`,
DROP `blank`,
DROP `proxy`,
DROP `proxyip`,
DROP `proxyport`,
DROP `num1`,
DROP `num2`,
DROP `num3`,
DROP `protocol`,
DROP `method`,
DROP `type`,
DROP `num4` 

This took out all of the columns I didn’t need and brought the size of the file down quite a bit.

The second thing was that the log I had, had a lot of anonymous results in it, roughly around a third of the rows. So I ran this little bit of code to remove all the anonymous results.

DELETE FROM tbl_log1 WHERE username like "%anonymous%" 

The problem with this is removed a lot of these results leaves a lot of overhead in the database. So you can either optimize the table after each delete query or you can run it at the end once you start searching. 

OPTIMIZE TABLE tbl_log1

This will remove the overhead on the table and will decrease the query times. 

You can now use queries to search for users internet usage or a certain websites users. You will find a lot of websites in the list, if a user typed in www.microsoft.com it wouldn’t just say www.microsoft.com in the log, it would have the url of every image that the user downloaded, every CSS file, every page….everything. 

You may also want to set up other queries to remove certain websites that you know are safe and don’t mind about such as www.microsoft.com:

DELETE FROM tbl_log1 WHERE url like "%.microsoft.%" 

To search for anyone going on the BBC website you could type the following:

SELECT username FROM tbl_log1 WHERE url like "%bbc.co.uk%" 

This will display a list of users that have been on BBC. But this will come up with multiple duplicates of the usernames due to the issue stated above. To stop this we can add the GROUP BY parameter to our query to group the usernames together so we only have a single result from each user. 

SELECT username FROM tbl_log1 WHERE url like "%bbc.co.uk%" GROUP BY username

This tutorial was originally posted on Skeletorscorpse.com

Apr200824

Zend: addDisplayGroup() – Setting the legend

Original Code:

$nok = $this->addDisplayGroup(array(‘nok_forename’,'nok_surname’,'nok_day’,'nok_month’,'nok_year’,
‘nok_address1′,’nok_address2′,’nok_town’,'nok_county’,'nok_postcode’,'nok_country’),
‘nextofkin’);

I then set about trying to get a legend on the fieldset that was created. My first thought was:

$nok->setLegend(‘Next of Kin’);

But on second thoughts that would never work. I then remembered that the third variable is normally options passed in an array. So…

$nok = $this->addDisplayGroup(array(‘nok_forename’,'nok_surname’,'nok_day’,'nok_month’,'nok_year’,
‘nok_address1′,’nok_address2′,’nok_town’,'nok_county’,'nok_postcode’,'nok_country’),
‘nextofkin’,array(‘legend’=>’Next of Kin’));

This works. :)