CakePHP 2.1.x - Run a query without any models in AppController

Question

I am trying to run a query in AppController on a table that has no Model associated with it. I don't want to use a Model cause this query would fire on every request and I guess using a Model would make it a bit slower.

I found out in one forum that this can be achieved with the following code in CakePHP 1.3

$db = ConnectionManager::getInstance();
$conn = $db->getDataSource('default');
$conn->rawQuery($some_sql);

But this is not working in CakePHP 2.1.3. Any help would be appreciated. Thanks :)

Solution 1

The getDataSource() method is static in CakePHP 2.x, so you should be able to use:

$db = ConnectionManager::getDataSource('default');
$db->rawQuery($some_sql);

Solution 2

you should run this way

    App::uses('ConnectionManager', 'Model'); 
    $db = ConnectionManager::getDataSource('default');
    if (!$db->isConnected()) {
       $this->Session->setFlash(__('Could not connect to database.'), 'default',            array('class' => 'error'));
    } else {
        $db->rawQuery($some_sql);
    }

Solution 3

rawQuery will not return data, use $db->query instead.

$db = ConnectionManager::getDataSource('default');
$data = $db->query($some_sql);

Comments

[deck john]

i did the same thing bit shows me the error message which is mentioned below. Only variables should be assigned by reference. can you tell me that what changes still i have to do ?

[Nico]

You may need App::uses('ConnectionManager', 'Model'); as well

[Ray]

Any way to do this with prepared statements?

[Tomas Gonzalez]

I tried this using CakePHP 2.6.3 and it only returned an object with the query, but not the result. Instead, I used $model->query('SELECT * FROM table'); That gave me the result I expected.

[Dharmesh patel]

second line will be $db->fetchAll($sql); for getting result directly.