CSVSearch - Search a database and display the results

Search all or part of your online database and display the results using your existing look and feel. You can offer as many or as few search options as you want. The advanced version has enhanced filtering and can split the results across 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 CSVRead 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

CSVsearch:

From a search box on a web page, the information is sent to the CSVsearch script. This then displays the results from the database in the format of your template.

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

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

Files you will need:
CSVsearch script (download from this website)
Template to show results (instructions on this page)
Template for 'no results' page (optional)
Database (instructions on this page)
Link or search box (on any standard web page)

Things you have to do:
Follow the instructions below
Download the CSVsearch script and make a few changes
Create a link or search box 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 CSVsearch 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
Search all the database from any web page YES YES
Search one field of the database from any web page YES YES
Customise the look using a template YES YES
Number of search methods 5+ 30+
Split the results over many pages - YES
Set the number of results per page - YES
Advanced page selector - YES
Choose the number of results per page - YES
Show the number of records YES YES
Show the number of matching records YES YES
Show the original search in the results page  - YES
Option to change the delimiter - YES
Set the required number of characters - YES
Option to sort results by field name - YES
Option to sort results ascending or descending - YES
Select the default order results are displayed in - YES
Option to sort results by date - YES
Use image as 'GO' button rather than HTML - 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

Back to Top

Set-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
$CSV_file="database.txt";

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

Message is not records are found
$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' 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";

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 = "csvsearch.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','fieldname2');

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 csvsearch.pl?hl=off or csvsearch.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 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.

Your database should be less than 1000 records and under 1MB run smoothly on most shared web servers. 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 CSVsearch you can display the total number of records, and the number of matching records.

With CSVsearch Xtra you can also display the search request, 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 the top]


Include links, emails or images or HTML code 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">

To include the contents of an HTML file in the template, first define these includes (Advanced Settings Include Pages), then use them by putting [[include1]], [[include2]], ... in the page.

Back to Top


Running the script

You now need a web page with a search box. This form is all you need to perform a simple search of your whole database. Example code.

Search:



If you want to search just one field in the database you can do so by changing the name=search to name=field1 where field one is the field you want to user to search. In this example we use a dropdown box to search just field 4 from a list. You can add more than one box.

Search:



You may wish to search just one field of the database but give the user the choice of the field to search. This is an example using radio buttons to show the fields the user can choose to search.

Search:
Search Just: Field 1 Field 2 Field 3 Field 4



CSVsearch Xtra - Advanced search boxes

With CSVsearch Xtra you can give the visitor 4 different methods of searching, split the results across different pages and sort the results by any field name.

Exact phrase: This will find an exact phrase in the database
All the words: This will find all the words in any order
Any words: This will find any of the words
Perfect field match: This will look to match 100% of a field perfectly

Example of an advanced search box where the user can choose the method to search the database. You can use as many or few of the options as you want to as radio buttons, or just one as a hidden field.

Search:
Method: Exact phrase All the words Any words Perfect field match



Example of an advanced search box where the user can choose the method to search the database, and the field to search. In this example 'field1' is the name of the field in the database.

Search:
Method: Exact phrase All the words Any words Perfect field match  
Search Just:
Field 1 Field 2 Field 3 Field 4



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.

Search:
Method: Exact phrase All the words Any words Perfect field match  
Search Just:
Field 1 Field 2 Field 3 Field 4

You can also sort the results by field name by adding the code below as hidden or form field. Where "fieldname" is the name of field you wish CSVsearch Xtra to sort the results by.

To sort text in ascending order:
<input type="hidden" name="order_by" value="fieldname">
<input type="hidden" name="order" value="abc">

To sort text in descending order:
<input type="hidden" name="order_by" value="fieldname">
<input type="hidden" name="
order" value="cba">

To sort numbers in ascending order:
<input type="hidden" name="order_by" value="fieldname">
<input type="hidden" name="
order" value="123">

To sort numbers in descending order:
<input type="hidden" name="order_by" value="fieldname">
<input type="hidden" name="
order" value="321">

With CSVsearch Xtra you can change the search button for an image.
Change line: <input type="submit" value="[CSVsearch Xtra]">
For this:
<input type="image" src="search.gif">

You are not restricted to just one search box. You can add dropdown menus and checkboxes. You can mix and match almost all of the options as hidden field or radio buttons to suit your search requirements.

Advanced Feature 'ez_range' numeric range

'ez_range' makes it easy to let your visitors choose field value ranges from a drop-down box or a radio button form.  In URL form, this function would look like
   /cgi-bin/csvsearch.pl?field3=ez_range(6,9)

Where 6 is the minimum value and 9 is the maximum value field3 can have in order to match.

Such form could look like this :

field 3:
Between 0 and 5
Between 5 and 9
10 or above

and would look like this in the HTML code :

[FrontPage Save Results Component]

Similar things can be done using a drop-down menu, ...

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/csvsearch.pl?mydatabase=db1

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

You can as always mix and match different templates and databases as required
/cgi-bin/csvsearch.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