+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Database Transactions With Multiple Active Records Calls In Separate PHP Functions (/showthread.php?tid=33631)
Database Transactions With Multiple Active Records Calls In Separate PHP Functions - El Forum - 09-02-2010
[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?
Database Transactions With Multiple Active Records Calls In Separate PHP Functions - El Forum - 09-02-2010
[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.
Database Transactions With Multiple Active Records Calls In Separate PHP Functions - El Forum - 09-02-2010
[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.
Database Transactions With Multiple Active Records Calls In Separate PHP Functions - El Forum - 09-02-2010
[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:
Quote: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.
Database Transactions With Multiple Active Records Calls In Separate PHP Functions - El Forum - 09-02-2010
[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.
Database Transactions With Multiple Active Records Calls In Separate PHP Functions - El Forum - 09-02-2010
[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?
Database Transactions With Multiple Active Records Calls In Separate PHP Functions - El Forum - 09-02-2010
[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();
Database Transactions With Multiple Active Records Calls In Separate PHP Functions - El Forum - 09-02-2010
[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
Database Transactions With Multiple Active Records Calls In Separate PHP Functions - El Forum - 09-02-2010
[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.
Database Transactions With Multiple Active Records Calls In Separate PHP Functions - El Forum - 09-02-2010
[eluser]asciiCode[/eluser]
Great thanks for your help, I will figure out the InnoDB problem with my service provider!