[SOLVED] PDO transaction on tables ith foreign keys...

[ToonMariner]

Think I may be taking the wrong approach to this but here goes...

 

I have a PDO transaction - first query insert a new user into the database - the second query inserts profile data about that user into a profile table.

 

the user_id on the location table is a foreign key to the userID on the user table.

 

here's the code

 

<?php
	$userQry	=	"
					INSERT INTO
						`users`
					(
						`username`,
						`local_email`,
						`domain_email`,
						`password`
					)
					VALUES
					(
						:username,
						:localemail,
						:domain,
						SHA1(:password)
					)
					";
	$addressQry	=	"
					INSERT INTO
						`user_location`
					(
						`user_id`,
						`city_id`,
						`user_address`
					)
					VALUES
					(
						:userid,
						:city,
						:address
					)
					";

	$this->beginTransaction();

	$user	=	$this->prepare ( $userQry );
	$user->bindValue ( ':username' , $_POST['regusername'] , PDO::PARAM_STR );
	$user->bindValue ( ':localemail' , $email[0] , PDO::PARAM_STR );
	$user->bindValue ( ':domain' , $domain_id , PDO::PARAM_INT);
	$user->bindValue ( ':password' , PW_SALT . $_POST['reguserpassword'] , PDO::PARAM_STR );
	$insertUser	=	$user->execute();

	$locale	=	$this->prepare ( $addressQry );
	$locale->bindValue ( ':userid' , $this->lastInsertId () , PDO::PARAM_INT );
	$locale->bindValue ( ':city' , $_POST['regcity'] , PDO::PARAM_INT );
	$locale->bindValue ( ':address' , $_POST['regaddress'] , PDO::PARAM_STR );
	$insertLocale	=	$locale->execute();
	var_dump ($locale->errorInfo () );
	/*if	(
		$insertLocale->rowCount() > 0
		)
	{
		$this->commit ();
		return true;
	}*/
	$this->rollBack ();
	return false;
?>

 

now you will see that I want to check if the last query run executed - know that won't work because the query was not committed so no rows will actually be affected.

 

The problem is the foreign key reference - as the user is not really inserted the second insert will fail as the foreign key referenced does not exist.

 

bit stuck on how to approach  this now - the foregin key constraint has to stay but I also want the transaction so I can roll back if anything goes wrong...

 

any help much appreciated

[ToonMariner]

Should I put this in mysql section?

 

gonna anyway

[Mchl]

Should I put this in mysql section?

 

gonna anyway

 

Do not double post. If you want your topic moved, contact moderators.

 

[edit]

 

And this should be solution to your problem:

http://www.php.net/manual/en/pdo.lastinsertid.php

[ToonMariner]

Yeah I bind the last insert id to the user_id in the second query BUT as the first insert has not been committed the second query fails on foreign key constrains - that user_id doesn't actually exist yet...

[Mchl]

Usually last insert ID IS available before commiting a transaction.

[ToonMariner]

Yes it is available.

 

The second insert fails however due to the fact that the lastinsert_id (which is correctly returned and assigned to the column having a foreign key reference to the first table) won't actually exist until the transactions are committed.

 

So when the second insert runs - it looks to see if that value exists in the field the foregin key references. As it doesn't actually exist yet the query cannot execute successfully.

[Mchl]

Rows that have been inserted within transaction, are visible during this transaction.

 

I don't know how about PDO, but this works in raw SQL

 

CREATE TABLE  `test`.`tr1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB;

CREATE TABLE  `test`.`tr2` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `FK` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK` (`FK`),
  CONSTRAINT `tr2_ibfk_1` FOREIGN KEY (`FK`) REFERENCES `tr1` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB;

START TRANSACTION;

INSERT INTO tr1 VALUES (null);

SET @lastID = LAST_INSERT_ID();

INSERT INTO tr2(fk) VALUES (@lastID);

COMMIT;

[ToonMariner]

So is there something special I have to do with PDO to make what I am trying to achieve work?

[Mchl]

I'm not sure, but it should work just as good.

 

Did you try doing same transaction using raw SQL? Does it succeed?

 

It might have something to do with how your tables are declared, and not with PDO at all.

 

[added]

 

A thought. Maybe it's not user_id constraint that fails, but city_id ?

[ToonMariner]

problem was one of the bindValues not returning an error - sorted now cheers mate