abner has asked for the wisdom of the Perl Monks concerning the following question:

I am had a successful script to query a mysql table based on a match in two columns.

On of the columns `preauth` is of form tinyint and contains only 0's and 1's. These represent boolean yes and no values. I am trying to figure out how to display "Yes" or "No" in my output in place of the 0's and 1's.

I have been experimenting with several different strategies but can't seem to crack the code. My latest attempt and related error messages are posted below.

#!/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 u
+ser
                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;

[download]

Error Messages: 

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$

[download]

Replies are listed 'Best First'.
Re: Substitute "Yes" for "1" in mysql query output?
by Marshall (Canon) on Jan 25, 2017 at 06:05 UTC
    A few comments on your code..

    • Consider the use of RaiseError => 1 in the connect statement. If you do that, the "or die" clauses in prepare or execute methods are not needed.
    • Consider a subroutine to get the 2 user supplied parameters, if nothing else, to make to code "flow" better with easier to see boundaries: prepare statement, get user input, execute...
    • Regarding:
      if ( $preauth eq '0' ) { print "No" };
if ( $preauth eq '1' ) { print "Yes" };

      [download]
      I would code this to print "Yes" only if $preauth ==1 and "No" for all other cases.
      One way to do that is like this: print ($preauth eq '1' ? "Yes" : "No");
    Some untested example code:
    Read more... (2 kB)
[reply]
[d/l]
[select]
Re: Substitute "Yes" for "1" in mysql query output?
by poj (Abbot) on Jan 25, 2017 at 07:24 UTC

    You could just amend the sql

    SELECT updated,
       if (preauth,'Yes','No') as preauth,
       patient, evidence,staff
FROM facts
WHERE cpt = ? AND insco = ?

    [download]
    poj
[reply]
[d/l]
      I tried this. It works and I think it makes my code look better. Thank you.
[reply]
Re^2: Substitute "Yes" for "1" in mysql query output?
by soonix (Chancellor) on Jan 25, 2017 at 07:17 UTC
    If you're sure $preauth can be only 0 or 1 (or '0' or '1'), you can do
    print qw(No Yes)[$preauth];

    [download]
    Update: oops, this is meant as a reply to the OP... -- 2nd Update: thanks for reparenting
[reply]
[d/l]
[select]
      In any event, I see what you are doing. This will not produce any printout at all if $preauth is not 0 or 1 (as you alluded to)

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

my $preauth=3;

print qw(No Yes)[$preauth]; # No output at all!

print ($preauth eq '1' ? "Yes" : "No"); # Prints No

      [download]
      As a practical matter, I use SQLite for all my DB work unless I need to use another DB (and that can happen for a lot of reasons). SQLite does not have a boolean type..it is only an int. So a value like "3" could conceivably show up. My code only recognizes '1' as the 'True' value. No insurance company or provider is going to work with a DB that does not have a boolean data type.

      It appear to me that Re: Substitute "Yes" for "1" in mysql query output? is the way to go... Put this yes/no decision into the SQL solution space.

[reply]
[d/l]
        Yes, shortly after I posted, i saw poj's reply and thought "Hey, that's even better!". The only nag was, that it's not Perl :-)

        On the latter point (and your point), there may still be different views, how the database distinguishes true from false, and how Perl (and/or the respective script) does that. The best way to minimize conflicts seems to be laid out over there (CHECK constraint on the column). But of course, a real Boolean column type would be better.

[reply]
Re: Substitute "Yes" for "1" in mysql query output?
by Anonymous Monk on Jan 25, 2017 at 03:44 UTC

     my $preauth eq '0'

    Time to review perlintro for example of how to use my

[reply]
[d/l]
Re: Substitute "Yes" for "1" in mysql query output?
by abner (Novice) on Jan 25, 2017 at 03:59 UTC

    Found the problems!

    # my $preauth = $data1; (Noticed that I forgot to remove the hash from the beginning of this line).

    Got rid of "my" before $preauth in my if ($preauth eq '0'/'1') lines.

    Now it is working. :->

[reply]

Back to Seekers of Perl Wisdom