Database Transactions With Multiple Active Records Calls In Separate PHP Functions

[eluser]asciiCode[/eluser]
I have separate php functions to "create a user" and "create contact information" for a user. Each separate function contains the Active Record db code.

However if I want to use the two functions together, is there any way to do transactions?

The traditional example is:

$this->db->trans_start();
$this->db->query('first sql query');
$this->db->query('second sql query');
$this->db->query('third sql query');
$this->db->trans_complete();

However, I want to do something to this effect:

$this->db->trans_start();
$this->CreateUser();
$this->CreateContactInformation();
$this->db->trans_complete();

Is there anyway to do this?

[eluser]bretticus[/eluser]
Like the manual says, you can run as many queries as you like as long as you run trans_begin() first and trans_complete() when done (unless you roll back of course.) Your create and contact methods are using AR I assume, so I see no problem calling those methods in a wrapper method as you have demonstrated. Just make sure your tables are InnoDB and not MyISAM.

[eluser]asciiCode[/eluser]
Thanks for the help! I am using MySql and when I try to add a table it does not give InnoDB as an option only MyISAM and a few others.

[eluser]bretticus[/eluser]
Really, innoDB is quite standard for MySQL. Is this an old database server? If so, is it one you can update? Shared hosting?

What type of MySQL client are you using? phpMyAdmin, MySQL Administrator?

From manual:

In MySQL, you'll need to be running InnoDB or BDB table types rather than the more common MyISAM. Most other database platforms support transactions natively.

[eluser]asciiCode[/eluser]
I have a VPS. I am creating the database tables with MySQL workbench. It tries to create InnoDB tables. However when I check the tables with PHPMyAdmin, it shows the tables are MyISAM.

[eluser]bretticus[/eluser]
[quote author="asciiCode" date="1283465359"]I have a VPS. I am creating the database tables with MySQL workbench. It tries to create InnoDB tables. However when I check the tables with PHPMyAdmin, it shows the tables are MyISAM.[/quote]

Can you change them to innoDB in phpMyAdmin?

[eluser]asciiCode[/eluser]
Assuming I get InnoDB setup ... just to be clear it is ok to have php logic between the trans_start and trans_compete calls? Something like this.

$this->db->trans_start();

if (not $this->UserExists())
$this->CreateUser();

$this->UpdateContactInformation();

$this->db->trans_complete();

[eluser]asciiCode[/eluser]
No I cannot change it to InnoDB in phpMyAdmin , and when I go to create a new table in phpMyAdmin it will not allow me to create a InnoDB table

[eluser]bretticus[/eluser]
[quote author="asciiCode" date="1283465819"]Assuming I get InnoDB setup ... just to be clear it is ok to have php logic between the trans_start and trans_compete calls? [/quote]

Absolutely. when you run trans_start, later, trans_commit will automatically roll back if any of your queries fail. What's in between shouldn't affect it because transactions only concern themselves with changed data for unsuccessful transactions.

[eluser]asciiCode[/eluser]
Great thanks for your help, I will figure out the InnoDB problem with my service provider!