CODEWITHSUNDEEP
phpsqlcakephp
iamjonesy
iamjonesy

Reputation: 25122

CakePHP: add variables to query in controller

I have a controller action that uses a sql query:

    $tag = $this->params['tag'];

    $this->set('projects', $this->Project->query('SELECT * FROM projects INNER JOIN projects_tags ON projects.id = projects_tags.project_id INNER JOIN tags on projects_tags.tag_id = tags.id WHERE tags.tag LIKE $tag'));

As you can see at the end I want to use a where clause with the $tag variable but I'm not sure how the syntax would go. As I'm getting the error 

Unknown column '$tag' in 'where clause'

Can someone steer me in the right direction?

Ta,

Jonesy

Upvotes: 0

Views: 4828

Answers (5)

Daniel Wright
Daniel Wright

Reputation: 4604

Should you want to use Cake's ORM, the following code should provide results equivalent to your raw SQL query:

$this->loadModel('ProjectsTag'); // Load the joining table as pseudo-model

// Define temporary belongsTo relationships between the pseudo-model and the two real models
$this->ProjectsTag->bindModel(array(
    'belongsTo' => array('Project','Tag')
));

// Retrieve all the join-table records with matching Tag.tag values
$result_set = $this->ProjectsTag->find('all',array(
    'conditions' => array('Tag.tag LIKE' => "%{$tag}%")
));

// Extract the associated Project records from the result-set
$projects = Set::extract('/Project', $result_set);

// Make the set of Project records available to the view
$this->set(compact('projects'));

Upvotes: 3

Sam D
Sam D

Reputation: 470

I would at least consider using the cakephp sanitize functions on your tag strings if they are user sourced. See http://book.cakephp.org/view/1183/Data-Sanitization or if using mysql as the db at least consider using http://www.php.net/manual/en/function.mysql-escape-string.php or do something to filter your user input. But the best thing is to make use of the CakePHP orm stuff.

Upvotes: 1

deceze
deceze

Reputation: 522185

I would strongly advise you to use the Cake ORM instead of raw queries, especially if you're going to plug URL parameters into it. Conditions on HABTM tables can be tricky, but you can build your joins using Cake's ORM syntax as well!

Read the manual, section 3.7.6.9 Joining tables.

Upvotes: 4

pleasedontbelong
pleasedontbelong

Reputation: 20102

in php there's a difference between single and double quotes... basically, single quotes dont evaluate the variables... use double quotes instead And i think that LIKE will need also single quotes.. i'm not really sure

"SELECT * FROM projects INNER JOIN projects_tags ON projects.id = projects_tags.project_id INNER JOIN tags on projects_tags.tag_id = tags.id WHERE tags.tag LIKE '$tag'"

i know.. i know.. people will start talkin' about sql injection.. and the need to scape the caracters... that's another question =)

good luck!

Upvotes: 1

fabrik
fabrik

Reputation: 14365

Modify your query as:

$this->set('projects',
$this->Project->query("SELECT * FROM projects
                       INNER JOIN projects_tags
                       ON projects.id = projects_tags.project_id
                       INNER JOIN tags ON projects_tags.tag_id = tags.id
                       WHERE tags.tag LIKE '" . $tag . "'") //problem was here
                     );

and it will work.

Upvotes: -4

Related Questions