Perl/cgi DBI to mysql database select query with parameter

Like this:

my $min_salary=50000;

my $sql = qq{ select name, salary from employees where salary > ? };
my $sth = $dbh->prepare($sql) || die "Failed to prepare";
$sth->execute($min_salary) || die "Failed to execute";

while (my ($name, $salary) = $sth->fetchrow_array) {
    # do what you want with $name and $salary for this employee
}
$sth->finish;

Well, roundel35 has already done this.  Look at this line:

my $sql = qq{ select name, salary from employees where salary > ? };

Select all Open in new window

See the "?" in there?  That is replaced by the argument is this line:

$sth->execute($min_salary) || die "Failed to execute";

Select all Open in new window

So, when the SQL executes, the "?" will be replaced by the value of $min_salary.

Let's try a slightly more complex example, using a placeholder in an insert.  Say you've got a comma separated file with names, ages, and genders of various people.  You've got the database all set up, now you want to insert all of your data into your table.  Here's a quick way to use placeholders to insert that data:

$sth = $dbh->prepare("
   INSERT INTO people_data
   VALUES (?,?,?)
");

open(DATAFILE, "datafile.csv");
while (<DATAFILE>) {
    chomp;
    my($name, $age, $gender)=split(/,/);
    $sth->execute($name, $age, $gender);
}
$sth->finish();

Select all Open in new window

See what's going on here?  You can use the same thing in queries - put in question marks for the various parameters that you want to change, then just supply those values when you call execute() on the handle.

You need to move your line 14 further up the code. You can't reference $cgi until you've set it using the "new" method.

Move line 14 to underneath "use CGI;" and all should be well.

That here doc is not even coming close to doing what you want.

Here's a cleaned up and corrected version.

#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

my $cgi = CGI->new;
my $cmd = $cgi->param("command");

print $cgi->header, $cgi->start_html,
      $cgi->h1("testing db connection and query");

my $dbh = DBI->connect('DBI:mysql:ncts:192.168.1.251', 'test', 'test2011',
                        { RaiseError => 1 } )
        or die "Couldn't open database: $DBI::errstr; stopped";

my $sth = $dbh->prepare("SELECT value
                         FROM viewer_text
                         WHERE status = ?");

$sth->execute($cmd);

while ( my ($value) = $sth->fetchrow_array() ) {
    print $cgi->p($value);
}

$dbh->disconnect();
print $cgi->end_html;

Select all Open in new window

I finally got ActivePerl working on IIS on this computer.  The code above by FishMonger was the most complete and error-free example I could find.  I had a couple of examples from somewhere else that I was able to fix by referring to FishMonger's example.  Good question, helped me a lot...

See the instructions here: http://docs.activestate.com/activeperl/5.14/install.html#installing%20activeperl%20on%20windows%20%28x86%29

If you have a previous version, uninstall and Delete Everything in the Perl directory.  For some reason, you can't simply upgrade versions, you must get rid of the old one first.  And you have kill IIS while you are doing the install for everything to register properly.  I'm running Windows XP and I reboot between each of these steps.

After you have ActivePerl installed, then and only then can you use 'ppm' to go get the DBD extension for MySQL.  When I tried to install the new version over the old version, something kept trying to use a DLL from the old version which simply didn't work.  Not until I completely cleaned out the old versions and reinstalled the newest version and drivers did things work as they should.

It is really important that you uninstall the program AND delete all of the files and directories under Perl.  Make sure your own Perl programs are somewhere else on your system.  I had to try this 3 times before I got it all working.

Most other programs take care of uninstalling incompatible previous files but ActivePerl does not.

Ok, now we're getting really far off topic of the original question.  Issues regarding installing perl and or IIS and tying them together should be handled in a new thread (or threads).  What I will say on that topic is that you'll have far less headaches if you use apache instead of IIS.