Re: setting SQL variable

["Bruce Cowin" <bdc@xxxxxxxxxxxxxxx>]

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@xxxxxxxxxx> 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