Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Re: Retrieving multiple records from database

by gmax (Abbot)
on Oct 16, 2004 at 17:39 UTC ( #399785=note: print w/ replies, xml ) Need Help??


in reply to Retrieving multiple records from database

Use the "IN" operator instead of "OR". Here's my favorite idiom (using placeholders):

my @ids_to_match = ( ... ); my $query = "SELECT somedata from table WHERE id IN (" . join(",", map {"?"} @ids_to_match ) .")"; my $sth = $dbh->prepare($query); $sth->execute(@ids_to_match);

Alternatively:

my $query = "SELECT somedata from table WHERE id IN (" . join(",", map { $dbh->quote($_) } @ids_to_match ) .")"; my $sth = $dbh->prepare($query); $sth->execute();

See DBI Recipes for more idioms.

 _  _ _  _  
(_|| | |(_|><
 _|   


Comment on Re: Retrieving multiple records from database
Select or Download Code
Re^2: Retrieving multiple records from database
by bradcathey (Prior) on Oct 16, 2004 at 18:44 UTC

    Thanks gmax (I was secretly hoping you'd see this OP). Ended up with a more familier:

    $stmt = qq/SELECT somedata FROM table WHERE id IN (/ . join(',', ('?') + x @ids_to_match ) . qq/)/; $sth = $dbh->prepare($stmt); $sth->execute(@ids_to_match); my $results = $sth->fetchall_arrayref({});

    My next question is why does fetchrow_array() only return an array of 1 element, but fetchall_arrayref({}) nabs them all?


    —Brad
    "Don't ever take a fence down until you know the reason it was put up." G. K. Chesterton
      My next question is why does fetchrow_array() only return an array of 1 element, but fetchall_arrayref({}) nabs them all?

      Well the difference between fetch*row* and fetch*all* explains why fetchrow gets just one row and fetchall gets them all. The difference between fetchrow_*array* and fetchrow_*arrayref* explains why using a scalar returns 1 value for an array and all values into an arrayref. If you want the full row, you need to do @row = fetchrow_array(), not $row = fetchrow_array().

      The array that "fetchrow_array()" returns is the selected columns (each in their own element of the array) from a single row. If your query grabbed multiple rows, you need to keep calling fetchrow_array() to get the next row, and so on. Undef will indicate you're done.

      fetchall_arrayref() fetches all of the rows, in a multidimensional array where the 2nd dimension is the columns.


      Dave

        Thanks, davido, I ended up with this in my final app:

        while ($row = $sth->fetchrow_array()) { push (@categories, $row); }

        I guess fetchrow and fetchall we appropriately named ;^) Now, I wish there were a fetchcol that just retrived a known column of a given row (intersection, so selectcol_arrayref wouldn't work) so I don't have to do:

        $single_value = $categories[0];

        after the fetch, and could just do:

        $single_value = $sth->fetchcol_justone;

        —Brad
        "Don't ever take a fence down until you know the reason it was put up." G. K. Chesterton
Re^2: Retrieving multiple records from database
by mpeppler (Vicar) on Oct 17, 2004 at 15:58 UTC
    This is a really neat idiom, but there is a caveat: some database engines impose a limit on the number of items in an IN clause, and/or impose a limit on the number of placeholders that can be used in a SQL statement.

    Michael

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (4)
As of 2014-10-25 05:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (142 votes), past polls