Checkboxes into an SQL Select query

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

Dear monks,

Try as I might, I have searched through the archives and even looked at CGI.pm but have not been able to work out what I am doing wrong.

I have an HTML form with six checkboxes all formatted as such:

<input type="checkbox" name="chk1" value="Listname" onclick="sql()" />
[download]

The JavaScript just links to a text box and shows the query being created so the user can check it before sending it to the db.

The name goes from chk1 to chk6 and the value names correspond to column names in a MySQL db. What I need to do is to pass the checked values to a Perl script which is combining all of the values and creating the SQL query. Whilst I can get the rest of the SELECT script to build correctly, I have been unable to get this first section which defines the relevant columns to search to work.

If have tried using:

if (exists $fd{chk1} ) { 
push @columns
}
[download]

and

if ($fd{chk1} eq "Listname") {
$columns = $db->quote($fd{$_}) .",";
}
[download]

What I was trying to do was to pop the column names into an array, @columns, and then assign the array to $columns which the SELECT command calls but I have managed to lose my way somewhat.

I would be very grateful for any help so I can learn what I have done incorrectly.

Many thanks in advance.

Comment on Checkboxes into an SQL Select query Select or Download Code

Replies are listed 'Best First'.
Re: Checkboxes into an SQL Select query by Roy Johnson (Monsignor) on May 30, 2007 at 15:55 UTC
I think you want to grep the checked values and then join them. Maybe use map to apply the `$db->quote` to them. Something like (read bottom to top): `$columns = join ',', # paste them together separ +ated by commas map { $db->quote($fd{$_}) } # extract the value and quo +te it grep { exists $fd{$_} } # keep only the checked one +s ('chk1'..'chk6') # Checkboxes ;` [download] Caution: Contents may have been coded under pressure.	[reply] [d/l] [select]
Re^2: Checkboxes into an SQL Select query by Quicksilver (Scribe) on May 31, 2007 at 08:48 UTC
Many thanks Roy, the code has pretty much achieved what I was trying to do though it replaces commas with \0 but I'm sure I can sort that out today.	[reply]
Re: Checkboxes into an SQL Select query by agianni (Hermit) on May 30, 2007 at 16:13 UTC
Can you explain where you are getting $fd from? I'm assuming you're using the `Vars` method of CGI, but it would be good to be explicit about it. As to your qestion, I might consider doing this by naming all of your checkboxes the same thing, thus receiving an arrayref back to start with: `<input type="checkbox" name="chk" value="foo" onclick="sql()" /> <input type="checkbox" name="chk" value="bar" onclick="sql()" /> <input type="checkbox" name="chk" value="baz" onclick="sql()" />` [download] Then you can: `my $columns = join ',' # join with commas map { $db->quote($_) } # quote everything @{$fd{chk}}; # dereference the arrayref` [download] `perl -e 'split//,q{john hurl, pest caretaker}and(map{print @_[$_]}(joi +n(q{},map{sprintf(qq{%010u},$_)}(2*23074993,51016415261,75979 +36997,13177145131,3*26789167*181))=~/\d{2}/g));'` [download]	[reply] [d/l] [select]
Re^2: Checkboxes into an SQL Select query by Quicksilver (Scribe) on May 31, 2007 at 08:53 UTC
Agianni, Sorry, should have explained the script that I'm working on converting uses $fd to grab the form data and identify it in Perl. Point taken on the rename which I've done in the checkboxes to standardise them.	[reply]
Re: Checkboxes into an SQL Select query by graff (Chancellor) on May 31, 2007 at 01:56 UTC
If have tried using: `if (exists $fd{chk1} ) { push @columns }` [download] The "push" function needs one or more args after the array, which would be the value(s) to be pushed onto the array. Your push statement there (with just the array alone) is not doing anything at all. (Was that a copy/paste error in creating the post, or did you really miss that point in the original code?) Update: As for your other attampt: `if ($fd{chk1} eq "Listname") { $columns = $db->quote($fd{$_}) .","; }` [download] That doesn't quite make sense either, because the "if" condition is testing `$fd{chk1}` but the assignment statement inside the block is referring to `$fd{$_}` -- and you haven't shown anything to suggest that $_ would actually equal "chk1" at that point (it probably does not).	[reply] [d/l] [select]
Re^2: Checkboxes into an SQL Select query by Quicksilver (Scribe) on May 31, 2007 at 08:55 UTC
Graff, I had thought that I'd failed to link the statement properly but by that time had tied myself up in knots untying it as 'twere.	[reply]