-1

I have the following query:

SELECT p.*
        FROM property p
        INNER JOIN property_meta pm ON p.id = pm.property_id 
        WHERE LOWER(p.name) LIKE concat("%", :title, "%") AND p.deleted = :deleted AND ((pm.meta_key = :area_key AND pm.meta_value IN (:area_value)) )  GROUP BY p.id

which is executed using the following binding parameters:

$bind[':title'] = '';
$bind[':deleted'] = 0;
$bind[':area_key'] = 'property_area';
$bind[':area_value'] = '1217, 1270';

$sth = $this->db->prepare($query);
$sth->execute($bind);
return $sth->fetchAll();

the problem is that I doesn't get any result, but the most weird thing is that if I execute the query using PhpMyAdmin console, I correctly get the results.

is there any mistake I did?

Table structure

the property table have the following structure:

| id | title | deleted
  1     test    0
  2     test2   0

and property_meta have this structure:

| id | property_id | meta_key        | meta_value  
  1       1           property_area      1217
  4       2           property_area      1270
sfarzoso
  • 1,356
  • 2
  • 24
  • 65
  • Have you set it to show errors? add `$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` in the initialisation see [Docs](https://www.php.net/manual/en/pdo.error-handling.php) – akaBase Mar 31 '20 at 10:33
  • @akaBase yep this option is on, unfortunately I didn't get any errors, I just reiceve an empty result – sfarzoso Mar 31 '20 at 10:34
  • IN won't work with a CSV string, you may be able to use FIND_IN_SET - https://stackoverflow.com/questions/4155873/find-in-set-vs-in – Nigel Ren Mar 31 '20 at 10:37
  • You should check the `$sth->execute($bind)` in an if/else statement and and if its false there is an error and you can then get that message using `$sth->errorInfo()` – akaBase Mar 31 '20 at 10:39
  • The comment of @NigelRen has pointed me in the right direction, most probably I need to convert the values within `IN` operator in different parameters, that's why is not working – sfarzoso Mar 31 '20 at 10:40
  • If there really are no errors, then it's not a PDO, but an SQL problem. So in that case you'll have to share the db structure (with a little sample data to see what's expected). – El_Vanja Mar 31 '20 at 10:41
  • @El_Vanja please, check my example in the updated question, as I said using the same query all is working in PhpMyAdmin – sfarzoso Mar 31 '20 at 10:47
  • Does this answer your question? [Can I bind an array to an IN() condition?](https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition) – vrijdenker Mar 31 '20 at 10:53
  • @vrijdenker should I iterate over each values and binding it? – sfarzoso Mar 31 '20 at 10:55
  • I think so. I think the first answer in that post explains how to do it. However, I think the answer below (Gihan's answer) would work as well. – vrijdenker Mar 31 '20 at 11:41

1 Answers1

0

There are 2 issues in your query

  1. LIKE concat("%", :title, "%")

Instead, you should use as below

LIKE :title
...
$bind[':title'] = '%' . $title . '%';
  1. pm.meta_value IN (:area_value)

PDO doesn't support binding values for IN clause. Instead, you should use as below

pm.meta_value IN ($areaValue)

You'll have to properly sanitize $areaValue and it should not be empty.

Gihan
  • 4,163
  • 6
  • 31
  • 49