How to execute custom query in CakePHP

Question

I'm currently trying to execute custom queries in CakePHP framework, meaning instead of using CakePHP syntax, I'd like execute normal SQL query like SELECT * FROM post ORDER BY id desc.

I cannot figure out how to do it. I read several answers to the similar questions, but it still doesn't work.

As far as I understand I should put function like:

public function testx()
{
    $sql = "SELECT * FROM posts WORDER by id desc";
    return $this->query($sql);
}

to file Post in directory Model and then put this code:

$result = $this->Post->testx();

to index function in PostsController in Controller directory.

I still can't figure out how to print out the data in View/Posts/index.ctp.

Thanks in advance for any answer.

Solution 1

public function index(){
    $this->loadModel('Post'); //or you can load it in beforeFilter()
    $data=$this->Post->query('SELECT * FROM posts ORDER by id desc');
    $this->set('data',$data);
}

In your view file index.ctp. Write Below Code

<?php
     if($data) {
          echo "<pre>";
          print_r($data);
     } else {
          echo 'no data found';
     }
?>

Solution 2

If you want to execute a delete (or any other) query without a model then you should try

$db = ConnectionManager::getDataSource('default');
$db->rawQuery("DELETE FROM table WHERE id=5");

Solution 3

You can use connection manager to run custom SQL queries. Follow this article from cake php documentation to use connection manager: https://book.cakephp.org/3.0/en/orm/database-basics.html

use Cake\Datasource\ConnectionManager;
$connection = ConnectionManager::get('default');
$results = $connection->execute('SELECT * FROM articles')->fetchAll('assoc');

After this to view results in your view (index.ctp) you have to set the result variable

$this->set('result',$result);

Now you can view the data from your database in index.ctp using the result variable.

Solution 4

App::import("Model", "ModelName");  
    $model = new ModelName();  
    $query = "Select * from tablename";
    $data = $model->query($query);

Solution 5

$this->Post->query("SELECT * FROM posts ORDER by id desc");

View more solutions

Comments

[Celebris]

I tried that but instead off printing out the variable I get "Notice (8): Undefined index: Post [APP/View/Posts/index.ctp, line 17]". I use $post['Post']['id'] to print out the variable. But it prints out that same error message for all the rows in the table, so the SQL query is at least partly working.

[cornelb]

did you use debug($posts) ? It might be $post['post'].

[Ziad Akiki]

What about queries with parameters?

[K.Raj.]

@ZiadAkiki Could you please elaborate on it? So, I can help.

[Ziad Akiki]

does $this->Post->query('SELECT * FROM posts ORDER by id desc WHERE id = ?', $id); for example works? In other words, if I need to pass a parameter in the query securely.

[K.Raj.]

@ZiadAkiki Yes. You can pass the parameters. I believe you should go with CakePHP 3 ORM rather than a Query Builder to secure the query. Hope this helps.