.com Solutions Inc. - logo  
Products - Header Buttons
.
Repgen bullet item 7b Installgen bullet item 7b FmPro Migratorbullet item 7b CGIScripter
.
.
.

.
.

CGIScripter - MySQL Files - Title Graphic

CGIScripter Features and Benefits

CGIScripter Demo Available for download...

Bookmark This Page

email a friend

Flowchart

HTML Query List Example - For Delete

.

#!/usr/bin/perl
# Script: example_delete1
# Features: This Perl program processes web queries
# which are displayed as a list of
# query results retreived from the
# database.
#
# Requirements:
# Perl CGI module
# Perl DBI module
# Perl DBD::mysql module
# MySQL client software must be installed on the
# webserver running this program.
#
# Database Connection:
# MySQL Connection: database=mysql1:host=mysqlhost1:port=3306
#
# External Files:
# HTML Query Form: example_delete1.html
# Display Record Script:example_delete_record_display1
#
#
#
# Usage: Query HTML Form Submits To: http://www.dotcomsolutionsinc.net/cgi-bin/example_delete1
# Query List Links To: http://www.dotcomsolutionsinc.net/cgi-bin/example_delete_record_display1
#
# Copyright 2003 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 7-5-2003 dsimpson Initial Release
#
# This output file was created by CGIScripter version 1.48 on Sat Jul 12 10:14:55 2003. By .com Solutions Inc. www.dotcomsolutionsinc.net
#
use strict;
use DBI qw(:sql_types);
use CGI qw(:standard escape);

$CGI::POST_MAX=10000; # max post size - to avoid denial of service attacks
$CGI::DISABLE_UPLOADS = 1; # 1 = no uploads of files allowed via CGI.pm - for security
my $display_record_url = 'http://www.dotcomsolutionsinc.net/cgi-bin/example_delete_record_display1'; # URL to script which displays individual records from database
my $db_connect_string = 'database=mysql1:host=mysqlhost1:port=3306';
my $database_tablename = 'asset_management2';
my $schema_name = 'mysqluser1';
my $schema_password = 'mysqluser1pwd';
my $debug = 0; # DBI tracing enable/disable
my $long_readlength = 10000; # maximum number of bytes for character and binary large object types data inserted into database - increase this value as needed
my $results_per_page = 30; # The number of rows of result info to display per page
my $number_of_results = 0; # The actual number of rows retreived from the database
my $endpage_record = $results_per_page; # The last record displayed on the current results page
my $global_database_error = 0;
my $primary_key_column = 'item';
my $url_encoded_primary_key_data = ''; # Temporary storage for url encoded primary key field data
my @list_of_query_fields = (); # Dynamically built list of query fields
my $query_fields_text = ''; # List of query fields separated by commas
my $number_of_query_fields = 0; # The number of query fields which were filled in.
my $sql_where_statement = ''; # The "where" statement - used only if number of query fields > 0
my $sql_where_clause_text = ''; # SQL where clause built up dynamically from fields which where filled in.
my $dbi_bind_param_text = ''; # list of DBI bind parameter statements
my $bind_param_count = 1; # Bind parameter counter starts at 1
my $ref_rowdata = (); # Reference to dbi fetchrow_arrayref of query results - (this improves performance)
my $generic_error_text = 'Error encountered:';
my $no_records_found_error_text = 'No records found.';
my $next_link_text = 'Next';
my $previous_link_text = 'Previous';
# ---------- Alternate HTML Header/Footer -----------
# This HTML page Header/Footer info is used by default
my $html_header =<<"EOF"; # default HTML header
<html>
<head>
<title>example delete</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000" >
EOF
my $html_footer =<<"EOF"; # default HTML footer
</body>
</html>
EOF
my $use_external_html = 0; # 1 = read HTML from external files, 0 = use default HTML
my $external_html_header_filename = 'example_header1.html';
my $external_html_footer_filename = 'example_footer1.html';
if ($use_external_html == 1)
{
{
# set input line separator to undef to read whole file at once
local $/ = undef;

# read HTML header from external file
open (FILE1,"$external_html_header_filename") || warn ("Could not open input file $external_html_header_filename for reading. Using default header HTML instead of external file.");
$html_header = <FILE1>;
# close the input file
close (FILE1);

# read HTML footer from external file
open (FILE1,"$external_html_footer_filename") || warn ("Could not open input file $external_html_footer_filename for reading. Using default footer HTML instead of external file.");
$html_footer = <FILE1>;
# close the input file
close (FILE1);
}
}

# ---------- Displayed Field Names -----------
my $item_display_as = 'Item to Enter';
my $category_display_as = 'Category of Item';
my $model_display_as = 'Model';
my $serial_number_display_as = 'Serial Number';
my $information_display_as = 'Information';
my $date_purchased_display_as = 'Date Purchased';
my $depreciation_display_as = 'depreciation';
my $assigned_display_display_as = 'assigned_display';
my $depreciation_life_display_as = 'depreciation_life';
my $remaining_life_display_as = 'remaining_life';
my $cost_display_as = 'cost';
my $book_value_display_as = 'book_value';

# ---------- Get CGI Form Data -----------
# get parameters passed in via POST by using CGI.pm
my $cgi_item = param("item");
my $cgi_category = param("category");
my $cgi_model = param("model");
my $cgi_serial_number = param("serial_number");
my $cgi_information = param("information");
my $cgi_date_purchased = param("date_purchased");
my $cgi_depreciation = param("depreciation");
my $cgi_assigned_display = param("assigned_display");
my $cgi_depreciation_life = param("depreciation_life");
my $cgi_remaining_life = param("remaining_life");
my $cgi_cost = param("cost");
my $cgi_book_value = param("book_value");

# get current results page number
my $crecord = param ("crecord");
$crecord = 1 if !defined ($crecord) || $crecord !~ /^\d+$/ || $crecord < 1;
$endpage_record = ($crecord + $results_per_page) - 1;
# ---------- Define Bind Variable Types -----------
my $cgi_bind_vartype_item = '';
my $cgi_bind_vartype_category = '';
my $cgi_bind_vartype_model = '';
my $cgi_bind_vartype_serial_number = '';
my $cgi_bind_vartype_information = '';
my $cgi_bind_vartype_date_purchased = ',SQL_DATE';
my $cgi_bind_vartype_depreciation = '';
my $cgi_bind_vartype_assigned_display = '';
my $cgi_bind_vartype_depreciation_life = '';
my $cgi_bind_vartype_remaining_life = '';
my $cgi_bind_vartype_cost = '';
my $cgi_bind_vartype_book_value = '';

# find fields containing data
if (length($cgi_item) > 0)
{
push (@list_of_query_fields,"item");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_item$cgi_bind_vartype_item);\n";
$bind_param_count++;
}
if (length($cgi_category) > 0)
{
push (@list_of_query_fields,"category");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_category$cgi_bind_vartype_category);\n";
$bind_param_count++;
}
if (length($cgi_model) > 0)
{
push (@list_of_query_fields,"model");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_model$cgi_bind_vartype_model);\n";
$bind_param_count++;
}
if (length($cgi_serial_number) > 0)
{
push (@list_of_query_fields,"serial_number");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_serial_number$cgi_bind_vartype_serial_number);\n";
$bind_param_count++;
}
if (length($cgi_information) > 0)
{
push (@list_of_query_fields,"information");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_information$cgi_bind_vartype_information);\n";
$bind_param_count++;
}
if (length($cgi_date_purchased) > 0)
{
push (@list_of_query_fields,"date_purchased");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_date_purchased$cgi_bind_vartype_date_purchased);\n";
$bind_param_count++;
}
if (length($cgi_depreciation) > 0)
{
push (@list_of_query_fields,"depreciation");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_depreciation$cgi_bind_vartype_depreciation);\n";
$bind_param_count++;
}
if (length($cgi_assigned_display) > 0)
{
push (@list_of_query_fields,"assigned_display");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_assigned_display$cgi_bind_vartype_assigned_display);\n";
$bind_param_count++;
}
if (length($cgi_depreciation_life) > 0)
{
push (@list_of_query_fields,"depreciation_life");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_depreciation_life$cgi_bind_vartype_depreciation_life);\n";
$bind_param_count++;
}
if (length($cgi_remaining_life) > 0)
{
push (@list_of_query_fields,"remaining_life");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_remaining_life$cgi_bind_vartype_remaining_life);\n";
$bind_param_count++;
}
if (length($cgi_cost) > 0)
{
push (@list_of_query_fields,"cost");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_cost$cgi_bind_vartype_cost);\n";
$bind_param_count++;
}
if (length($cgi_book_value) > 0)
{
push (@list_of_query_fields,"book_value");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_book_value$cgi_bind_vartype_book_value);\n";
$bind_param_count++;
}

$number_of_query_fields = @list_of_query_fields;
if ($number_of_query_fields > 0)
{
$sql_where_statement = ' where '; # The SQL "where" clause is only used if at least one query field was entered
# otherwise, all records are retrieved from the database table.
for (my $query_loop=0;$query_loop < $number_of_query_fields; $query_loop++)
{
# build list of query fields
$query_fields_text .= $list_of_query_fields[$query_loop] . ', ';
$sql_where_clause_text .= ' and ' if ($query_loop > 0); # prefix AND to where clause after 1st iteration
$sql_where_clause_text .= $list_of_query_fields[$query_loop] . ' = ?';
}

# remove trailing space and comma
chop($query_fields_text);
chop($query_fields_text);

}

# ---------- Retrieve Data From Database -----------
print header();
print $html_header;

if ($debug == 1)
{
# turn on DBI tracing
unlink 'dbitrace.log' if -e 'dbitrace.log';
DBI->trace(2, 'dbitrace.log');
}

my $db_dbh = DBI->connect ("dbi:mysql:$db_connect_string", "$schema_name", "$schema_password", {RaiseError => 0, PrintError => 1, AutoCommit => 1 })
or die "Can't connect to the MySQL $db_connect_string database: $DBI::errstr\n";
$db_dbh->{LongReadLen} = $long_readlength;
$db_dbh->{LongTruncOk} = 0;

$db_dbh->do("SET OPTION SQL_BIG_TABLES = 1");
my $db_sth = '';

#$db_sth = $db_dbh->prepare("select item, category, model, serial_number, information, date_purchased, depreciation, assigned_display, depreciation_life, remaining_life, cost, book_value from $database_tablename where item = ? and category = ? and model = ? and serial_number = ? and information = ? and date_purchased = ? and depreciation = ? and assigned_display = ? and depreciation_life = ? and remaining_life = ? and cost = ? and book_value = ? ") or warn " Database error:",$db_dbh->errstr(), "\n";
$db_sth = $db_dbh->prepare("select item, category, model, serial_number, information, date_purchased, depreciation, assigned_display, depreciation_life, remaining_life, cost, book_value from $database_tablename $sql_where_statement $sql_where_clause_text ") or warn " Database error:",$db_dbh->errstr(), "\n";
$global_database_error = 1 if ($db_sth->err());

eval($dbi_bind_param_text);

$db_sth->execute() or warn $db_sth->errstr(); # check for error
$global_database_error = 1 if ($db_sth->err());

# find out how many rows were retrieved from the database
$number_of_results = $DBI::rows;

# ---------- Display Query Data -----------
if (($number_of_results > 0) and ($global_database_error == 0))
{
my $display_record_url_full = $display_record_url . '?' . $primary_key_column . '=';

my $plural_error_text = 's';
$plural_error_text = '' if $number_of_results == 1;
my $results_count = 1;
$endpage_record = $number_of_results if $endpage_record > $number_of_results;
print "Result$plural_error_text $crecord - $endpage_record of $number_of_results";
print '<table border="1">'; # start table HTML command
print "<tr><th>$item_display_as </th><th>$category_display_as </th><th>$model_display_as </th><th>$serial_number_display_as </th><th>$information_display_as </th><th>$date_purchased_display_as </th><th>$depreciation_display_as </th><th>$assigned_display_display_as </th><th>$depreciation_life_display_as </th><th>$remaining_life_display_as </th><th>$cost_display_as </th><th>$book_value_display_as </th> </tr>";
while ($ref_rowdata = $db_sth->fetchrow_arrayref())
{
if ( ($results_count >= $crecord) and ($results_count <= $endpage_record))
{
# HTML escape all output data for correct display in browser
$ref_rowdata->[0] = CGI::escapeHTML($ref_rowdata->[0]);
$ref_rowdata->[1] = CGI::escapeHTML($ref_rowdata->[1]);
$ref_rowdata->[2] = CGI::escapeHTML($ref_rowdata->[2]);
$ref_rowdata->[3] = CGI::escapeHTML($ref_rowdata->[3]);
$ref_rowdata->[4] = CGI::escapeHTML($ref_rowdata->[4]);
$ref_rowdata->[5] = CGI::escapeHTML($ref_rowdata->[5]);
$ref_rowdata->[6] = CGI::escapeHTML($ref_rowdata->[6]);
$ref_rowdata->[7] = CGI::escapeHTML($ref_rowdata->[7]);
$ref_rowdata->[8] = CGI::escapeHTML($ref_rowdata->[8]);
$ref_rowdata->[9] = CGI::escapeHTML($ref_rowdata->[9]);
$ref_rowdata->[10] = CGI::escapeHTML($ref_rowdata->[10]);
$ref_rowdata->[11] = CGI::escapeHTML($ref_rowdata->[11]);

$url_encoded_primary_key_data = $ref_rowdata->[0];
print '<tr>';
print "<td><A HREF=\"$display_record_url_full$url_encoded_primary_key_data\">&nbsp;$ref_rowdata->[0]</A></td><td>&nbsp;$ref_rowdata->[1]</td><td>&nbsp;$ref_rowdata->[2]</td><td>&nbsp;$ref_rowdata->[3]</td><td>&nbsp;$ref_rowdata->[4]</td><td>&nbsp;$ref_rowdata->[5]</td><td>&nbsp;$ref_rowdata->[6]</td><td>&nbsp;$ref_rowdata->[7]</td><td>&nbsp;$ref_rowdata->[8]</td><td>&nbsp;$ref_rowdata->[9]</td><td>&nbsp;$ref_rowdata->[10]</td><td>&nbsp;$ref_rowdata->[11]</td>";
print '</tr>';
$results_count++;
}
else
{
# skip past data which should not be displayed - but increment counter
$results_count++;
}
}

print '</table>'; # end table HTML command
Delete('crecord'); # delete any existing parameter info for crecord in order to construct URL
my $previous_crecord = $crecord - $results_per_page;
$previous_crecord = 1 if $previous_crecord < 0;
param(-name=>'crecord',-value=>"$previous_crecord"); # create and populate a new crecord with info for Previous link
my $prev_url = self_url();

Delete('crecord'); # delete any existing parameter info for crecord in order to construct URL
my $next_crecord = $crecord + $results_per_page;
$next_crecord = $number_of_results if $next_crecord > $number_of_results;
param(-name=>'crecord',-value=>"$next_crecord"); # create and populate a new crecord with info for Next link
my $next_url = self_url();

# determine if Previous and Next links are required
if ($crecord > 1)
{
# Previous link is needed
print "<A HREF=\"$prev_url\">$previous_link_text&nbsp;&nbsp;</A>";
}
else
{
# Previous link is not needed - just print the text
print "$previous_link_text &nbsp;&nbsp;";
}
if ($number_of_results > $endpage_record)
{
# Next link is needed
print "<A HREF=\"$next_url\">$next_link_text&nbsp;&nbsp;</A>";
}
else
{
# Previous link is not needed - just print the text
print "$next_link_text";
}
print $html_footer;
}
else
{
if ($global_database_error == 0)
{
print "$no_records_found_error_text<BR>";
print $html_footer;
}
else
{
print "<BR>$generic_error_text<BR>$DBI::errstr";
print $html_footer;
}
}

$db_sth->finish();
# disconnect from database
$db_dbh->disconnect or warn "Can't disconnect from the database $db_connect_string database: $DBI::errstr\n";

.

hline

. .

.

. .
 

Home | Products | Services | Downloads | Order | Support | Contact

Legal Notices

.
.   .
.
Home Products Services Downloads Order Support Contact