[Resolved] [Resolved] Transactions over multiple php pages. How?

relnah

Ok, the problem is this:

I need to start a transaction in MySQL with InnoDB-tables,keep it "open" while loading new php-pages and finally doing a commit or rollback.

How do I make this possible?

I did make it happen by on the first page doing a normal mysql_connect etc. and then not doing a connect on the following pages. I did however need to do a db_select (it seemed to loose what database when switching pahes but not he connection).

Now when I tried to put it live I couldn't merely do a select on the coming pages because the mysql-server doesn't allow any user to connect to localhost without a password on the live machine. (this is possible on my desktop since it's not used for other than development).

Got any suggestions??

Regards,
Relnah

tomhath

I'd be surprised if you can get it to work, it doesn't seem to me that the connection would persist. Can you pass the data from page to page until you have everything, then do the entire transaction?

relnah

The user enters a date and a store -> klicks continue (page reload)

User adds product (page reload), and keeps adding products until he/she klicks done or abort.

Naive as I was I though this would be simple with transactions... just ope the transaction right before the store is entered and then do commit or rollback when done/sbort is pressed.

I could store the data in a temporary table I guess. Just thought the transaction solution would be more effective... seemes I was wrong, unless there is a way to get it to work.

Drakla

Hi Relnah.

I don't think you've sussed how transactions work properly by the sound of it. You don't start one for the whole time somebody is browsing the site - that would in fact be catastrophic, as any table rows that you lock will be unable to be updated for the entire time they're on there.

Depending on how complicated your shopping system needs to be, i.e. do you need it to manage your stock levels, and need to put them back after orders aren't paid for or problems happen, you're going to need an order table that you add to during the current buying session, and add the user or session ID to.

It's too complicated to go into in a brief post as there are issues you might or might not have to address.

superwormy

Yeah, you can't keep connections live between page requests. You didn't do it before either. PHP will automatically try to connect on its own if you call mysql_query() or ...select_db() without having a connection open. That was what was happening to you before, you weren't actually keeping the connection open.

Sxooter

What you need to do, likely, is something akin to "checking out/checking in" data. I.e. make another table that has references for the rows that are "locked" and then run a vacuum type process every few hours to drop stale locks (i.e. ones that are more than x minutes old and assumed abandoned.)

Then, in your code you check to see if the data is "locked" or "checked out" before allowing the user to proceed.

transactions across pages are, as mentioned above, problematic.

relnah

Thanx for the replies.
I'm going with creating a "temporary" table to store the data, and then putting it into the correct tables on "done".
if done is not pressed or if "Abort" is pressed I'm just gonna wipe the table... with the users ID as prefference offcourse.

Again thanx for the answers.