unable to make SELECT take variables

From: John S Brigham (mrphysh_at_juno.com)
Date: 11/03/03 

To: dbi-users@perl.org
Date: Mon, 3 Nov 2003 07:34:26 -0800

I need help.

I am working on a MySQL /Perl web site. I am teaching myself MySQL and
PERL and am making progress.

As an exercise, I want to put my Mother's Christmas card list in a MySQL
database. I want to sort and print the mailing labels from the database.
 This is all done in PERL.

The machinery is all in place. I have built and am now filling the
database with names and addresses.

I want to be able to query the database for specific records through an
interactive Perl script. With some effort, I have gotten the SELECT
statement to work but is will not take a string variable.

Short question:

$cty = <STDIN>;

chomp $cty;

#then
.......SELECT column from table where City = $cty #doesn't work.

No matter how I do it, the script will not take the variable. There is
no error. variable causes an error

.......................
These work:
.................................................
my $sth = $dbh->prepare(q {
  SELECT Full_Name ,Street_Address
  FROM momaddr
  WHERE City = "Denver"# The table,column names are not case specific
}) or die "can't prepare statement"; #the double quotes around column
specifics are required
    $sth->execute()
     or die "can't execute";
print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n";
print "Field names: @{ $sth->{NAME}}\n\n\n";

while (($Full_Name,$Street_Address) = $sth->fetchrow_array) {
      print "$Full_Name$Street_Address\n\n";
 }
  # check for problems which may have terminated the fetch early
  die $sth->errstr if $sth->err;
............................................................
these do not work: (No error but the statement will not take the variable
$cty)
......................................................

my $cty = "Denver";

my $sth = $dbh->prepare(q {
  SELECT Full_Name ,Street_Address
  FROM momaddr
  WHERE City = "$cty"# The table,column names are not case specific
}) or die "can't prepare statement"; #the double quotes around column
specifics are required
    $sth->execute()
     or die "can't execute";
print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n";
print "Field names: @{ $sth->{NAME}}\n\n\n";

while (($Full_Name,$Street_Address) = $sth->fetchrow_array) {
      print "$Full_Name$Street_Address\n\n";
 }
System:
>
> Windows 2000
> pentium II
> PERL Active Perl version 1.37 5.8.0.806
> MySQL version 2000 version 5.00.2195
>
> Thanks in advance
> John in Denver, Colorado USA
>
> _____

________________________________________________________________
The best thing to hit the internet in years - Juno SpeedBand!
Surf the web up to FIVE TIMES FASTER!
Only $14.95/ month - visit www.juno.com to sign up today!

Relevant Pages

  • Re: How do I Exchange data with web server
    ... any reason why you don't just open the tables directly on the MySql ... Can you not have a connection setup to the database? ... how does the web site know to read those text files? ... many cases, to use mysql *OVER* the web, you have to setup a ssl connection. ...
    (microsoft.public.access.forms)
  • script help to update member profiles
    ... form is all stored in a MySql Database (First Name, Last Name, Username, ... my partner that did all the perl scripts was injured ... Compare entered username and password to database to confirm the user is ...
    (comp.lang.perl.misc)
  • [More info]: mysql/perl/gcc/DBI/DBD -- Sunfreeware/CPAN -- Solaris 8
    ... I'm going to rebuild all the perl stuff from scratch. ... think I have mysql running alright from the SunFreeware build. ... privileges in the mysql database setup. ... bin/mysqlshow: Access denied for user: 'root@localhost' ...
    (SunManagers)
  • Re: How do I Exchange data with web server
    ... The reason that I am doing it this way is because the MySQL database is ... how does the web site know to read those text files? ...
    (microsoft.public.access.forms)
  • Perl with DBI
    ... I'm not 100% sure whether to post this to an MySQL ng or Perl, ... Using phpMyAdmin, I created the database, and assigned a user to it: ... 'username' TINYTEXT NOT NULL, 'email' TINYTEXT NOT NULL, 'comment' ...
    (comp.lang.perl.misc)