List:General Discussion« Previous MessageNext Message »
From:J Irwin Date:April 2 1999 1:37pm
Subject:MySQL/DBI problem
View as plain text  
The server I'm on is using Apache 1.3.3 for FreeBSD 2.2.7, MySQL 3.22, perl 5.002 and
DBI-0.93. The database has 5 tables each with a primary key column, "id" that is
identical for all 5 tables. When I telnet in and use the command line, the query works
fine and returns the expected results. When I connect with DBI, only the beginning of the
HTML page is printed. No data, no error, nothing else is printed, not even the bottom of
the HTML page. I would appreciate any suggestions. I have successfully queried 1 table at
a time using DBI, but the bind_columns still doesn't work right.
TIA,
J Irwin
 
query-using only 3 of the tables
 
command-line:
SELECT categories.id, categories.ILTCIBA,
demographics.company, demographics.city,
demographics.state, products.upgrades
FROM categories, demographics, products
WHERE(categories.ILTCIBA=1
AND demographics.state="NY"
AND categories.id=products.id
AND demographics.id=products.id)
ORDER BY demographics.city, demographics.company;
 
perl using DBI:
#!/usr/local/bin/perl

use DBI;
use CGI qw(param);
print "Content-type: text/html\n\n";

my $CATEGORY = param("category");
my $STATE = param("state");
 
(print beginning of HTML page)
 
use strict;
 my $sql_query = "SELECT ";
 $sql_query .= "categories.id, categories.$CATEGORY ";
 $sql_query .= "demographics.company, demographics.city, ";
 $sql_query .= "demographics.state, products.upgrade ";
 $sql_query .= "FROM  categories, demographics, products ";
 $sql_query .= "WHERE categories.$CATEGORY=1 ";
 $sql_query .= "AND demographics.state=$STATE ";
 $sql_query .= "AND categories.id=products.id ";
 $sql_query .= "AND demographics.id=products.id ";
 $sql_query .= "ORDER BY demographics.city, demographics.company";
 
 my $dbh = DBI->connect('DBI:mysql:mydatabase:myserver',
    'myusername',
    'myuserpassword') ||
     die "Database connection not made: $DBI::errstr";
 
 my $sth = $dbh->prepare($sql_query) || 
   die "Unable to prepare $sql_query: $DBI::errstr\n";
 
 $sth->execute();
 
 my ($id, $category, $company, $city, $state, upgrade);
 
 $sth->bind_columns(undef, \$id, \$category, \$company, \$city, \$state, \$upgrade);
 
 while ($sth->fetchrow_arrayref)
 {
  $search_results .= "$company<BR>\n"
  $search_results .= "$city<BR>\n"
  $search_results .= "$state<BR>\n"
  $search_results .= "$upgrade<BR>\n"
 }

 $sth->finish();
 $dbh->disconnect();

 print "$search_results";
 
(print remainder of HTML page)
 
exit;

Thread
MySQL/DBI problemJ Irwin2 Apr
Re: MySQL/DBI problemGraeme B. Davis2 Apr
Re: MySQL/DBI problemFred Lindberg2 Apr
Re: MySQL/DBI problemGraeme B. Davis2 Apr
Re: MySQL/DBI problemFred Lindberg2 Apr
Re: MySQL/DBI problemGraeme B. Davis2 Apr