I think the issue might be that you cannot stack the queries in mysql....you
can't run both queries separated by a semi-colon. You will likely need to
loop thru the queries and execute them one at a time
Bastien
From: marga <marga@xxxxxxxxxx>
To: php-db@xxxxxxxxxxxxx
Subject: setting SQL variable
Date: Thu, 18 Jan 2007 10:37:04 +0100
Hi,
I create a sql variable with php code. Is possible that it didn't works?
In order to maintain the integrity I open a transaction to do all
inserts. First insert a row in table1. I need these Insert ID
to make the follow Inserts in the relations tables. Is not possible to use
mysql_insert_id() and assing it in a php variable, because
mysql_insert_id() is executed after the COMMIT, I think.
I try to set a sql variable, do "echo" of the queries and paste the SQL
code in SQL console and works fine. But I have a SQL error if I execute
the php code. The mysql_error returns "" and mysl_errno return 0.
How to obtain the insert id of table1 and use it in the rest of inserts
into the transaction?
Thanks for advance!
I have a structure like this:
CREATE TABLE `table1` (
`id` int(11) NOT NULL auto_increment,
`test` int(11),
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
CREATE TABLE `table2` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
CREATE TABLE `table3` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
CREATE TABLE `table4` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
CREATE TABLE `rel_1` (
`id` int(11) NOT NULL auto_increment,
`id_table1` int(11) NOT NULL,
`id_table2` int(11) NOT NULL,
`id_table3` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `id_table1` (`id_table1`),
KEY `id_table2` (`id_table2`),
KEY `id_table3` (`id_table3`),
) ENGINE=InnoDB;
ALTER TABLE `rel_1`
ADD CONSTRAINT `rel_1_ibfk_1` FOREIGN KEY (`id_table1`) REFERENCES
`table1` (`id`),
ADD CONSTRAINT `rel_1_ibfk_2` FOREIGN KEY (`id_table2`) REFERENCES
`table2` (`id`),
ADD CONSTRAINT `rel_1_ibfk_3` FOREIGN KEY (`id_table3`) REFERENCES
`table3` (`id`);
CREATE TABLE `rel_2` (
`id` int(11) NOT NULL auto_increment,
`id_table1` int(11) NOT NULL,
`id_table4` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `id_table1` (`id_table1`),
KEY `id_table4` (`id_table4`)
) ENGINE=InnoDB;
ALTER TABLE `rel_2`
ADD CONSTRAINT `rel_2` FOREIGN KEY (`id_table4`) REFERENCES `table4`
(`id`),
ADD CONSTRAINT `rel_2` FOREIGN KEY (`id_table1`) REFERENCES `table1`
(`id`);
Part of code (simplified):
mysql_query("SET AUTOCOMMIT=0; BEGIN;");
mysql_query("INSERT INTO table1 (test) VALUES ('test');
if (mysql_errno()) {
$error = 1;
echo "ERROR__1<br>";
}
else {
mysql_query("SET @id_last_table1=LAST_INSERT_ID();");
$query1 = "INSERT INTO rel_1 (id_table2, id_table1) VALUES
(".$_POST['idtable2'].", @id_last_table1 );";
$query2 = "INSERT INTO rel_2 (id_table4, id_table1) VALUES
(".$_POST['idtable4'].", @id_last_table1 );";
$query = $query1.$query2;
mysql_query($query);
if (mysql_errno() || $error==1) {
mysql_query("ROLLBACK");
echo "ERROR_2.<br>";
}
else { mysql_query("COMMIT"); }
--
Marga Vilalta
marga at ayuken dot com
Hov ghajbe'bogh ram rur pegh ghajbe'bogh jaj
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
_________________________________________________________________
http://ideas.live.com/programpage.aspx?versionid=b2456790-90e6-4d28-9219-5d7207d94d45&mkt=en-ca
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
