in reply to Re: DBI compare columns problem
in thread DBI compare columns problem

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


Replies are listed 'Best First'.
Re: (3) DBI compare columns problem
by Roger (Parson) on Sep 17, 2003 at 08:53 UTC
    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]
Re: (3) DBI compare columns problem
by hmerrill (Friar) on Sep 17, 2003 at 13:13 UTC
    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]
        Thanks guys,

        I figured it out... It was in my sql statement.

        -Paul

[reply]

In Section Seekers of Perl Wisdom