1

I want to use category listing in subcategory controller. I am trying following methods to get categories which status are 1.

$this->loadModel('Categories');
$query = $this->Categories->find();
$query->where(['cat_status' => 1]);

This is returning following output

'SELECT Categories.id AS Categories__id, Categories.cat_name AS Categories__cat_name, Categories.cat_status AS Categories__cat_status FROM categories Categories WHERE cat_status = :c0'

I don't know where it is getting:c0. Then I used another code which is giving all the list but where clause is not working.

$this->loadModel('Categories');
$categories = $this->Categories->find('all',[
                                            'where' => (['cat_status'=>1]),
                                            'limit' => 5,
                                            'order' => 'Categories.id DESC'
    ]);
debug($categories);

The output is

SELECT Categories.id AS Categories__id, Categories.cat_name AS Categories__cat_name, Categories.cat_status AS Categories__cat_status FROM categories Categories ORDER BY Categories.id DESC LIMIT 5

Please suggest a suitable way.

Moumita Paul
  • 25
  • 7
  • Does it not make more sense to put the `->where()` before running the `->find()` – RiggsFolly Jul 03 '17 at 12:22
  • "_doesn't work_" is not a proper problem description! Even if the problem might be obvious for people that know the CakePHP internals, please always be as specific as possible as to what _exactly_ happens, and what _exactly_ you'd expect to happen instead. Show the data that you are working with, the context, the code required to reproduce the problem, your debugging attempts, and possible errors. That being said, what you're showing there is a totally valid SQL query with a placeholder for a bound value. – ndm Jul 03 '17 at 12:34
  • @RiggsFolly In CakePHP 3.x, `find()` returns a query (builder) object. – ndm Jul 03 '17 at 12:35
  • @ndm Ah that explains the `:c0` which would be used as a Bound Parameter – RiggsFolly Jul 03 '17 at 12:42

2 Answers2

0

Pass conditions with query like below example.

Example 

$categories = $this->Categories->find('all',['conditions'=>['cat_status'=>1]])->limit(5)->order(['Categories.id'=> 'DESC']);
shubham715
  • 3,324
  • 1
  • 17
  • 27
  • it's working. I get the result but why the query is SELECT Categories.id AS `Categories__id`, Categories.cat_name AS `Categories__cat_name`, Categories.cat_status AS `Categories__cat_status` FROM categories Categories WHERE cat_status = :c0 ORDER BY Categories.id DESC LIMIT 5 Can you explain what is :c0 ? – Moumita Paul Jul 03 '17 at 12:30
  • 1
    @shubham715 Its not done to allow escaping the input. It is done to allow for Bound parameters. Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) – RiggsFolly Jul 03 '17 at 12:38
  • 1
    you can read in details here https://stackoverflow.com/questions/18426172/what-does-bind-param-accomplish – shubham715 Jul 03 '17 at 12:46
0

You need to add ->first() to your where clause, like so :

$query->where(['cat_status' => 1])->first();
Ansu Kumari
  • 37
  • 6