Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

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) { }

Comment on DBI problem on fetchrow
Download Code
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};

    -David

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 M.foo FROM Regions AS M; SELECT M.foo 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.

      -David

      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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://651025]
Approved by moritz
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (9)
As of 2014-07-11 02:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (217 votes), past polls