Re: select from mysql table based on two column match

[madtoperl]

If I understood your question correctly, you are trying to get two inputs from user in terminal say cat, dog and if those columns values in the table are matching with cat and dog respectively,
you need to select some other columns in the matching row and print it out.
If that is what you are looking for here you go, Untested though!

#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;

#Get first input
print "Enter cpt code> ";
my $cptcode = <STDIN>;

#Get Second input
print "Enter cpt2 code> ";
my $cpt2code = <STDIN>;

if ( ($cptcode != “”) && ($cpt2code != “”)) {
        my $dbh = DBI->connect('dbi:mysql:mydatabase:localhost:3306','
+abc','xyz') or die "Couldn't connect to database: " . DBI->errstr;
        my $sth = $dbh->prepare('SELECT * FROM charge WHERE column1 = 
+$cptcode AND column2 = $cpt2code’)
                or die "Couldn't prepare statement: " . $dbh->errstr;

                 $sth->execute()    # Execute the query
                or die "Couldn't execute statement: " . $sth->errstr;

        # Read the matching records and print them out
        while (my @data = $sth->fetchrow_array()) {
                my $cptanswer = $data[1];
                my $insid = $data[2];
                my $preauth = $data[3];
                my $updated = $data[5];
                print "\t$cptanswer\t$insid\t$preauth\t$updated\n";
        }

        if ($sth->rows == 0) {
                print "No names matched `$cptcode'.\n\n";
        }

        $sth->finish; 

        $dbh->disconnect;
}

[marto]

"If that is what you are looking for here you go, Untested though!"

Some comments:

#!/usr/bin/perl -w
use strict;
use warnings;

You have use warnings; and perl -w, both activate warnings.

#Get first input
print "Enter cpt code> ";
my $cptcode = <STDIN>;

You're not chomping user input.

my $sth = $dbh->prepare('SELECT * FROM charge WHERE column1 = $cptcode
+ AND column2 = $cpt2code’)

This is dangerous. See SQL_injection/Bobby Tables. Use placeholders and bind variables.

[afoken]

You have use warnings; and perl -w, both activate warnings.

But the effects of use warnings; and perl -w are slightly different, this is explained in detail in warnings. Rule of thumb: perl -w enables warnings globally (bad), whereas use warnings; enables warnings only per file or block (good).

Alexander

[haukex]

Hi madtoperl,

Your code contains “smart” quotes, so it won't compile.

$dbh->prepare('SELECT * FROM charge WHERE column1 = $cptcode AND column2 = $cpt2code’)

Single quotes won't interpolate these variables, and even if they did, you shouldn't ever interpolate user input into SQL! Use placeholders instead.

my $cptcode = <STDIN>;

These values need to be chomped to remove the trailing newline.

Update: Looks like marto and I had almost the same thoughts at the same time :-)

Regards,
-- Hauke D

[abner]

Actually no you misunderstand. I want to get two separate inputs from the user via terminal. Then I want to query a mysql table and return the rows where one column matches the first input and another column matches the second input in the same row.

[Anonymous Monk]

I want to get two separate inputs from the user via terminal. Then I want to query a mysql table and return the rows where one column matches the first input and another column matches the second input in the same row.

madtoperl's code does match that description.