I've decided to put something in here as it took me an age to find out how I could do it.

This is when using a MySQL query within a PHP script.  The process is used often to do a statistics table or top ten chart of your data (eg. movies, music, etc).

My aim is to do the following:

1. retrieve data from a table,
2. count the number of times each data exists,
3. sort it in reverse order so that the most frequent is at the top of the list
4. print out each row with the number of times that particular data appeared in a row

My old method was to:

 I'm beginning a list as I've just spent an age trying to get PHP output to create a text file.  Then my client showed me how she then opens the text file in Excel, so I said we could get the script to do that instead.

PHP to TXT

header('Content-Type: text/plain; charset=utf-8');
header('Content-Disposition: attachment; filename=foo.txt');
echo 'contents of file';

PHP to XLS

$export_file = "my_name.xls"; 
ob_end_clean(); 
ini_set('zlib.output_compression','Off'); 
header('Pragma: public'); 
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT"); 
header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT'); 
header('Cache-Control: no-store, no-cache, must-revalidate');
header('Cache-Control: pre-check=0, post-check=0, max-age=0');
header ("Pragma: no-cache"); 
header("Expires: 0"); 
header('Content-Transfer-Encoding: none'); 
header('Content-Type: application/vnd.ms-excel;'); 
header("Content-type: application/x-msexcel");  
header('Content-Disposition: attachment; filename="'.basename($export_file).'"'); 

If you've ever made PHP scripts to process data within a LAMP environment (Linux, Apache, MySQL, PHP) then this happens a lot.  In the following example, our HTML form will allow the user to specify a date (so excludes hours, minutes and seconds).  For demonstration purposes, I'm going to be using the European date format so DD/MM/YYYY.

Basically you have a HTML form with an input field type of 'FILE' (ie. <input type="file" name="file_to_upload" />) and want a PHP file to process this.  This example applies to a Linux Apache MySQL PHP (LAMP) environment.

I added this article because when I was only trying to look up how to do this, this nut "the Storyteller" went and carried out a benchmark test on the most popular ways of counting the occurrence of a specific word within a string of text.

• SITS:Vision v8.7.0
• Uniface v9.6.03
• Microsoft Windows 7
• Microsoft SQL Server 2012 (SP1) - 11.0.3128.0

• w3schools.com

• Microsoft Windows 7 Enterprise
• SITS:Vision Students (v8.7.0)

-- What I have: no export format
<stu_codc>5826</stu_codc>

-- What I could have: export format set to get lookup value
-- &GCOD_NAME.COD.SRS
<stu_codc>England</stu_codc>

-- What I want: export format to get multiple values
-- &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>]
<stu_codc>5826|England</stu_codc>