CakePHP: add variables to query in controller

Question

Hi,

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

Answer 1

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.

Answer 2

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!

Answer 3

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.

Answer 4

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.

Answer 5

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'));