#!/usr/bin/perl -w
# first experiments in perl front end for mysql queries
use strict;
use DBI;

        my $dbh = DBI->connect('dbi:mysql:preauth:localhost:3306','xyz','abc')
                or die "Couldn't connect to database: " .        DBI->errstr;
        my $sth = $dbh->prepare('SELECT
                        `updated`,
                        `preauth`,
                        `patient`,
                        `evidence`,
                        `staff`
                        FROM facts WHERE cpt = ? and insco = ?')
                or die "Couldn't prepare statement: " . $dbh->errstr;

        print "Enter cpt code> ";              # Read input from the user
                my $cptcode = <>;
                chomp $cptcode;
                if ( defined $cptcode && $cptcode ne '') {
                print "Enter three character insurance company code> ";
                my $inscode = <>;
                chomp $inscode;

                if (defined $inscode && $inscode ne '') {    # Execute the query
                $sth->execute($cptcode,$inscode)
                or die "Couldn't execute statement: " . $sth->errstr;
        }
        }
        # Read the matching records and print them out
        print " Last update\t\tPre-auth\tPat ID\tEvidence\tAuthor\n";
        while (my @data = $sth->fetchrow_array()) {
                my $onlydate = substr( $data[0], 0, 10 );
#               my $preauth = $data[1];
                my $patient = $data[2];
                my $evidence = $data[3];
                my $staff = $data[4];
                print " $onlydate\t";
                if ( my $preauth eq '0' ) { print "No" };
                if ( my $preauth eq '1' ) { print "Yes" };
                print "\t$patient\t$evidence\t$staff\n";
        }

        if ($sth->rows == 0) {
                print "No matches for specified input.\n\n";
        }

        $sth->finish;
        print "\n";
        $dbh->disconnect;

##</code><code>##

abner@avery:~/queries/preauth$ ./test.pl
Enter cpt code> 27096
Enter three character insurance company code> 00D
 Last update            Pre-auth        Pat ID  Evidence        Author
Use of uninitialized value $preauth in string eq at ./test.pl line 39, <> line 2.
Use of uninitialized value $preauth in string eq at ./test.pl line 40, <> line 2.
 2016-11-08             0003287 brought from other table        abner
Use of uninitialized value $preauth in string eq at ./test.pl line 39, <> line 2.
Use of uninitialized value $preauth in string eq at ./test.pl line 40, <> line 2.
 2016-11-08             0003287 brought from other table        mary

abner@avery:~/queries/preauth$