cakephp and PDO limit error

Question

Hi all I have a site develop in cakephp and I have a query in pdo where I want to insert a limit value. I have try in this mode:

$max_result = 10;
$search = "test";
$product_alias = $this->ProductAlias->query(
'SELECT DISTINCT * 
   FROM product_aliases 
   WHERE product_aliases.alias 
   LIKE :search LIMIT :limit_search'
 ,array('search' => '%'.$search.'%','limit_search' => intval(trim($max_result)))
);

I have tried also:

...
WHERE product_aliases.alias 
  LIKE :search 
  LIMIT :limit_search'
,array('search' => '%'.$search.'%','limit_search' => intval($max_result)));

and

...

WHERE product_aliases.alias 
  LIKE :search 
  LIMIT :limit_search'
,array('search' => '%'.$search.'%','limit_search' => $max_result));

but always return me this error: 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 '10' at line 1

I have seen that there is the bind but I don't know how to apply to this situation. Any solutions?

http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991623 — Your Common Sense, Apr 21 '13 at 18:37

hjpotter92 · Accepted Answer · 2013-04-21T17:40:44.593

1

Not sure about CakePHP API, but you can try this:

$product_alias = $this->ProductAlias->prepare('SELECT DISTINCT * 
    FROM product_aliases 
    WHERE product_aliases.alias LIKE :search 
    LIMIT :limit_search');
$product_alias->bindParam( 'search', '%'.$search.'%', PDO::PARAM_STR );
$product_alias->bindParam( 'limit_search', (int) intval(trim($max_result)), PDO::PARAM_INT );

On checking CakePHP's docs, they do provide PDOStatements too: http://api.cakephp.org/2.2/class-PDOStatement.html

edited Apr 21 '13 at 17:40

answered Apr 21 '13 at 17:34

hjpotter92

78,589
36
144
183

I have tried and return me 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 '' – Alessandro Minoccheri Apr 21 '13 at 17:38
@AlessandroMinoccheri Then there might be some other problems. Also, using `*` in SELECT queries isn't recommended. – hjpotter92 Apr 21 '13 at 18:43

score 0 · Answer 2 · answered Apr 21 '13 at 18:17

0

You don't have to do all this. (You shouldn't do this). Writing all your queries by hand basically makes the whole framework useless.

Read this part of the manual Retrieving your data

To retrieve your data in CakePHP, use this;

$product_alias  = $this->ProductAlias->find('all', array(
    'conditions' => array(
        'ProductAlias.alias LIKE' => '%' . $search . '%',
    ),
    'limit' => $max_result
));

answered Apr 21 '13 at 18:17

thaJeztah

27,738
9
73
92

I know how to retrieve data with cakephp but I would apply at the query PDO, I don't want to use this system for many reason – Alessandro Minoccheri Apr 21 '13 at 18:20
Could you explain those reasons? Also, please add the full SQL statement to your question so that we're able to see what 'CakePHP' created – thaJeztah Apr 21 '13 at 18:33
Because I have to make many and many left join insides and the best method to do that is to write at hand the query and not using find or contain. For this I would like to use PDO in that way if is possible. I don't want to print all the SQL because the problem isn't the sql – Alessandro Minoccheri Apr 21 '13 at 18:35
Hm, wonder if your database-design is really optimal. However, try this; `$db = $this->ProductAlias->getDataSource(); $db->execute('SQL STATEMENT', array(), array('param1' => xxx, 'param2' => xxx));`. See the source here: [DboSource::execute()](https://github.com/cakephp/cakephp/blob/master/lib/Cake/Model/Datasource/DboSource.php#L422) – thaJeztah Apr 21 '13 at 18:49
Unbind any models you don't need before you use the CakePHP method which @theJetzah recommended you should use. This will stop CakePHP from doing the many to many joins (see [documentation](http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#creating-and-destroying-associations-on-the-fly)). – Sam Delaney Apr 21 '13 at 20:40
@SamDelaney just set `AppModel->recursive` to `-1` to disable all joins by default and use the Containable behavior to specify which models *are* desired in your result. There's normally *no* need to unbind models, only in very rare situations – thaJeztah Apr 21 '13 at 20:44
Sorry @thaJeztah, you're right. My solution is only really appropriate if you want finer control of your associations. – Sam Delaney Apr 21 '13 at 22:28

cakephp and PDO limit error

2 Answers2