Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

how do i construct a sql select statement, where i want to get the where clauses out of an array

by pitbull3000 (Beadle)
on Aug 25, 2001 at 20:32 UTC ( #107836=perlquestion: print w/replies, xml ) Need Help??

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

dont know if i get beaten for asking something bout sql here...;D ive got a table in a mysql db which looks like id size price color also i got an array @id = (25, 36, 45, 109), now i want a select statement in which only the rows with the ids out of the array @id are returned... my try was
$statement = qq[SELECT id, size FROM table WHERE id = '@id'];
but this wont work, do i have to write for each id to select a where clause (sorry for my bad english...;.))
  • Comment on how do i construct a sql select statement, where i want to get the where clauses out of an array
  • Download Code

Replies are listed 'Best First'.
Re (tilly) 1: how do i construct a sql select statement, where i want to get the where clauses out of an array
by tilly (Archbishop) on Aug 25, 2001 at 21:08 UTC
    Two solutions.

    The first is to do:

    my $ids_in = join ", ", @in; my $sql = "select id, size from table where id in ($ids_in)";
    The other is to put the ID's into a temp table and join on the temp table.

    UPDATE
    Forgot some silly parens. Thanks runrig.

Re: how do i construct a sql select statement, where i want to get the where clauses out of an array
by Corion (Patriarch) on Aug 25, 2001 at 20:40 UTC

    In my projects, I had routines like the following :

    sub selectAll { my ($what,$table,@clauses); my $where = ""; if (@clauses) { $where = " WHERE " . join(" and ",@clauses); }; my $statement = "SELECT $what FROM $table $where"; my $sth = $dbh->prepare($statement); $sth->execute() or die "SQL: selectAll: '$statement' failed."; return $sth->fetchall_arrayref(); };

    Nowadays, I'm using mostly prepared SQL queries with prepared parameters, as this gives automatic quoting, parameter count checking and syntax checking before that statement is executed the first time. If you don't have dynamic tables (and if you have, you should maybe rethink your database layout), the following should work for you :

    my $sthGetFile = $dbh->prepare("select VISUAL,LINK from FILES where (I +D=?)"); sub getFile { my ($file) = @_; $sthGetFile->execute($file); return $sthGetFile->fetchrow_arrayref(); }; # Assuming $id exists in the table print getFile($id)->[1];

    Update: On rereading the original post, I think the real answer is

    my @id = (1,2,3,4,6,7,8); my $clause = ""; if (@id) { $clause = "where ID in (" . join(",",@id) . ")"; }; my $statement = "select ID,COLOR from TABLE $clause"; ...

Re: how do i construct a sql select statement, where i want to get the where clauses out of an array
by chipmunk (Parson) on Aug 25, 2001 at 23:48 UTC
    I like to use placeholders with DBI, for efficiency and robustness. You can prepare a statement once, and use it over and over again with different bind values. However, it's difficult to use placeholders efficiently with the IN() function, because you might not know ahead of time how many values you will want to bind. To deal with this issue, I came up with the following approach:
    my @sth; while (<DATA>) { my @id = split /,/, $_; $sth[@id] ||= $dbh->prepare( 'SELECT id, size FROM table WHERE id IN (' . join(',', ('?') x @id) . ')' ); $sth[@id]->execute(@id); while (my($id, $size) = $sth[@id]->fetchrow_array()) { print "$id $size\n"; } } __DATA__ 1,4,6 7,10 9 2,5,8 3
    Instead of a single statement handle in $sth, I have an array of statement handles in @sth. After setting up @id, I make sure there's a statement handle prepared with the right number of placeholders. For example, the first time @id has three values, $sth[3] will be undef, so a new statement handle is prepared, with three placeholders, and assigned to $sth[3]. The next time @id has three values, $sth[3] will already hold the proper statement handle.

    The statement handle is executed on the next line, with the values in @id bound to the placeholders. The results are fetched and processed, and then the process starts over with new values in @id. Note that @id is in a scalar context in $sth[@id] and ('?') x @id, and a list context in execute(@id).

Re: how do i construct a sql select statement, where i want to get the where clauses out of an array
by thpfft (Chaplain) on Aug 25, 2001 at 21:11 UTC

    mysql supports the IN() clause. So it might be easiest to use this:

    SELECT id, size FROM table WHERE id IN ('$id[0]', '$id[1]', ...)

    Which i would build this way, but there are plenty of others:

    my $idlist = join(',', map($dbh->quote($_),@id)); $statement = "SELECT id, size FROM table WHERE id IN ($idlist)";

    Apparently there are issues with the length of @id: past a certain point it would need to be divided into chunks for this approach to work.

    update *sigh*. redundant again.

Re: how do i construct a sql select statement, where i want to get the where clauses out of an array
by greywolf (Priest) on Aug 25, 2001 at 20:51 UTC
    You could try:
    $statement = "SELECT id, size FROM table WHERE "; for ($x = 0; $x < @id; $x++) { if ($x > 0) { $statement .= " || "; } $statement .= "id = '$id[$x]'"; }


    Update: fixed a typo

    mr greywolf
Re: how do i construct a sql select statement, where i want to get the where clauses out of an array
by George_Sherston (Vicar) on Aug 25, 2001 at 21:25 UTC
    One More Way To Do It:
    use DBI; my $dbh = DBI->connect("DBI:mysql:database=your_db"); my $where = "(id = '" . join ("' OR id = '", @ids) . "')"; my $sth = $dbh->prepare("SELECT * FROM event WHERE $where");
    Then loop through the db output with
    while ($ref = $sth->fetchrow_hashref) { &do_stuff_with($ref->{'db_col_u_like'}); }
    NB when @ids is empty your SQL statement is SELECT * FROM event WHERE (id = '') which may be just what you want.

    George Sherston
      bout the problem of an empty array i already thought, but the array shouldnt be empty it is filled with values out of a form, which invokes the script... but u never know... would it be some solution to put a zero as first id in the array, cause the zero wont be an id in the db table
        Yes, or just put the whole thing in an if (@ids) { ... }loop - which would make it work quicker when @ids was empty

        George Sherston
Re: how do i construct a sql select statement, where i want to get the where clauses out of an array
by runrig (Abbot) on Aug 25, 2001 at 22:42 UTC
    If there are not too many ids then you could do:
    $where_clause = 'where id in ('.join(",",("?") x @id).')'; $sql .= $where_clause; my $sth = $dbh->prepare($sql); $sth->execute(@id);
    If there ARE too many ids, then you're stuck with:
    $sql .= 'where id = ?'; my $sth = $dbh->prepare($sql); for my $id (@id) { $sth->execute($id); ... }
    How many is TOO many? Dunno. Depends. 256 or so?

    Update: I like tilly's temp table idea. I've actually done that in some cases.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (3)
As of 2022-12-01 06:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?