perl to write to more than one table in a database at the same time (or call anotherscript to do one)

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

Hi Monks, I have a problem I want perl to insert data into a mysql database with data from more than one table. I'm also having form connecting problems, but thatll come later.

$dbh = DBI->connect('dbi:mysql:Database','root','password')
or die "Connection Error: $DBI::errstr\n";

my $sth = $dbh->prepare("insert into studenttbl(fname, lname, grade, D
+OB, email, tel_no, membertype, username) values ('$fname','$lname','$
+grade','$DOB','$email', '$tel_no', '$membertype', '$username') "); 
$sth->execute();
my $sth2 = $dbh->prepare("insert into logintbl(username, password) val
+ues('$username', '$password') ");
print "Content-type: text/html\n\n";
print "<h1> thanks for registering </h1>";
}
[download]

THE ABOVE DOES NOT WORK- How can I get perl to connect to more than one table and then insert the values in?

ive searched online, but what I try never seems to work for example you cant make two mysql statements because they clash with perls ';' end statement and therefore with mysql statements ending ';' this seems to break it. i'm whittering again! please help!

bogglemaster89

Comment on perl to write to more than one table in a database at the same time (or call anotherscript to do one) Download Code

Replies are listed 'Best First'.
Re: perl to write to more than one table in a database at the same time (or call anotherscript to do one) by Corion (Patriarch) on Mar 16, 2010 at 20:31 UTC
If you really want to insert a row into two tables "at once", you will (at least with MySQL) need a transaction. See DBI for how to start/commit a transaction. As an aside, you should really use DBI placeholders (see DBI, again), instead of interpolating Perl variables into your SQL statements. That way, you avoid needing to take special care of users like "O'Hara" and Bobby Tables.	[reply]
Re: perl to write to more than one table in a database at the same time (or call anotherscript to do one) by Cody Fendant (Hermit) on Mar 16, 2010 at 22:30 UTC
In your code, you only execute $sth, you don't execute $sth2.	[reply]
Re: perl to write to more than one table in a database at the same time (or call anotherscript to do one) by bogglemaster89 (Novice) on Mar 16, 2010 at 21:05 UTC
thanks for your help, I have now got the following to work use DBI; use CGI; $dbh = DBI->connect('dbi:mysql:Database','root','password') or die "Connection Error: $DBI::errstr\n"; #my $sth = $dbh->prepare("insert into logintbl(username, password) val +ues('jones2010','password') "); #my $sth2 = $dbh->prepare("insert into studenttbl(fname,lname,grade,DO +B,email,tel_no,membertype,username) VALUES ('jamie','batty','7thkyu', +'1988/07/21','battydave&#64hotmail.com','012345234543','monthly pay', +'jones2010')"); #$sth->execute(); #$dbh->disconnect; $dbh->{AutoCommit} = 0; # disable auto-commit $dbh->do ("insert into logintbl(username, password) values('jones2100' +,'password')"); $dbh->do ("insert into studenttbl(fname,lname,grade,DOB,email,tel_no,m +embertype,username) VALUES ('jamie','batty','7thkyu','1988/07/21','ba +ttydave&#64hotmail.com','01234523454','monthly pay','jones2010')"); $dbh->commit(); [download] I shull now focus on gettng the O'NEIL problem fixed....tomorrow lol! Thanks again! bogglemaster89.	[reply] [d/l]
Re^2: perl to write to more than one table in a database at the same time (or call anotherscript to do one) by Corion (Patriarch) on Mar 16, 2010 at 21:13 UTC
Use DBI placeholders. It's as easy as the following: ... $dbh->{AutoCommit} = 0; # disable auto-commit my $sth_insert_login = $dbh->prepare(<<'SQL'); insert into logintbl( username, password) values(?, ?) SQL my $sth_insert_student = $dbh->prepare(<<'SQL'); insert into studenttbl( fname,lname,grade,DOB,email,tel_no,membertype,username) values (?, ?, ?, ?, ?, ?, ?, ?) SQL #$dbh->do ("insert into logintbl(username, password) values('jones2100 +','password')"); $sth_insert_login->execute('jones2100', 'password'); #$dbh->do ("insert into studenttbl(fname,lname,grade,DOB,email,tel_no, +membertype,username) VALUES ('jamie','batty','7thkyu','1988/07/21','b +attydave&#64hotmail.com','01234523454','monthly pay','jones2010')"); $sth_insert_student->execute('jamie','batty','7thkyu','1988/07/21','ba +ttydave@hotmail.com','01234523454','monthly pay','jones2010'); $dbh->commit(); [download]	[reply] [d/l]
Re: perl to write to more than one table in a database at the same time (or call anotherscript to do one) by bogglemaster89 (Novice) on Mar 16, 2010 at 20:30 UTC
I managed to get it to work on one just now, but im unsure how to make this work for two please help!	[reply]