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

Question

This is a complement to my resolved question here: how to get an array of zip codes within x miles in perl

Ok, I have an @zips array. Now I'm 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 can’t make it work. (I use placeholders and, as you see in my link above.)

I managed 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 similarities with accuracy, this is not quite what I want. Just wanted to see if I could ANYTHING work on my own.

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

Actually, there is much more to this request, but I left everything to save it here.

. , .

.

Answer 1

, , .

( , $dbh MySQL):

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

.

- , , 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.

Answer 2

, , . WHERE zip IN (?) , () ( IN?).

, , " " :

#!/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);

Answer 3

, CPAN, DBIx::Perlish :

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

.

: DBIx::Perlish.

Answer 4

perl , SQL: SQL IN ? -

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

I doubt that just adding an array to perl will create the correct rows for the SQL string ...

Answer 5

You need to turn the array into a string of values ​​separated by commas. Try the following:

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