CSVRead - Read a database and display the results in a web page

Read, filter, search and sort information from a CSV database, then using a link or virtual include display the data in the same look and feel as your website. The advanced version has enhanced filtering and can split the results across multiple pages. (CGI/Perl Unix)

Note : as of recent, CSVRead and CSVSearch are the same scripts.  It might help you to have a look at the CSVSearch instructions too.

Overview of the script
Set-up the script
Create your database
Create your HTML template
Using a table in your template
Include links, emails or images in the template
Running the script

 


Overview of the script

CSVread:
Using a link, or a virtual include in a web page to the CSVread script, the script will look at the database, filter and display the results in the format of your template.

CSVread Xtra:
With CSVread Xtra you have many more filters and you can split the results over several pages.

Current versions:
Basic 28/Oct/2006
XTra 17/Aug/2005

Files you will need:
CSVread script (download from this website)
Template to show results (instructions on this page)
Database (instructions on this page)
Link or virtual include (on any standard web page)

Things you have to do:
Follow the instructions below
Download the CSVread script and make a few changes
Create a link or virtual include in your webpage
Create a database (or let CSVwrite do it!)
Create a 'template' in your own style
Upload the script and webpages

Minimum Requirements:
Your own website with FTP access
Permission to run CGI scripts on your server
UNIX web hosting with Perl 5.006001 or greater

Features CSVread Xtra
For commercial and private use YES YES
Can be used on as many websites as I build YES YES
No adverts or links in the script YES YES
Instant download YES YES
Display all the database in your web page YES YES
Customise the look using a template YES YES
Display information using a virtual include YES YES
Display information using a link YES YES
Use more than one filter at the same time YES YES
Number of filters 5+ 30+
Sort data ascending or descending by any field - YES
Split the results over many pages - YES
Set the number of results per page - YES
Advanced page selector - YES
Show the number of records YES YES
Show the number of matching records YES YES
Option to change the delimiter - YES
Select the default order results are displayed in - YES
Option to sort results by date - YES
Use different templates with the same script - YES
Use different database with the same script - YES
Use unique ID to display one record in the database YES YES
Highlight text in search results in five colours - YES
Exclude fields from searching - YES
Search on numeric ranges from your form (ez_range) - YES
use HTML includes in templates - YES
show the results using columns (table) - YES
Separate template for 'no matches' page - YES


Setting Up the Script - CSVsearch & CSVread

Check your path to perl
#!/usr/bin/perl

Change the PATH of your database.
If it is in the same directory as the script this should be okay.
my $CSV_file = "database.txt";

Change the PATH of your template.
If it is in the same directory as the script this should be okay.
my $HTML_template = "template.htm";

Name of the script, or FULL URL
my $scriptname = "csvread.pl";

Message if no records are found.
my $no_matches_found = "Sorry, no results found";

Advanced Settings ID 

You can link to and display just one record in the database 'show=X' (X is the value of the ID field in the database) To do this one field in your database will need to have a 'unique ID'. CSVwrite has a feature to write a 'unique ID'.

To use the unique ID feature set this to 1 or 0 not to use the feature.
my $ID_use = 1;

Set this to the field name that has the unique ID
my $ID_field_name = "ID";

Upload and CHMOD 755, or 777 if not public.

Back to Top


CSVsearch Xtra & CSVread Xtra

Check your path to perl
#!/usr/bin/perl

This is the URL (Not PATH) to the script
my $scriptname = "csvread.pl";

Change the PATH of your database.
If it is in the same directory as the script this should be okay.
my $CSV_file = "database.txt";

Change the PATH of your template.
If it is in the same directory as the script this should be okay.
my $HTML_template = "template.htm";

Message if no records are found.
my $no_matches_found = "Sorry, no results found";

Number of random records to display
my $num_random = 5;

Number of records per page
my $records_per_page = 5;

To command override the default number of records
my $field_name_records = 'records';

Text for 'previous' button
my $textPrevious = "Previous";

Tex for 'next' button
my $textNext = "Next";

Number of pages in the range at any time. See the instructions for the template for the code to add the page selector anywhere on your page.
my $pageSelectorRange = 10;

Select the default order results are displayed in.
1 for newest first and 0 for oldest first.
my $order_desc = 1;

If you wish to use a different delimiter in the database to the pipe you can change it. However the pipe is the standard for web database scripts.
my $separator = '|';

When using the 'search=x' feature you can set the minimum number of characters the user must input. 
my $min_query_length = 0;
my $min_query_length_error = "The minimum characters is $min_query_length!";


When running the script with no requests choose 0 to display all the database or 1 to display your $no_matches_found message.
my $default_show_mode = 0;

If you want to use an image rather than the HTML search button change this to 1 to allow an image or 0 not to allow the image search button.
my $kill_image_buttons_value = 0;

You can sort and filter by the Date format as written by CSVwrite Xtra or input the date into a field yourself. Input the fields that contain dates. 2003.10.19 or 2003/10/19 or 2003-10-19.
my @DateFields = ('DATE','DATE2');

If you do not want fields to be searched then you can exclude them from being searched by users.
my @no_search_fields = ("fieldname","fieldname");

If you have a field containing financial amounts in US style notation (e.g. : 100,000) then enter the field names here to make the script treat them as numbers (for sorting, etc...)
my @currency_fields = ('fieldname1', 'fieldname2');

If you server is in another time zone and you wish to correct for that, enter the difference in number of hours here.
my $server_timezone_offset = 0;

Advanced Settings 'No Matches' template

It is possible to have the script show a custom page when no matches are found, instead of printing its own default 'no matches' page.  Includes (see further on) and also certain tags
(like [[#_search]], ...) will work on this custom page.

If you want to use your own custom page, then set this to 1:
my $use_no_matches_template = 0;

Enter the PATH to your custom 'no matches' page. 
my $no_matches_template = 'noresults.htm'; 

Advanced Settings Highlighting

You can request the script to 'Highlight' the results of a search with a different colours. Use 1 to switch this feature on a 0 to switch it off.

You can also switch this feature on/off with a link csvread.pl?hl=off or csvread.pl?hl=on. Any fields that are used in HTML code or you do not wish to highlight can be switched off.
my $highLighting = 1;
my @highLightColors = ("#FFFF00", "#FF0000", "#00FFFF", "#00FF00", "#C0C0C0");
my @no_highlight_fields = ('DATE','ID');


Advanced Settings ID 

You can link to and display just one record in the database 'show=X' (X is the value of the ID field in the database) To do this one field in your database will need to have a 'unique ID'. CSVwrite has a feature to write a 'unique ID' when it writes a record.

To use the unique ID feature set this to 1 or 0 not to use the feature.
my $ID_use = 1;

Set this to the field name that has the unique ID
my $ID_field_name = "ID";

Advanced Settings Columns

Use this if you want to display your records in a table, one database record per column.  Set the following option to 1 in order to enable columns:
my $use_columns = 0;

Define the number of columns in the table:
my $columns = 2;

Define the table properties:
my $tableWidth ="100%";
my $tableBorder = 0;
my $tableCellpadding = 6;
my $tableCellspacing = 0;
my $tableStyle = "";
my $tableBorderColor = "teal";
my $tableBgColor = "#ffffff";


Advanced Feature Extra Database and Template

If you want to use the same script but use a different database or template you can. You put the location of the files in the script rather than in your form or link. You can have as many or few as you like.
$DB{'db1'} = '/path/to/your/database1.txt';
$DB{'db2'} = '/path/to/your/database2.txt';
$DB{'db3'} = '/path/to/your/database3.txt';
$DB{'db4'} = '/path/to/your/database4.txt';
$DB{'db5'} = '/path/to/your/database5.txt';

$TMP{'tp1'} = '/path/to/your/template1.htm';
$TMP{'tp2'} = '/path/to/your/template2.htm';
$TMP{'tp3'} = '/path/to/your/template3.htm';
$TMP{'tp4'} = '/path/to/your/template4.htm';
$TMP{'tp5'} = '/path/to/your/template5.htm';

Advanced Settings Include Pages

It is now possible to use HTML includes from the template.  Once you configured this, the script will replace each of occurence of [[includex]] by the corresponding HTML page/file.
my $use_includes = 0;

If you want the script to cast an error when one of the templates could not be loaded, then set this value to 1:
my $error_on_missing = 0;

You can start defining which includes the script can use:
my %INCL=();
$INCL{'include1'} = "main-menu.htm";
$INCL{'include2'} = "main-menu.htm";
$INCL{'include3'} = "main-menu.htm";
$INCL{'include4'} = "main-menu.htm";
$INCL{'include5'} = "main-menu.htm";



Upload and CHMOD 755, or 777 if not public.

[back to the top]


Create your database

You can create your database using notepad, but you don't need to!

The first time you run CSVwrite it will create the database for you, make sure you put some data in each field when you first run your form.

With the script you will get a sample database, you can edit this by adding new fields and records using CSVedit.

If you do create your own, don't use any special characters or spaces in the field names. Keep them short as your browser can only send a limited number of characters to the database at any one time. Ensure they match your form fields exactly. The size limitations of your database are determined by your web hosting and the server specifications.

Example database:
The first line is the heading and the rest the data. You can have as many field names as you like in the database.

Save your database as a plain text file database.txt See the FAQ page for excel questions.

IMPORTANT: Please do not use field names in your database used in the script or template. The following are used by the scripts and can not be used as field names name, method, action, page, record, header, random, display, search, order_by, order, mode & headlines.

Upload and CHMOD 755, or 777 if not public.

Back to Top


Create your HTML template 

This will display the results in the way you wish to see them. Using any HTML web style you like. Create a simple template first, and save it as template.htm Everything inside the template brackets is repeated for each record (including any HTML code), everything outside the template brackets is displayed just once (you may wish to display a menu). A sample template is included in the download.

Make sure the fields have the same name as the database fields, insert the fields you wish to display in any format you like with the field names inside double brackets [[fieldname]] note the new style brackets in the template from January 30th 2005

Example code:

With CSVread you can display the total number of records, and the number of matching records.

With CSVread Xtra you can also display the number of pages and the links to the other pages anywhere in the template page.

Insert the code below anywhere you like on the template page, outside the template brackets.



The script will read and display the code from your template. You can not include any SSI (Server side includes) in the template. This is not possible with CGI Perl scripts.

If you have $use_includes set to 1, you will be able to include tags like [[include1]], [[include2]], ... to have the script copy and paste the HTML of the corresponding file into your template.  This system helps you replace SSI commands.

Back to Top


Using a table in your template

You may wish to display your results inside a table. The script can print any HTML code that can be repeated, so this is easy to do.

In the example below we have created a simple table with four columns and a 'header' row. You can change this to fit your database.



You may want to 'alternate' the colour of the rows. This can be done using a simple JavaScript Using the same table example above we have added the JavaScript. You will need to add the 'onload' command to your <body> tag at the top of the template and the 'table ID' to the table. If you are using a 'header' row you can set the x=0 to be x=1 and it will start at row 2. Simply change the two 'backgroundColor' values to the colour you want.



Back to Top


Include links, emails or images in the template

The template will print any HTML code from your database. So you can use the template to display almost anything. This feature will not work with the results highlighting option.

To include a link in your template have a field in the database with the URL in it, in this example I have called the field 'web_link'

Normally HTML code for a link would look like this:
<a href="http://www.EZscripting.com">Web Link</a>

All you need to do is replace the part you have in the database with the code for that field name. So if your database has the URL in a field called 'web_link' then your new could would look like this.
<a href="[[web_link]]">Web Link</a>

Now for each record the script will display the URL from the database in the template.

You can extend this as much as you like, so you could have the name of the site with the link for example
<a href="[[web_link]]">[[name_of_web_link]]</a>

Use the same technique for a link to an email address
<a href="mailto:[[Email_Address]]">[[Email_Address]]</a>

If your feeling really smug you can fill out part of the email from data in the database, so with one click all the informaion is ready to send.
<a href="mailto:[[Email_Address]]?Subject=[[Web_site_name]]
&Body=[[Web_site_name]]">[[Your_Name]]</a>


This is exactly the same for using the script to display an image. The normal code for displaying an image looks like this <img src="image.gif"> now just change the code for the image to the field name from your database <img src="[[image]]"> When adding images remember the code is now being sent from your script, so the location has changed. If you used <img src="image.gif"> you may now need the full path to the image not just the name. <img src="../images/image.gif">

Back to Top


Running the script

You can display CSVread in two ways, by using a link or a virtual include.

To have a virtual include on a webpage you must use Server Side Includes. To do this rename your htm or html page to shtml. Then add <!--#include virtual="/cgi-bin/csvread.pl"--> at the location you wish to see the database displayed.

The examples below show the different methods you can use to filter data with CSVread and CSVread Xtra.

With both CSVread and CSVread Xtra you can use more than one filter at the same time by adding the '&' between the features.

CSVread examples: Display from a link or Virtual Include

/cgi-bin/csvread.pl
Simply run the script to display all the database in any template format.

/cgi-bin/csvread.pl?search=anything
Search all the database for data.
This example is searching for the text 'anything'

/cgi-bin/csvread.pl?search=anything&header=field1
Search just one field in the database to find a match.
This example is searching for the text 'anything' in just 'field1'

/cgi-bin/csvread.pl?field4=gorilla
Search just one field in the database to find a match.
This example is searching 'field4' for the text 'gorilla'

/cgi-bin/csvread.pl?field4!=gorilla
Search just one field in the database to find fields that don't match.
This example is searching 'field4' for text without 'gorilla'

/cgi-bin/csvread.pl?show=90232
If you are using the 'unique ID' feature you can display just one record in the database from a link. In this example 90232 is the value of the unique ID field set-up in the demo.

Advanced features with CSVread Xtra:

/cgi-bin/csvread.pl?search=Type something anything&method=exact
/cgi-bin/csvread.pl?search=Type something anything&method=all
/cgi-bin/csvread.pl?search=Type something anything&method=any
/cgi-bin/csvread.pl?search=Type something anything&method=perfect
/cgi-bin/csvread.pl?field1=Type something anything&method=exact
/cgi-bin/csvread.pl?field1=Type something anything&method=all
/cgi-bin/csvread.pl?field1=Type something anything&method=any
/cgi-bin/csvread.pl?field1=Type something anything&method=perfect

Add a search method to your database or field search
'method=exact' This will find an exact phrase in the database
'method=all' This will find all the words in any order
'method=any' This will find any of the words
'method=perfect' This will match a field in the database exactly

/cgi-bin/csvread.pl?field1=T&method=start
/cgi-bin/csvread.pl?search=P&header=field1&method=start
You can ask the script to display records where a field starts with a chosen letter of the alphabet, you choose to use 1 or more letters. Ideal for a directory.

/cgi-bin/csvread.pl?field3>5
/cgi-bin/csvread.pl?field3>=5
/cgi-bin/csvread.pl?field3<5
/cgi-bin/csvread.pl?field3<=5
/cgi-bin/csvread.pl?field3=5
/cgi-bin/csvread.pl?field3!!5

If your field has just numbers (no special characters) you can filter and sort by that field. You can use '=' '>' '<' '>=' '<=' and '!!'

/cgi-bin/csvread.pl?order_by=field4&order=abc
/cgi-bin/csvread.pl?order_by=field4&order=cba
/cgi-bin/csvread.pl?order_by=field3&order=123
/cgi-bin/csvread.pl?order_by=field3&order=321

Display your results in any order by any field. (note the underscore order_by)
'order=abc' Display results by alphabet from A-Z, by field name.
'order=cba' Display results by alphabet from Z-A, by field name.
'order=123' Display results by number lowest first, by field name.
'order=321' Display results by number highest first, by field name.

/cgi-bin/csvread.pl?display=random
/cgi-bin/csvread.pl?order=random

Display a set number of random records. You can also use 'order=random' This will not work with the page selector feature.

/cgi-bin/csvread.pl?DATE>2001-10-19
/cgi-bin/csvread.pl?DATE>=2001-10-19
/cgi-bin/csvread.pl?DATE<2001-10-19
/cgi-bin/csvread.pl?DATE<=2001-10-19
/cgi-bin/csvread.pl?DATE=2001-10-19
/cgi-bin/csvread.pl?DATE!!2001-10-19
/cgi-bin/csvread.pl?DATE>now
/cgi-bin/csvread.pl?DATE>=now
/cgi-bin/csvread.pl?DATE<now
/cgi-bin/csvread.pl?DATE<=now
/cgi-bin/csvread.pl?DATE=today
/cgi-bin/csvread.pl?DATE>today
/cgi-bin/csvread.pl?DATE>=today
/cgi-bin/csvread.pl?DATE<today
/cgi-bin/csvread.pl?DATE<=today

CSVread Xtra / CSVsearch Xtra can sort records by date and time or just date. By using the feature on CSVwrite Xtra you can automatically write the time to the database when the data is sent. You may want to input the data yourself directly into the database. The data must be in one of the following formats: YYYY.MM.DD or YYYY/MM/DD or YYYY-MM-DD or to use the full date and time: HH:MM:SS YYYY-MM-DD You can use '=' '>' '<' '>=' '<=' '!!' and 'now' for current server time, or 'today' for just the current server day.

/cgi-bin/csvread.pl?order_by=DATE&order=cba
/cgi-bin/csvread.pl?order_by=DATE&order=abc
Display your results in any order by the 'DATE' field.
'DATE&order=abc' Display results by 'DATE' field with newest first
'DATE&order=cba' Display results by 'DATE' field with oldest first

/cgi-bin/csvread.pl?records=5
/cgi-bin/csvread.pl?records=10
/cgi-bin/csvread.pl?records=15
/cgi-bin/csvread.pl?records=20
/cgi-bin/csvread.pl?records=999
Allow users to choose how many results per page. This is done by 'overriding' the default records per page with a hidden or selected value.

/cgi-bin/csvsearch.pl?field3=ez_range(6,9)
Return all records for which field 3 has a value between 6 and 9 (inclusive).

Advanced Feature Extra Database and Template

If you are using the advanced features to use different templates and databases with the same script you will need to add the code into your links.

To use the database you put for 'db1' by using a link you would add:
/cgi-bin/csvread.pl?mydatabase=db1

To use the template you put for 'tp1' by using a link you would add:
/cgi-bin/csvread.pl?mytemplate=tp1

You can as always mix and match different templates and databases as required
/cgi-bin/csvread.pl?mydatabase=db1&mytemplate=tp1

If you are using a form rather than links you need to add some code to your form. This can be a hidden field or as a dropdown menu.

To use the database you put for 'db1' by using a hidden field in a form:
<input type="hidden" name="mydatabase" value="db1">

To use the template you put for 'tp1' by using a hidden field in a form:
<input type="hidden" name="mytemplate" value="tp1">

As part of a drop-down menu your code would look like this:

Back to Top

 
 

home | rate the scripts | community support forum | www.ezscripting.com