Re: setting SQL variable

To accomplish this in the past, it is assumed that the last entered ID is the highest (since it auto increments) To solve this simply, I would use (truncated a bit): <?php $insertSQL = "insert into table1(test) values(1)"; $idSQL = "select top(id) from table1"; $someVar = mysql_result(mysql_fetch_array($idSQL)); $theID = $someVar[0]; // contains the ID to use in the subsequent transactions ?> Good luck, let me know if you need any more assistance. - Nick Stinemates On Fri, Jan 19, 2007 at 09:38:01AM +1300, Bruce Cowin wrote: > What about doing all of that in a stored proc and handle the transaction in there? I've > never done that with MySQL (only SQL Server) but I'm assuming it's possible. > > > Regards, > > Bruce > > >>> marga <marga@ayuken.com> 18/01/2007 10:37:04 p.m. >>> > 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 > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >