Insert SQL query into controller in cakephp

Question

I'm using cake php version 2.4.1

I want change this SQL query into PHP code for my controller in cakePHP

SELECT id,name 
FROM providers 
WHERE id not in 
    (SELECT c.provider_id 
    FROM token_maps a 
    INNER JOIN token_map_groups b ON b.token_map_id = a.id 
    INNER JOIN token_map_group_providers c on c.token_map_group_id=b.id 
    LEFT JOIN providers p on p.id=c.provider_id 
    WHERE a.id=2
    )

I can't use $this->Provider->query('sql query'); because it will give me false result different from when I execute the query in mysql.

can anyone help me?

If you in model Provider.php, use: $this->query('sql query');. If you in other model, write your way is ok. If your model class don't load, try $Provider_Model = ClassRegistry::init('Provider'); $result_array = $Provider_Model->query(...);. — Vy Do
– Vy Do, Commented Apr 24, 2015 at 11:34

Community · Accepted Answer · 2017-05-23 11:58:17Z

2

At first I told you that your approach is not recommended in any MVC framework. You need to declare a method in Provider model, then you can write cakePHP custom query.

Look at How to create custom MySQL queries in CakePHP?

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Apr 21, 2015 at 5:35

Supravat M

2,5722 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sudhir · Accepted Answer · 2015-04-21 12:09:27Z

-1

If you query gives the exact answer in PHPMYADMIN then make a query like this:

$this->loadModel('Provider');

$query = "SELECT id,name FROM providers WHERE id not in (SELECT c.provider_id  FROM token_maps a 
INNER JOIN token_map_groups b ON b.token_map_id = a.id 
INNER JOIN token_map_group_providers c on c.token_map_group_id = b.id 
LEFT JOIN providers p on p.id=c.provider_id 
WHERE a.id=2
)";

$this->Provider->query($query);

answered Apr 21, 2015 at 12:09

Sudhir

83312 silver badges33 bronze badges

3 Comments

AD7six Over a year ago

It's not recommended (on the scale of not-recommended - like just one step below "completely wrong") to put sql in a controller - put it in a model (if raw sql is actually necessary) and call the model method.

Sudhir Over a year ago

In model by default it will call every where. If you need some place then use in controller

AD7six Over a year ago

That's not how models are supposed to be used. raw-sql is not "parameters"

Collectives™ on Stack Overflow

Insert SQL query into controller in cakephp

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related