CSVUpdate Visitors can update a record in your database

Allow visitors to your website update their information themselves. Provide an ID and Password for each record then the visitor can update one record in the database. The script is very simple to set-up and customize using the look and feel of your own website. (CGI/Perl Unix)

Overview of the script
Set-up the script
Create your database
Create your HTML template
Running the script

Overview of the script

CSVupdate:
Using a link or typing the URL in your browser, the visitor will be asked for a user ID and Password, then given access to edit one record in the database.

CSVupdate Xtra:
Using a link or typing the URL in your browser, the visitor will be asked for a user ID and Password, then given access to edit one record in the database. You can select field that are hidden or not available to edit.

Current versions:
Basic 28/Oct/2006
XTra 03/Oct/2006

Files you will need:
CSVupdate script (download from this website)
Database (instructions on this page)
Template (download from this website)

Things you have to do:
Follow the instructions below
Download the CSVupdate script and make a few changes
Create a database (or let CSVwrite do it!)
Customise your template
Upload the script

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
Sendmail or access to SMTP (Xtra Version)

Features CSVupdate 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
Edit records in the database YES YES
Delete records in the database NO NO
ID field locked and can not be edited YES YES
Manage line breaks into HTML or spaces YES YES
ID & Password protection YES YES
Option to change the delimiter - YES
Option to change the character replacing delimiter - YES
Option to change the textbox for scrolling textbox - YES
Choose fields that you do not wish users to edit - YES
Choose fields that you do not wish users to see - YES
Option to allow or block HTML code  - YES
Customise the look using a template YES YES
Use the same template as CSVread & CSVsearch YES YES
Link directly from CSVread or CSVsearch YES YES
Required fields option for numbers only - YES
Required fields option for Email address only - YES
Required fields option for general text - YES
Send the data by email using sendmail or SMTP - YES
Send field name and data or just data by email - YES
Option not to use the password protection YES YES
File upload (not possible on all web servers) - YES
Allow the record to be deleted - YES
Filter to block 'bad' words - YES
Thank You page can show the values of the form - YES

Back to Top



Set-up the script

CSVupdate

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

Name of this script, or local URL (Not Path).
my $prog_name = "csvupdate.pl";

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

Change the URL of your thank you file (This MUST be in a public directory). If it is in the same directory as the script this should be okay
my $HTML_thankyou = 'thankyou.shtml';

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

If you are using scrolling text boxes the script will need to remove any line breaks. This is then replaced with the HTML code for a line break, or you may wish to use a space.
my $line_break = '<br>';

Name the field that has the unique ID. Note, this must be unique in each record or the script will edit the first record only. It is not possible to have more than one record with the same unique ID.
my $ID_field = "ID";

Name the field that has the password for the record.
my $Password_field = "ID"; (leave blank if not using this feature)

ID and Password errors
my $no_match = "Sorry, your record not found!";
my $no_login = "Sorry, your password is incorrect!";
my $no_id = "Sorry, your request without id(record unique key)!";


Upload and CHMOD 755, or 777 if not public.

Back to Top

CSVupdate Xtra

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

Name of this script, or local URL (Not Path).
my $prog_name = "csvupdate.pl";

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

Change the URL of your thank you file (This MUST be in a public directory). If it is in the same directory as the script this should be okay
my $HTML_thankyou = 'thankyou.htm';

Change the URL of the thank you file you want the script to show when a user deletes a record rather than updating it.  If you have one generic thank you page, then just set it to the same value as the previous option.
my $HTML_thankyou2 = 'thankyou2.htm';

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

If you wish to use a different delimiter in the database to the pipe you can change it.
my $separator = '|'; 

If you wish to change the character the script used when a user inputs data with the delimiter.
my $change = "I";

If you are using scrolling text boxes the script will need to remove any line breaks. This is then replaced with the HTML code for a line break, or you may wish to use a space.
my $line_break = '<br>';

To stop users posting HTML set this to 1, or 0 to allow HTML
my $kill_html_tags = 1;

Name the field that has the unique ID. Note, this must be unique in each record or the script will edit the first record only. It is not possible to have more than one record with the same unique ID.
my $ID_field = "ID";

Name the field that has the password for the record.
my $Password_field = "ID"; (leave blank if not using this feature)

You can select fields that you do not wish the script to display in the edit window, and fields that you wish to show, but not edit (The ID and password fields are done automatically and don't need to be listed).
Input the field names as below, or leave blank. ();
my @no_show_fields = ("textbox","radiobutton");
my @no_edit_fields = ("textbox","radiobutton");

If you set $allow_delete to 1, the update form will also have a checkbox allowing the user to delete the record rather than change it.
my $allow_delete = 1;
my $delete_command = 'Simply delete this record';
my $delete_message = 'The following record was removed from the database:';

Advanced Settings Thank You Page

If you use a static html 'Thank You' page, you can also let it show the values of the fields from your form.  If set to 1, you can include tags like [[fieldname]] in your Thank You page, which the script will change into the value of field 'fieldname', allowing you to customize your page to the specific visitor.
$display_data_on_thankyou = 0;

Note that if you switch on this feature, $HTML_thankyou and $HTML_thankyou2 (see earlier) have to be the PATH to your 'Thank You' html page.

Advanced Settings ID and Password errors
my $no_match = "Sorry, your record not found!";
my $no_login = "Sorry, your password is incorrect!";
my $no_id = "Sorry, your request without id(record unique key)!";


Advanced Settings table & big textbox description
You can select fields to have a large scrolling text box rather than a standard text box. Input the field names as below, or leave blank. ();
my @textareas = ("textbox","radiobutton");

The width of the table used to display the information.
my $table_width = 490;

The size of the scrolling text box for editing
my $rows = 4;
my $cols = 40;


Size of the text box for editing
my $text_box = 50;

Advanced Settings required fields
Required fields must have no spaces or special characters in the field name

ALL fields required with any data. Leave as () if none are required.
(Ensure they are the same name as your form and database)
my @required_fields = (fieldname,fieldname,fieldname,);

The fields required with numbers. Leave as () if none are required.
(Ensure they are the same name as your form and database)
my @required_fields_numbers = (fieldname,fieldname);

The fields required with an email address. Leave as () if none are required. (Ensure they are the same name as your form and database)
my @required_fields_email = (fieldname,fieldname);

Advanced Settings required fields errors
You can customise the error message and format a customer will see if they do not input your required fields. You can add HTML but do not any quote marks in the code.

my $error_fields_require = "Field is blank, it is required";
my $error_fields_numbers = "Only numbers";
my $error_fields_email = "email address is not valid";
my $error_title = "<b>Sorry, we require more information</b>";
my $fontColor = "black";
my $fontSize = 3;
my $fontFace = "Verdana";
my $return_message = "Please click 'back' on your browser";


Advanced Settings Email
You get get a copy of the data by email once updated by a user (excluding files).

Set this to '1' to send a copy of the data or to '0' not to
my $send_me = 0;

The email address to send a copy of the data to
my $admin_mail = 'email@your-url.co.uk';

Change this to the default email address you want the email to be from.
my $from = 'email@your-url.co.uk';

The error seen if potentially damaging code is attempted to be sent
my $email_input_error = "Non resolved email input";

Set this to 1 if you want just data by email and not the field names.
my $send_just_data = 0;

This is the PATH to your send mail (check this with your administrator)
my $mailprog = '/usr/sbin/sendmail';

The subject line in the email message
my $subject = 'User has updated the database';

If you want the mail only to contain those fields for which there is a value, then set this to 1
$only_fields_with_values = 0;

Advanced Settings SMTP rather than Sendmail
To use SMTP rather than sendmail remove the hash and space
# use Net::SMTP;

If you wish to use SMTP rather than SENDMAIL set this to '1'
my $send_via_SMTP = 0;

Your SMTP server details (check this with your administrator)
my $mailhost = 'smtp.com';

Advanced Settings file upload
Most web hosting will block this feature for security reasons. Check with your host that you can use file upload from a form before using this feature.

Switch the file upload option off 0 /on 1
my $file_upload = 0;

The fields in your database that will have files to upload
my @file_upload_fields = ("fieldname","fieldname");

If you want the script to re-name the file. Strongly recommended security feature.
my $rename_file = 1;

The maximum size of file allowed.
my $error_max_size = 100000;

Types of files allowed. Do not allow .exe files on your server, don't leave this blank.
my @required_file_types = ("txt", "jpg", "jpeg", "tif", "gif");

Errors for wrong file type and files too large
my $error_max_size = "Your file is too big!";
my $error_file_type = "File type is not valid!";


The PATH not URL to the folder to take the files. Your host may block all or some folders from taking files. Note no final trailing slash / in the PATH.
my $file_dir = "/home/path/to/your/file/folder";

The URL not PATH to the folder to take the files. Your host may block all or some folders from taking files. Note no final trailing slash / in the URL.
my $fileURL = "http://www.your-url.co.uk/files";

Advanced Settings - Bad Words Filter

If you want to prevent visitors from using certain words, you should enable this option by setting it to 1:
my $use_filter = 0;

Then specify the PATH to a file in which you typed the 'forbidden' words (one at each line):
my $badwordsfile = 'badwords.txt';

If a bad word is found, it will be replaced by:
my $censored = '***';

Advanced Settings Include Pages

If you have $display_data_on_thankyou set to 1, then the Thank You page is being handled by the script and not the server.  Any SSI includes on the page will no longer work.  In order to help mitigating this, you can use script based HTML includes.  Mind you that this feature is not needed for the Thank You page when $display_data_on_thankyou is set to 0 as you can use normal SSI includes then.

You can also use these includes for the normal script template (see 'Create your HTML template' below) regardless of the value of $display_data_on_thankyou.

Once you set $use_includes to 1, the script will replace each 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. The script will display the HTML code from your template and display the database in a table for editing inside your <template> brackets </template>

So, you can use the same template for CSVread CSVsearch and CSVedit without making any changes. However, should you wish to use a separate template then one is included in the download with the script and the code is below.

The table and font is controlled by a <style sheet> at the top of the HTML code. See the code below and the sample in the download.

Example code:


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

 
 

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