Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
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 chilling in the Monastery: (8)
As of 2014-10-01 01:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (386 votes), past polls