setting SQL variable
| From: | marga | Date: | Thu, 18 Jan 2007 09:37:04 +0000 |
| Subject: | setting SQL variable | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-42954@lists.php.net to get a copy of this message | ||
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