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

Fellow monasterians

Not sure whether this is a MySQL, DBI, or Perl question, or all three.

I want to retrieve data from a table by matching any number of values in one particular column. The number of values I'm trying to match will change from query to query. Here's my sample table:

+-----+------------+
| id  |  somedata  |  
+-----+------------+
|  1  |  aaaaaaaa  |  
+-----+------------+
|  2  |  bbbbbbbb  |  
+-----+------------+
|  3  |  cccccccc  |  
+-----+------------+
|  4  |  dddddddd  |  
+-----+------------+
|  5  |  eeeeeeee  |  
+-----+------------+

[download]

Example: in the first query I want to retrieve 'somedata' by matching 'id's 2,3, and 5. And in the next query just: 2. And in the next query: 1-5. So, the expected:

SELECT somedata FROM table WHERE id = 2 OR id = 3...

wouldn't work because the number of OR conditionals would need to vary. Building a dynamic query string seems like it could get unwieldy, especially if you have lots of ids to match:

my $stmt = "SELECT somedata FROM table WHERE id = $ids_to_match[0]";

for my $i ( 1 .. $#ids_to_match ) {
   $stmt .= " OR id = $ids_to_match[$i]";
}

[download]

I considered multiple queries:

for my $i ( 0 .. $#ids_to_match ) {
   my $stmt = "SELECT somedata FROM table WHERE id = $ids_to_match[$i]
+";
   [execute, fetch, push into AoH]
}

[download]

but seemed like a drain on the server. So, what am I not considering? Have I missed some key MySQL or DBI method? Thanks!


—Brad
"Don't ever take a fence down until you know the reason it was put up." G. K. Chesterton

Replies are listed 'Best First'.
Re: Retrieving multiple records from database
by gmax (Abbot) on Oct 16, 2004 at 17:39 UTC

    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);

    [download]

    Alternatively:

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

my $sth = $dbh->prepare($query);

$sth->execute();

    [download]

    See DBI Recipes for more idioms.

     _  _ _  _  
(_|| | |(_|><
 _|
[reply]
[d/l]
[select]
      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

[reply]

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

      [download]

      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
[reply]
[d/l]
[select]
        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().

[reply]

        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

[reply]
Re: Retrieving multiple records from database
by pernod (Chaplain) on Oct 16, 2004 at 17:34 UTC

    I usually do:

    my $stmt = "SELECT somedata FROM table WHERE id IN( " . join( ", ", @i
+ds_to_match . " )";

    [download]

    Although this assumes that all the id's are properly validated. I don't really like this solution myself as it does not use placeholders. Care should therefore be taken to avoid SQL-injection attacks.

    I look forward to seeing better solutions to this problem. Just my two cents anyway :)

    pernod
    --
    Mischief. Mayhem. Soap.

[reply]
[d/l]

      This is the way to go, however there is a small problem.

      Most databases have a limitation on the length of a SQL query string. By doing a join, without checking length, your code is exposed to this kind of problem.

      An enhanced approach is to join, but multiple joins if needed, and each join only produce string that is shorter than a predetermined length.

[reply]
Re: Retrieving multiple records from database
by pg (Canon) on Oct 16, 2004 at 18:26 UTC
    "I considered multiple queries:...but seemed like a drain on the server."

    That's not the only problem. As all the query strings and query results are most likely sent through network, this approach actually uses a bit more bandwidth than needed.

[reply]

Back to Seekers of Perl Wisdom