PHP DB - Re: [PHP-DB] setting SQL variable

Author

Re: [PHP-DB] setting SQL variable

Bruce Cowin

2007-01-18, 6:58 pm

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
[color=darkred]
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<br>";
}
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=3D=3D1) {
mysql_query("ROLLBACK");
echo "ERROR_2.<br>";
}
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

Nick Stinemates

2007-01-18, 6:58 pm

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

Sponsored Links