RE: [PHP-DB] setting SQL variable

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@ayuken.com> To: php-db@lists.php.net Subject: [PHP-DB] 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