Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

DBI problem on fetchrow

by Win (Novice)
on Nov 15, 2007 at 17:14 UTC ( #651025=perlquestion: print w/replies, xml ) Need Help??
Win has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

The following bit of code is giving me jip. Please can someone (except marto) point me in the right direction.
my $Select_max_score = "SELECT MAX(M.Score) FROM Regions M "; my $sth_max_score = $dbh->prepare($Select_max_score) or die "Couldn' +t prepare query: ".$dbh->errstr; $sth_max_score->execute() or die "Couldn't execute query: ".$sth_max +_score->errstr; my @max_score; while (@max_score = $sth_max_score->fetchrow_array) { }

Replies are listed 'Best First'.
Re: DBI problem on fetchrow
by moritz (Cardinal) on Nov 15, 2007 at 17:21 UTC
    A max(anything) will only return one value (unless combined with GROUP BY or the like).

    So you can just

    $sth_max_score->execute(); # no loop here: my @max_score = $sth_max_score->fetchrow_array(); # now $max_score[0] holds your desired value

    There's a prettier solution:

    $sth->execute(); $sth->bind_columns(\my $max); $sth->fetch; # $max holds the value here
Re: DBI problem on fetchrow
by erroneousBollock (Curate) on Nov 15, 2007 at 17:26 UTC
    You've just been given a reasonable answer in the CB.
    my $sth = $dbh->prepare("SELECT MAX(M.Score) as score FROM Regions M +"); die "can't blah ..." unless $sth && $sth->execute(); my $score = (($sth->fetchrow_hashref) || {})->{score};


Re: DBI problem on fetchrow
by kyle (Abbot) on Nov 15, 2007 at 17:27 UTC

    This query doesn't look right to me: "SELECT MAX(M.Score) FROM Regions M". Specifically that stray "M" on the end doesn't seem like proper syntax. Since you're specifying "M.Score" as what you want, I'm guessing "M" is a table name. Maybe you want a comma between "Regions" and "M"? It really doesn't die when you try to prepare or execute that?

    Update: Oh yes, I have seen table aliases before, thanks for the many reminders. Since I hardly use them myself, I'm not used to seeing them. Sorry about the confusion.

      That part of what the OP is doing is ok. "M" is being used as a table alias. SQL syntax supports table aliases either with or without the optional keyword "AS", so these two are the same, they both let you refer to "Regions" as "M":
      SELECT FROM Regions AS M; SELECT FROM Regions M;

        As a matter of style I would recomend using the "as" consistently. It reads better.

        BTW, "Regions as M"? Maybe there is a reason to use M for the Regions table, but I would expect R or (in case the table was used several times in the query) something like R_old, R_new, R1, R2, .... In either case I do not see why would you use an alias in a query that only includes one table. I guess a matter of style again :-)

      That's called a "table alias" in SQL and is perfectly valid.

      You may like to read the SQL99 standard.


      The SQL works fine with MySQL, the M is a table alias, the syntax is ... FROM table1 alias1, table2 alias2, ... with the alias$n being optional

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://651025]
Approved by moritz
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (7)
As of 2018-06-20 15:59 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (116 votes). Check out past polls.