Transaction support

[superflyc]

Sorry if this is a boneheaded question, but I'm also troubleshooting some other very random behavior and thought I'd check here after a search didn't get me anything.

Does PowWeb support transactions for MySQL? If so, why is this syntax resulting in a commit?

@mysql_query("BEGIN");
@mysql_query("insert into foo (bar) values ('foobar')");
@mysql_query("ROLLBACK");

Am I missing something stupidly obvious?

My end goal is something like this:
table ALBUMS
table SONGS

insert a record into ALBUMS, then use last_insert_id from that query as my foreign key to insert associated recs in SONGS.

Putting all of these in a transaction should ensure that last_insert_id gets the correct id.

Or am I making all of this far too complicated?

thanks!

[satis]

I'm not sure why you'd have a problem without using transactions. I do similar things and I just do my insert, pull the id into a variable, then use that variable for dependent tables. I've never run into an issue where I lost sync. So basically.

$this->executeQuery($insert);
$inserted_id = $this->getInsertedId();
$this->executeQuery('INSERT INTO table1 (whatever) values (' .$inserted_id .')');
$this->executeQuery('INSERT INTO table2 (whatever) values (' .$inserted_id .')');

etc. Semi-pseudocode above but I hope it's clear enough.

[superflyc]

Oh, that will more than likely work in most cases. This is just the retentive part of me that thinks, "what will happen if an insert fires in another request or thread?" and thinks it's a best practice to put these in a transaction.

Thanks for the sample, though. :)

[satis]

well, I believe when you do the insert request, it creates an object of the SQL transaction. Then when you do the request for the inserted id, it accesses that same SQL transaction object, so there's no chance of getting the wrong id. It doesn't for instance do a (select max(id) FROM table).

At least, that's my understanding of it. I'm not an expert so I could be wrong. I also use ADODB to abstract db operations in which the entire db interaction really it object-oriented.

[superflyc]

good point - I will do some digging in the documentation.

Thanks for the info about ADODB. I went to the site and it looks pretty interesting. I'm more of a Java/Grails/Groovy guy who inherited an OSS PHP site to maintain. It's an older codebase and has its own db abstraction layer. I'd like to learn more PHP - PEAR? some other frameworks, too - but there's only so many hours in the day.

Thanks again for the suggestions! :)

[satis]

I hear you. I'm a PHP/javascript/C# guy... been wanting to learn Java, Ruby on Rails, xaml and other more avant-garde stuff but just don't have time. Anyway, good luck, feel free to drop a post if you need help with anything.