Inserting Select Array Material to MySql

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

I have a database where, upon submitting a form, some tables are supposed to be able to recieve multiple rows, depending on the user input. For example:

my $med1="Award Number 1";
my $med2="Award Number 2";
my $med3="Award Number 3";

my %medals=(
"$med1"=>"$me1",
"$med2"=>"$me2",
"$med3"=>"$me3",

my $table="table";
my $state="INSERT INTO ".$table." (uid,mid,qty) VALUES (LAST_INSERT_ID
+(),?,?)";
foreach (keys%medals){
if($medals{$_}>0){
my $sth=$dbh->prepare($state);
$sth->execute ($_ ,$medals{$_});
}
}
[download]

This comes from a bunch of text inputs put into a hash so as to get the name of an award and how many times the award was given. This worked all well and fine but when I tried to use the same code with a different table name and column names it wouldn't work. I'm trying to insert rows from checkboxes using a simple array, which would input rows depending on which boxes met a certain condition but it is only inserting one line of the array which met the conditions. I have searched high and low for information on using perl/cgi to input information put into an array and have basically come up with nothing that works.

Is there something I'm missing here(or, I should say what is it I'm missig) when it comes to doing this. I'm basically down to sets of checkboxes and need to enter them (as many as are checked) into mysql table rows.If I could get the checked box info to insert I would be good to go.

Also, if it makes any difference at all--this is being done using xampp

Comment on Inserting Select Array Material to MySql Download Code

Replies are listed 'Best First'.
Re: Inserting Select Array Material to MySql by 1nickt (Canon) on Sep 11, 2015 at 03:47 UTC
Hi typomaniac. Your code doesn't compile, because you're just pasting chunks from your script. Eg: `my %medals=( "$med1"=>"$me1", "$med2"=>"$me2", "$med3"=>"$me3", my $table="table";` [download] Please post actual working code, with `use strict;` and `use warnings;` enabled. Then we'll be able to help more easily! See How do I post a question effectively? Edit: Also, here's a couple of observations. You should use `$dbh->prepare` outside a loop and then use `$dbh->execute` inside the loop with the bind values. Your code prepares the statement handle each time through the loop; the whole point of `prepare()` is so you only have to do it once. You use `LAST_INSERT_ID()` each time you execute the statement handle as one of the bind values, but you don't do any (more) inserts. If what you really want is the same value for that column in each row, get it before you prepare the statement handle (once) and put it in the SQL. Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l] [select]
Re: Inserting Select Array Material to MySql by poj (Abbot) on Sep 11, 2015 at 07:44 UTC
Assuming `uid` is `AUTO_INCREMENT` try `my $sql = "INSERT INTO $table (uid,mid,qty) VALUES (NULL,?,?)"; my $sth = $dbh->prepare($sql); for (keys %medals){ if ( $medals{$_} > 0 ){ $sth->execute ($_ ,$medals{$_}); } }` [download] `LAST_INSERT_ID()` will work for the first insert of the session because it will be 0, but subsequent inserts will fail as you try to enter another record with the same uid as the previous one. poj	[reply] [d/l] [select]
Re^2: Inserting Select Array Material to MySql by typomaniac (Novice) on Sep 12, 2015 at 04:43 UTC
Thanx, sorry for the screwball coding--fixed now. The LAST_INSERT_ID(), was actually tracking from one insert statement to the next. Anyway, on the other issue, I will have to re-post that one although I did find that the wrong column name was listed. The main problem I'm having is to get checkboxes to insert into mysql---if there were 5 boxes and 3 were clicked only one of them shows up in the db. As said before I will post at a later time when I get the xampp server software all working again.	[reply]