The following script, with some edits, has allowed access to a "LOCAL" mysql
database. I have reworked it some to access a MySQL on another server I
suppose I could transfer the script to that server for loading/execution, but
I'd like to learn how one accesses a remote mySQL database (to facilitate
building a shopping cart system.
Anyhow, when it executes, I can select the field I want to search on from a
popup_menu, select "is" or "contains" and then enter the text I wat to search
for. That part works. What doesn't work is that I receive simply a blank
page for a display (all this beautiful color of 738C8C).
Have I got the syntax for access to a remote database correct? TIA for any
help...
-----------------
#!/usr/bin/perl -w
# CGI to select data from a SQL database
use CGI;
use strict;
use DBI;
my $q = new CGI;
#Definitions -unique to each table, user, etc.
my $db = "the_plant_database";
my $table = "da_table";
my $host = "201.201.201.21";
my $user = "mysql";
my $password = "mypassword";
# This is the main branch. The first time through
# display the form to the user; when the user submits
# the form then we process the input
if ($q->param('field') eq "") {&printform()} else {&results()}
sub printform {
print "Content-Type: text/html\n\n";
# print $q->header;
print $q->start_html(-title=>'MySQL Plant Database at Plants.com',
-BGCOLOR=>'#738C8C',
-TEXT=>'white');
print "<CENTER><H1>MySQL Plant Search - Da_List of Plants</H1></CENTER><BR>";
print $q->startform;
print "<H2>Plants that match selection</H2><BR>";
print $q->popup_menu(-name => 'field',
-values => ["ML_ID", "Cultivar",
"Reg_Status","Section","Class","ML_ITG_ID","Registrant"],
-default=> "Cultivar");
print $q->popup_menu(-name => 'searchtype',
-values => ["is", "contains"],
-default => "is");
print $q->textfield(-name =>"text",
-size => 16), "<BR>";
print $q-> submit;
print $q->endform;
print $q->end_html;
}
sub results {
print $q->header();
my $field = $q->param('field');
my $searchtype = $q->param('searchtype');
my $text = $q->param('text');
my @table=();
print $q->start_html(-title=>'Database Results',
-BGCOLOR=>'#738C8C',
-TEXT=>'white');
# Establish a connection with the database
my $dbh = $drh->connect($host, $db, $table, $user, $password);
# A simple check to see if we connected
if (!$dbh) {
print "Cannot connect: $DBI::errstr<BR>";
print $q->end_html;
die;
}
# Build and execute the SQL statement
my ($SQLstatement);
if ($searchtype eq "contains") {
$SQLstatement = "select ML_ID, Cultivar, Reg_Status, Section, Class,
ML_ITG_ID, Registrant from $table
where $field like \"%$text%\"";
} else {
$SQLstatement = "select ML_ID, Cultivar, Reg_Status, Section, Class,
ML_ITG_ID, Registrant from $table
where $field = \"$text\"";
}
my $sth = $dbh->prepare($SQLstatement);
my $howmany = $sth->execute;
# Display an error message if we can't find any matches
if ($howmany eq "OEO") {
print $q-h3("<font color='red'> Couldn't match $text in database</font>");
die;
}
print "$howmany matches found in database<br><br>";
push (@table,$q->th(["ML_ID", "Cultivar", "Reg_Status",
"Section","Class","ML_ITG_ID","Registrant"]));
# Loop through all the matches and store them in @table
for (my $i = 0; $i < $howmany; $i++) {
my ($SQLML_ID, $SQLCultivar, $SQLReg_Status, $SQLSection, $SQLClass,
$SQLML_ITG_ID, $SQLRegistrant) = $sth->fetchrow_array;
push (@table,$q->td([$SQLML_ID, $SQLCultivar, $SQLReg_Status, $SQLSection,
$SQLClass, $SQLML_ITG_ID, $SQLRegistrant]));
}
# Print the table of data we received from the mySQL database.
print $q->table({-border => 1, -align => "center"},$q->TR(\@table));
print $q->end_html;
}
--
Andrew Lietzow
The ACL Group, Inc.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
