setting SQL variable

setting SQL variable

am 18.01.2007 10:37:04 von marga

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
";
}
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.
";
}
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

RE: setting SQL variable

am 18.01.2007 14:59:45 von Bastien Koert

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
>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
";
>}
>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.
";
> }
> 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-90 e6-4d28-9219-5d7207d94d45&mkt=en-ca

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: setting SQL variable

am 18.01.2007 15:04:13 von Nick Stinemates

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):

$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 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
";
> }
> 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.
";
> }
> 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

Re: setting SQL variable

am 18.01.2007 21:38:01 von Bruce Cowin

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 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=3DInnoDB;

CREATE TABLE `table2` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
) ENGINE=3DInnoDB;

CREATE TABLE `table3` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
) ENGINE=3DInnoDB;

CREATE TABLE `table4` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
) ENGINE=3DInnoDB;


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=3DInnoDB;

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=3DInnoDB;

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=3D0; BEGIN;");
mysql_query("INSERT INTO table1 (test) VALUES ('test');
if (mysql_errno()) {
$error =3D 1;
echo "ERROR__1
";
}
else {
mysql_query("SET @id_last_table1=3DLAST_INSERT_ID();");
$query1 =3D "INSERT INTO rel_1 (id_table2, id_table1) VALUES
(".$_POST['idtable2'].", @id_last_table1 );";
$query2 =3D "INSERT INTO rel_2 (id_table4, id_table1) VALUES
(".$_POST['idtable4'].", @id_last_table1 );";

$query =3D $query1.$query2;
mysql_query($query);
if (mysql_errno() || $error==1) {
mysql_query("ROLLBACK");
echo "ERROR_2.
";
}
else { mysql_query("COMMIT"); }






--=20


Marga Vilalta
marga at ayuken dot com

Hov ghajbe'bogh ram rur pegh ghajbe'bogh jaj

--=20
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php=20

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: setting SQL variable

am 19.01.2007 10:41:47 von Oskar

marga wrote:

> 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.
wrong, it is possible to use mysql_insert_id() during the transaction.

the code should look like this:

mysql_query("start transaction");
if (mysql_query(insert into first table)) {
$id=mysql_insert_id();
if (!mysql_query(subsequent inserts)) $error=1;
}
else $error=1;
mysql_query((isset($error)) ? "rollback" : "commit");

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: setting SQL variable

am 19.01.2007 10:58:17 von marga

You are the right.
Is possible use mysql_insert_id() and it need not stack the queries and
confirm these one by one.

Thanks a lot!!


OKi98 wrote:
> marga wrote:
>
>> 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.
> wrong, it is possible to use mysql_insert_id() during the transaction.
>
> the code should look like this:
>
> mysql_query("start transaction");
> if (mysql_query(insert into first table)) {
> $id=mysql_insert_id();
> if (!mysql_query(subsequent inserts)) $error=1;
> }
> else $error=1;
> mysql_query((isset($error)) ? "rollback" : "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