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: