Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

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 rifling through the Monastery: (4)
As of 2017-06-24 02:50 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (556 votes). Check out past polls.