in reply to DBI compare columns problem

are you useing strict? ie add 'use strict' near the top of your script.

Do some reading on using 'placeholders' in your sql.

Why the extra db selects? You appear to be fetching the same values as you already have.

There's something funky here:

 $mb_accept = $dbh=$db->prepare("SELECT ....

cleaning it up a bit..

if ($mb_award > 0) {
  $mb_amount_disp = $mb_award;
} elsif ($mb_accept > 0) {
  $mb_amount_disp = $mb_accept;
}

[download]

or if you know for certain one and only one of the values will always be > 0

$mb_amount_disp = $mb_award > 0 ? $mb_award : $mb_accept;

[download]

But what should happen when neither or both values are > 0 ?

cheers,

J

Replies are listed 'Best First'.
Re: (2) Need help with DBI
by Anonymous Monk on Sep 17, 2003 at 05:19 UTC

    Hi all,

    thanks for your replies. You have to forgive me; I am very new to perl !! And to add insult to injury, I had stepped away from it for a while ... other projects!

    my first post is what I had ended up with after many, many failed attempts. Originally I had.

    my ($mb_award,$mb_accept);

    $dbh=$db->prepare("SELECT mb_award,mb_accept FROM Items WHERE itemnum=$form{'item'} AND seller=$fdnum AND

    closef='1'");
    $dbh->execute();
    ($mb_award,$mb_accept) = $dbh->fetchrow_array();


    my $mb_amount_disp;

    if ($mb_award > "0.00") {
    $mb_amount_disp = $mb_award;
    }
    elsif ($mb_accept > "0.00") {
    $mb_amount_disp = $mb_accept

    I was thinking I was "using up my variable" somewhere ..... so I put the same query in again. it is strange that this is only working for the mb_accept column...

    I am using MySql for the database and the default values for the two columns are 0.00 . Entries will be made in the rows mb_accept or mb_award; never both! I need this bit of code to see which row the entry has been made in, and then display the amount. I tried using edoc's snippet ...

    if ($mb_award > 0) {
    $mb_amount_disp = $mb_award;
    } elsif ($mb_accept > 0) {
    $mb_amount_disp = $mb_accept;
    }

    but i still end up with the same result! Nothing... it doesn't even display the default value 0.00

    thanks again,

    -paul


[reply]
      It sounds like one of the columns in your table has NULL values. Try to add nvl to your SELECT statement, and also try to change
      ($mb_award,$mb_accept) = $dbh->fetchrow_array();

      [download]
      to two steps - 
      use Data::Dumper;
...
my @res = $dbh->fetchrow_array();
print Dumper(@res);

$mb_award = $res[0];
$mb_accept = $res[1];

      [download]
      Use the Data::Dumper module to investigate your variables - I suspect you will see an undef in @res (because one of the column might be NULL).

      One last observation is that your test of greater than "0.00" does not work if both values are equal to "0.00" or one is "0.00" and the other is NULL. In other words, the test is incomplete. Perhaps you need to add some exception handling...

      if ($mb_award > 0) {
  $mb_amount_disp = $mb_award;
} elsif ($mb_accept > 0) {
  $mb_amount_disp = $mb_accept;
} else {
  # both values are <= 0 or undef, what shall I do?
  ....
}

      [download]
[reply]
[d/l]
[select]
        Good advice about Data::Dumper and actually getting to 'see' the results.   But he's using MySQL which doesn't have nvl() so he can't use that.   I can find references to nvl() in Oracle and FoxPro docs, but not MySQL.   As I look, the closest equivalent in MySQL would be   ifnull(expr1,expr2).
[reply]
      Please do a "mysql> describe your_table_name;" and post the results here - I'm wondering what the data type is of the column you are comparing to 0.00.
[reply]

        There are several other rows in my table, but I don't think they are important ... here is how my two rows and their columns are structured. Dumped from phpMyAdmin ....

        Field Type Null Key Default Extra
        mb_accept decimal(9,2) YES   0.00  
        mb_award decimal(9,2) YES   0.00  
[reply]

In Section Seekers of Perl Wisdom