difficulty forming an array for a mysql query.

[bazz]

Hi,

In one of my queries, the resultset is being stored into an array. I thne process that array from
Hostelries Dining Out Attractions

into
'Hostelries','Dining Out','Attractions'

If I hard code that last line into my query, it works as expected. However, I need it to be dynamically created and so I ask for you help in building the correct array for the query (below).

here is my attempt.

push(@business_types,$business_type);
  $_ = qq('$_') for @business_types;
  $business_types_to_exclude = join(',',@business_types);

when I print that out, it does it like this:

'Hostelries','Dining Out'

which I think is how it should be built (???)

If I hard code that line into the query it works but it doesn't if I push it through a placeholder.

so, this works

and bt.business_type not in ('Hostelries', 'Dining Out')

but this doesn't

 push (@business_types, $business_type);

 $_ = qq('$_') for @business_types;
 $business_types_to_exclude = join(',',@business_types);

and bt.business_type not in (?) 

$sth2->execute( $business_types_to_exclude
                , $base_latitude_miles
                , $base_longitude
                , $base_radius
                , $base_radius
                , $base_radius
                , $base_radius
                , $base_radius
                );

It seems that the array is being compiled differently from how I expect so please, what am I missing?

bazz

[FishMonger]

This line is unnecessary.

$_ = qq('$_') for @business_types;

What happens if you remove that line?

[FishMonger]

This line should generate an error, if it's used as it is in your code snippet.

and bt.business_type not in (?)

[bazz]

Hi Fishmonger

if I remove the line

$_ = qq('$_') for @business_types;

I get the error message

execute statement failed: Unknown column 'Accommodation' in 'on clause'

But then, if having removed it, if I use the following execute statement, it seems to work.

$sth2->execute( @business_types_to_exclude
                , $base_latitude_miles
                , $base_longitude
                , $base_radius
                , $base_radius
                , $base_radius
                , $base_radius
                , $base_radius
                )or die "execute statement failed: $DBI::errstr\n";

is that the correct way to do it?

bazz

[FishMonger]

Yes, that is the correct method.