Insering into 2 tables?

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

dear monks, how does one insert data into two different MySQL tables? Is that done as two different SQL statements? I'm trying

my $SQL1    = "INSERT INTO property (address_1, address_2, county, cit
+y, zip, description, status, bedroom) VALUES ('$address_1', '$address
+_2', '$county', '$city', '$zip', '$description', '$status', '$bedroom
+')";

my $SQL2    = "INSERT INTO property2 (address_1, address_2, county, ci
+ty, zip, description, status, bedroom) VALUES ('$address_1', '$addres
+s_2', '$county', '$city', '$zip', '$description', '$status', '$bedroo
+m')";
<br>
[download]

and then my $sth= $dbh->prepare($SQL1,SQL2) or die $dbh->errstr; all that seems to do is execute only the first $! any help welcome
cheers
r_mehmed

Comment on Insering into 2 tables? Select or Download Code

Replies are listed 'Best First'.
Re: Insering into 2 tables? by Zaxo (Archbishop) on Jan 23, 2003 at 20:57 UTC
Kanji's diagnosis is correct, but his solution doesn't keep your executed statement handles around to be read. How about this? `my @SQLs = map { "INSERT INTO $_ (address_1, address_2, county, city, zip, description, status, bedroom) VALUES (?, ?, ?, ?, ?, ?, ?, ?)" } qw( property property2 ); my @sths = map {$dbh->prepare($_)} @SQLs;` [download] You'll probably want to execute both these on a common set of data, which I'll just call @data without worrying how it got there - `my @results = map {$_->execute(@data)} @sths;` After Compline, Zaxo	[reply] [d/l] [select]
Re: Insering into 2 tables? by Kanji (Parson) on Jan 23, 2003 at 20:00 UTC
`prepare()` only works on one statement at a time, so if you have more than one query you need to `prepare()` each individually. However, using a loop can save you some code repetition... `foreach my $sql ( $SQL1, $SQL2 ) { my $sth = $dbh->prepare($sql) or die $dbh->errstr; $sth->execute; }` [download] --k.	[reply] [d/l] [select]
Re: Insering into 2 tables? by hardburn (Abbot) on Jan 23, 2003 at 20:00 UTC
This doesn't solve the problem you are asked, but please, use placeholders: `my $SQL1 = "INSERT INTO property (address_1, address_2, county, cit +y, zip, description, status, bedroom) VALUES (?, ?, ?, ?, ?, ?, ?, ?) +"; my $sth = $dbh->prepare($SQL1) or die $dbh->errstr; $sth->execute($address_1, $address_2, $county, $city, $zip, $descripti +on, $status, $bedroom); $sth->finish();` [download] Unfortunately, placeholders can't be used on table names on most databases, or else the solution to your problem would be easy.	[reply] [d/l]
Re: Insering into 2 tables? by Sifmole (Chaplain) on Jan 23, 2003 at 20:41 UTC
If you put both statements in a single string, seperated by a semi-colon this should work. I am not saying this is a good way of doing it, but for informations sake: `my $SQL1 = "INSERT INTO property (address_1, address_2, county, cit +y, zip, description, status, bedroom) VALUES ('$address_1', '$address +_2', '$county', '$city', '$zip', '$description', '$status', '$bedroom +'); INSERT INTO property2 (address_1, address_2, county, city, zip, d +escription, status, bedroom) VALUES ('$address_1', '$address_2', '$co +unty', '$city', '$zip', '$description', '$status', '$bedroom')"; my $sth= $dbh->prepare($SQL1) or die $dbh->errstr;` [download]	[reply] [d/l]