MySQL - Perl: How to use an array with IN within a select query? (WHERE IN (@array))

Question

This is an addition to my solved question here: how to get array of zip codes within x miles in perl

OK, I have the array @zips. Now I am trying to use it in a query like this:

SELECT `club_name`,`city` FROM `table` WHERE `public_gig` = 'y' AND `zip` IN (@zips)
#I also tried syntax "IN ("@zips"), IN @zips and IN ('@zips')"

But, I cannot get it to work. (I am using placeholders and such as you see in my link above.)

I was able to get this to work:

$fzip=shift(@Zips);
$lzip=pop(@Zips);
SELECT `club_name`,`city` FROM `table` WHERE `public_gig` = 'y' AND `zip` BETWEEN $fzip AND $lzip

    ZIP | public_gig | start_time | fin_time | city       | club_name | and so on
  33416 | y          | 9pm        | 2am      | clearwater | beach bar | yada

But, for obvious reasons and some resemblance of accuracy, that is not really what I want. Just wanted to see if I could get SOMETHING working on my own.

Why can't I get the query to work with the zips in the array using IN?? Nothing is returned and there is no error.

There is actually a lot more in that query but, I left it all out to keep it short here.

I tried to figure it out by myself. Obviously, my learning capacity for the day is near peak.

Thanks for any help.

hobbs · Accepted Answer · 2019-10-21 14:10:51Z

10

All of the examples posted here will screw up if any of your values contain single-quotes, don't use them.

Instead (assuming $dbh is the database handle for your mysql connection):

my $zip_string = join q{,}, map $dbh->quote($_), @zips;

and interpolate that.

Or, for something nice, but not half as outlandish as DBIx::Perlish: SQL::Abstract.

my $sqla = SQL::Abstract->new;
my ($sql, @bind) = $sqla->select(
  'table', 
  ['club_name', 'city'],
  {
    public_gig => y',
    zip => { -in => \@zips },
  }
);

$dbh->prepare($sql);
$dbh->execute(@bind);
# fetchrow etc.

edited Oct 21, 2019 at 14:10

answered Feb 28, 2011 at 23:33

hobbs

245k20 gold badges225 silver badges304 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Grrrr Over a year ago

Leaving aside the "crack-fueled" part, the statement about "all of the examples" and single quotes is factually incorrect. :-)

Stephayne Over a year ago

Thank you. I do sterilize quite well and am using DBI amd placeholders but, your advice is taken quite seriously by me. I will continue to strive for better security. I will look into SQL::Abstract and see if I need to / am capable of convert(ing) my current queries. I will prob post another question with a working example of what I finally came up with. Thanks!

hobbs Over a year ago

@Stephayne point is that constructing the in this way doesn't use placeholders, so you need to use proper quoting.

Dave Sherohman · Accepted Answer · 2011-03-01 12:03:37Z

8

This can be done using placeholders, you just have to work around the limitation that each placeholder can only accept a single value. WHERE zip IN (?) won't work because you're (presumably) looking for more than one value (otherwise, why use IN?).

You can, however, easily build a statement on the fly with the correct number of placeholders:

#!/usr/bin/env perl

use strict;
use warnings;

my @zips = (12345, 54321, 90210);
my $stmt = "SELECT `club_name`,`city`
            FROM `table`
            WHERE `public_gig` = 'y' AND `zip` IN ("
           . join(', ', ('?') x @zips) . ')';

print "$stmt\n";

# Now just:
# my $sth = $dbh->prepare($stmt);
# $sth->execute(@zips);

answered Mar 1, 2011 at 12:03

Dave Sherohman

46.4k14 gold badges67 silver badges104 bronze badges

1 Comment

hobbs Over a year ago

This is very true -- it can become somewhat confusing if you're using it in a statement with more placeholders/bind values, but this works just great. And if it gets too complicated that's where something like SQLA will really help.

Grrrr · Accepted Answer · 2011-02-28 23:29:22Z

1

Alternatively, if you don't mind using weird CPAN modules, with DBIx::Perlish you can just say:

my @results = db_fetch {
   my $t: table;
   $t->public_gig eq "y";
   $t->zip  <-  @zips;
};

and it will do the right thing.

Full disclosure: I am the author of DBIx::Perlish.

answered Feb 28, 2011 at 23:29

Grrrr

2,53620 silver badges21 bronze badges

Comments

TheEye · Accepted Answer · 2011-02-28 22:56:47Z

-1

I don't know perl too much, but this looks like a simple SQL problem: why don't you just build the SQL IN clause from your array? You should get something like

AND zip IN ('zip 1', 'zip 2', '...')

I doubt just adding an array in perl will create the right strings for the SQL string ...

answered Feb 28, 2011 at 22:56

TheEye

9,3762 gold badges45 silver badges59 bronze badges

Comments

Sam Broad · Accepted Answer · 2011-02-28 23:01:57Z

-6

You need to turn the array into a string of values seperated by commas. Try this :

my $zipcodes = join('\',\'',@zips);
SELECT `club_name`,`city` FROM `table` WHERE `public_gig` = 'y' AND `zip` IN ('".$zipcodes."');

answered Feb 28, 2011 at 23:01

Sam Broad

12 bronze badges

3 Comments

Stephayne Over a year ago

Thank you @Sam Broad! Worked perfectly! Taking the extra time to use my vars really helped me out.

Ashley Over a year ago

-1 any teaching code for DBI without placeholders makes Bobby Tables weep.

Hack Saw Over a year ago

Snarky comments without pointers to docs explaining what you mean makes teachers weep.

Collectives™ on Stack Overflow

MySQL - Perl: How to use an array with IN within a select query? (WHERE IN (@array))

5 Answers 5

3 Comments

1 Comment

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related