[FIXED] How to make PDO queries in cakePHP?

Issue

I mean there must be a PDO Object initialized somewhere, and I want to use that instead of creating a parallel connection.

By request, here's the story behind it: I'm making a registration form, which saves to two models, (Firms and Users) with a HABTM connection between them. CakePHP cannot work with HABTM in this form, so without ruining the model I had to do something like this:

    if ($this->request->is('post')) {

        $this->Firm->create();
        if ($this->Firm->saveAll($this->request->data)) {

            for($i=0; $i<count($this->request->data['User']); $i++){ //működik :)
                $this->User->create(); // ?

                $a = $this->request->data['User'][$i];

                $b = randomString(28);
                $a['password'] = $b;
                $a['plain_password'] = $b;
                if($this->User->save($a)){
                    $this->Session->setFlash(__('The firm and a user have been saved.')); // no good
                }
            }

            //$this->Session->setFlash(__('The firm has been saved.'));
             return $this->redirect(array('action' => 'index'));
        } else {
            $this->Session->setFlash(__('The firm could not be saved. Please, try again.'));
        }

I want to simply do a query, something like INSERT INTO firms_users (user_id,firm_id) VALUES ($u,$f), because this is what the model can't do for me because of HABTM.

It would be great if this was done using a transaction, but I guess, using cakePHP standards, it's simply not possible.

Solution

Accessing the Datasource

You can access the model's data source using Model::getDataSource().

See also: http://book.cakephp.org/2.0/en/models/datasources.html

Running Manual Queries

If you want to run a manual query without using Cake's built-in model query methods and relationships, you can do this:

$this->MyModel->query("INSERT INTO firms_users (user_id,firm_id) VALUES ($u, $f)");

Bear in mind the data here is not protected against SQL injection.

Transactions

I just saw your edit about wanting to run a transaction. Yes, you can do this with Cake. A model's datasource object has methods for this. You just need to begin the transaction on any one of the models which will be involved in your queries.

// Get the datasource
$datasource = $this->getDataSource();

// Start transaction
$datasource->begin();

// Rollback
$datasource->rollback();

// Commit
$datasource->commit();

http://book.cakephp.org/2.0/en/models/transactions.html

Answered By - BadHorsie