CODEWITHSUNDEEP
cakephp
Rápli András
Rápli András

Reputation: 3923

How to make PDO queries in cakePHP?

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.

Upvotes: 1

Views: 2768

Answers (2)

AndreyP
AndreyP

Reputation: 2678

You can access the PDO object for your database connection from anywhere. (Use 'default' DB profile or replace with a different DB profile.)

CakePHP 2 example:

App::uses('ConnectionManager', 'Model');
$Connection  = ConnectionManager::getDataSource('default')
$PDO         = $Connection->getConnection();

CakePHP 3 example:

/** @var \Cake\Database\Connection $Connection */
$Connection = ConnectionManager::get('default');
if (!$Connection->isConnected())
    $Connection->connect();

$PDO = $Connection->getDriver()->getConnection();

# To access PDO inside `Cake\ORM\Table` objects 
# you can use `$Connection = $this->getConnection();`

Upvotes: 0

BadHorsie
BadHorsie

Reputation: 14554

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

Upvotes: 5

Related Questions