Re: select from mysql table based on two column match

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;
}
[download]

Comment on Re: select from mysql table based on two column match Download Code

Replies are listed 'Best First'.
Re^2: select from mysql table based on two column match by marto (Cardinal) on Jan 24, 2017 at 10:54 UTC
"If that is what you are looking for here you go, Untested though!" Some comments: `#!/usr/bin/perl -w use strict; use warnings;` [download] You have `use warnings;` and `perl -w`, both activate warnings. `#Get first input print "Enter cpt code> "; my $cptcode = <STDIN>;` [download] You're not chomping user input. `my $sth = $dbh->prepare('SELECT * FROM charge WHERE column1 = $cptcode + AND column2 = $cpt2code’)` [download] This is dangerous. See SQL_injection/Bobby Tables. Use placeholders and bind variables.	[reply] [d/l] [select]
Re^3: select from mysql table based on two column match by afoken (Chancellor) on Jan 26, 2017 at 20:15 UTC
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 -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re^2: select from mysql table based on two column match by haukex (Archbishop) on Jan 24, 2017 at 10:54 UTC
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	[reply]
Re^2: select from mysql table based on two column match by abner (Novice) on Jan 24, 2017 at 18:50 UTC
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.	[reply]
Re^3: select from mysql table based on two column match by Anonymous Monk on Jan 25, 2017 at 06:18 UTC
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.	[reply]