| Bastien Koert 2007-01-18, 6:58 pm |
| 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.a...94d45&mkt=en-ca
|