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@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
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
