Simple HTML Login Form

HTML Form with Post method:

PHP: Exporting Data to Excel

After you've put all that effort into importing your data into and SQL database and connecting it to your website, how do you get it back out and into Excel in order to keep your off-line and on-line systems synchronised?

The following article presents a simple method for downloading any data from PHP into an Excel spreadsheet - or at least something that looks like one.
1. Preparing the data

$data = array(
array("firstname" => "Mary", "lastname" => "Johnson", "age" => 25),
array("firstname" => "Amanda", "lastname" => "Miller", "age" => 18),
array("firstname" => "James", "lastname" => "Brown", "age" => 31),
array("firstname" => "Patricia", "lastname" => "Williams", "age" => 7),
array("firstname" => "Michael", "lastname" => "Davis", "age" => 43),
array("firstname" => "Sarah", "lastname" => "Miller", "age" => 24),
array("firstname" => "Patrick", "lastname" => "Miller", "age" => 27)
);

The first step is to output the data in a tab-delimited format (CSV can also be used but is slightly more complicated). To achieve this we use the following code:

header("Content-Type: text/plain");

$flag = false;
foreach($data as $row) {
if(!$flag) {
# display field/column names as first row
echo implode("\t", array_keys($row)) . "\n";
$flag = true;
}
echo implode("\t", array_values($row)) . "\n";
}

We set the content type to text/plain so that the output can more easily be viewed in the browser. Otherwise, because there is no HTML formatting, the output would appear as a single line of text.

The first line of output will be the column headings (in this case the field names are used). Values are separated with a tab \t and rows with a line break \n. The output should look something like the following:


firstname lastname age
Mary Johnson 25
Amanda Miller 18
James Brown 31
Patricia Williams 7
Michael Davis 43
Sarah Miller 24
Patrick Miller 27


There's already a weakness in this code that may not be immediately obvious. What if one of the fields to be ouput already contains one or more tab characters, or worse, a newline? That's going to throw the whole process out as we rely on those characters to indicate column- and line-breaks.

The solution is to 'escape' the tab characters. In this case we're going to replace tabs with a literal \t and line breaks with a literal \n so they don't affect the formatting:


function cleanData(&$str)
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
}

header("Content-Type: text/plain");

$flag = false;
foreach($data as $row) {
if(!$flag) {
# display field/column names as first row
echo implode("\t", array_keys($row)) . "\n";
$flag = true;
}
array_walk($row, 'cleanData');
echo implode("\t", array_values($row)) . "\n";
}


2. Triggering a download

What many programmers don't realise is that you don't have to create a file, even a temporary one, in order for one to be downloaded. It's sufficient to 'mimic' a download by passing the equivalent HTTP headers followed by the data.

If we create a PHP file with the following code then when it's called a file will be downloaded that can be opened directly using Excel.


function cleanData(&$str)
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
}

# filename for download
$filename = "website_data_" . date('Ymd') . ".xls";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");

$flag = false;
foreach($data as $row) {
if(!$flag) {
# display field/column names as first row
echo implode("\t", array_keys($row)) . "\n";
$flag = true;
}
array_walk($row, 'cleanData');
echo implode("\t", array_values($row)) . "\n";
}
exit;

javascript image viewer

1. JavaScript Image Viewer on click event.

http://www.dynamicdrive.com/dynamicindex4/thumbnail.htm

2. JavaScript Image Viewer on mouse over event.

http://www.dynamicdrive.com/dynamicindex4/thumbnail2.htm

Image resizing tool

Image resizing tool
http://webresizer.com/resizer/

JavaScript Pop Up lightbox

JavaScript Pop Up lightbox:
Just view source the code and save it.
http://www.emanueleferonato.com/downloads/lightbox.html