How to create custom MySQL queries in CakePHP?

Question

I am trying to create my own MySQL queries in Cakephp.

This is my LocationsController.php :

 <?php App::uses('Location', 'Model'); class LocationsController extends AppController { public $helpers = array('Html', 'Form'); function index() { $this->loadModel("Location"); $this->Location->get(); } } 

This is my LocationModel.php :

 <?php App::uses('AppModel', 'Model'); class LocationModel extends Model { public $name = 'Location'; public function get() { $this->Location->query("SELECT * FROM locations;"); } } 

As you can see, I'm just trying to execute a simple query, but it does not work. I get this error:

 Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'get' at line 1 

When I use one of the magic methods, for example find ("all"), it works ...

Do you see what the problem is? I really can't, and I'm just trying to do a simple task!

Answer 1

The class name of your location model should be Location , not LocationModel .

Because of this, CakePHP will generate a “generic” model for the Locations database table and use this model instead of your own model. Since this generic model does not have a get() method, it will execute get as an SQL statement, causing an error

Also, inside the model you should not use $this->Location->query(); , but just $this->query();

Answer 2

The location controller must be:

 <?php App::uses('Location', 'Model'); // Y do u need this? class LocationsController extends AppController { public $helpers = array('Html', 'Form'); function index() { $this->loadModel("Location"); $this->LocationModel->getLocations(); // I will strongly discourage using get() } } 

The location model should be:

 <?php App::uses('AppModel', 'Model'); class LocationModel extends Model { public $name = 'Location'; public function getLocations() // Try to avoid naming a function as get() { /** Choose either of 2 lines below **/ return $this->query("SELECT * FROM locations;"); // if table name is `locations` return $this->query("SELECT * FROM Location;"); // if table name is `Location` since your public name is `Location` } }