array help for a IN clause

[bazz]

Hi,

The following code builds an array with the value separated with commas. mysql doesn't ike it in an IN(@array) clause and I think the values should be surrounded by ' '


foreach my $facility (sort keys %params){

if ($facility eq 'facilities_throughout')
{
push(@throughout,$params{$facility});
}
elsif ($facility eq 'room_specific')
{
push(@room_specific,$params{$facility});
}
}
$throughout = (join("/ ",(@throughout)));
$throughout =~ s/\0/,/g;


what must I do to get the array to work in a mysql query


where IN ( @throughout )


I have tried it with $throughout but neither works.

the error is:


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'player,Central Heating,Double Glazing )
GROUP
BY b' at line 7 at


bazz

[FishMonger]

See if this works better.
push(@throughout, qq('$params{'facilities_throughout'}')) if exists $params{'facilities_throughout'};
push(@throughout, qq('$params{'facilities_throughout'}')) if exists $params{'room_specific'};

$throughout = join(", ", @throughout);

[bazz]

Thanks for that FishMonger but it doesn't quite do it yet.

the reuslt is


t= 'CD/DVD player�Central Heating�Double Glazing�Left luggage facilities'


I can't seem to put the closing and opening single quote in place of the stoopid squares.

what is causing them?

bazz

[bazz]

I found that the issue with regard to the mysql error was because @room_specific was empty.

I then amended your line to this - adding the bit in red

push(@room_specific, qq('$params{'room_specific'}')) if exists $params{'room_specific'} || push(@room_specific,'1');


It has done away with the error but is it good?

bazz

[bazz]

I get it to work if there is just one value in $throughout

If there is more than one value, the query chokes and it seems to be the 'squares' that cause it.

my %params = $cgi->Vars;
my @throughout;

push(@throughout, qq('$params{'facilities_throughout'}')) if exists $params{'facilities_throughout'};

my $throughout = join(", ", @throughout);




the problematic array (with more than one value), outputs as

thr = 'CD/DVD player�Double Glazing'


aaaargh! I wish I could work this out.

bazz

[FishMonger]

Is $params{'facilities_throughout'} a multi valued form field? If so, then you need to use split to separate the values.

This can be reduced to a single statement, but it's easier to understand when split up.
my @throughout = split(/\0/, $params{'facilities_throughout'});

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

my $throughout = join(', ', @throughout);

[bazz]

Thank you FishMonger.

I had made a working snippet using the $cgi->param(''); and then looped thorugh it. Hoever, as ever, your script is shorter, neater and, undoubtedly, works better.

bazz :thumbsup: