Simpler DBI::MySQL queries

Cody Pendant has asked for the wisdom of the Perl Monks concerning the following question:

I know how to do the queries which go like this:

$sth = $dbh->prepare( "something" ) 
  || die "Error: " . $dbh->errstr;
$sth->execute()
while ( my $ref = $sth->fetchrow_hashref() )
    {
        print "$ref->{something}";
    }
}
[download]

But that's a lot of code to get a single value that I know is there.

I know, because I've asked this question before on the Chatterbox, (sorry about any déjà vu!) that there's a way to do something like this

$single_scalar = $sth->something();
[download]

It's not a major issue, it's just that when I just want to get something really simple, like the first name of a person whose ID is '1', and I know they exist, I still end up doing that whole hashref thing, which is overkill.

As I say, I know I've asked this before, and forgotten the answer, but I also don't know where to look in the documentation to find answers like this. So it's a "teach a man to fish" thing. Tell me where I should be looking up this kind of thing and I promise not to ask again.

($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss')
=~y~b-v~a-z~s; print

Comment on Simpler DBI::MySQL queries Select or Download Code

Replies are listed 'Best First'.
Re: Simpler DBI::MySQL queries by davido (Cardinal) on Dec 01, 2004 at 06:51 UTC
You are probably looking for selectrow_arrayref() (and its siblings). `$ary_ref = $dbh->selectall_arrayref($statement); $hash_ref = $dbh->selectall_hashref($statement, $key_field); $ary_ref = $dbh->selectcol_arrayref($statement); $ary_ref = $dbh->selectcol_arrayref($statement, \%attr); @row_ary = $dbh->selectrow_array($statement); $ary_ref = $dbh->selectrow_arrayref($statement); $hash_ref = $dbh->selectrow_hashref($statement);` [download] DBI also allows you to prepared cached statements, and to repeatedly execute prepared statements using placeholders. The code pasted above is from the documentation for DBI. Dave	[reply] [d/l]
Re: Simpler DBI::MySQL queries by edan (Curate) on Dec 01, 2004 at 06:53 UTC
Look at the `selectrow_`, `selectcol_` and `selectall_*` methods in DBI (`perldoc DBI`) -- edan	[reply]
Re: Simpler DBI::MySQL queries by b10m (Vicar) on Dec 01, 2004 at 08:33 UTC
You definately want to take a peek at Class::DBI (I think ... ;) -- b10m All code is usually tested, but rarely trusted.	[reply]
Re: Simpler DBI::MySQL queries by bart (Canon) on Dec 01, 2004 at 11:07 UTC
Take a look at DBIx::Simple (Hi, Juerd!). Many queries can become a one-liner, and the interface with SQL::Abstract is simply magic.	[reply]
Re^2: Simpler DBI::MySQL queries by Juerd (Abbot) on Dec 18, 2004 at 12:41 UTC
(Hi, Juerd!) (Hello!) Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }	[reply]
Re: Simpler DBI::MySQL queries by jZed (Prior) on Dec 01, 2004 at 18:17 UTC
What you are probably thinking about is $dbh->selectrow_array() which can get a single scalar with a single call. It combines prepare,execute,fetch into one call and returns an array in list context and a scalar in scalar context (it is not like a normal array that returns the number of elements of the array in scalar context). But you need to be careful because you must make sure that the query returns only one column and one row. The example below is ok because it asks for a single column and (assuming that the ids are unique, only a single row): `my $username = $dbh->selectrow_array(" SELECT username FROM tbl WHERE id = 9 ");` [download] But this next example is bad, it retrieves all columns and while it may work, exactly which column is returned is not guaranteed: `my $username = $dbh->selectrow_array(" SELECT * FROM tbl WHERE id = 9 ");` [download] And this last example is only ok if you want the first row, but not the rest, since it may gather many rows and the selectrow_array will only return the first (note the less than comparison): `my $username = $dbh->selectrow_array(" SELECT username FROM tbl WHERE id < 9 ");` [download]	[reply] [d/l] [select]
Re^2: Simpler DBI::MySQL queries by Cody Pendant (Prior) on Dec 02, 2004 at 00:44 UTC
Thanks everyone. That's what I needed. Also I now know that 'perldoc DBI' was the place to look. My code is now simpler. ($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss') =~y~b-v~a-z~s; print	[reply]
Re^2: Simpler DBI::MySQL queries by Anonymous Monk on Dec 02, 2004 at 04:58 UTC
Manual blindness, I call it, where you read the perldoc over and over and yet can never find the call you think must be there somewhere. Ta muchly.	[reply]
Re: Simpler DBI::MySQL queries by dragonchild (Archbishop) on Dec 01, 2004 at 13:24 UTC
I tend to write my own DBI-wrapper class. So, I get to have a fetch_value() statement which takes an SQL query and returns back a single value. :-) Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re: Simpler DBI::MySQL queries by Anonymous Monk on Dec 01, 2004 at 09:26 UTC
`($sth = $dbh->prepare( "something" ))->execute(); my $val= $sth->fetchrow_array();` [download] I've no doubt a true perlmeister could find better (and I'll take a slap for removing your error handling).	[reply] [d/l]
Re^2: Simpler DBI::MySQL queries by Mutant (Priest) on Dec 01, 2004 at 10:00 UTC
I'll let you off that slap if you set RaiseError to true. :) If you're doing more than a couple of calls to DBI, it's usually better to have this turned on, and wrap something at a high level in an eval{}.	[reply]