0

I am trying to execute a MySQL statement. The variable clause has the where clause. The query was working fine until I used bindParam. From the documentation, there have been instances of LIKE being used. However, I am still unclear as to why my query returns null. I think the flaw is in using the :tag in the $clause.

 public function findAllByTag($tag)
    {
        $query  = "SELECT * FROM Conditions WHERE  ";
        $clause = "(tag LIKE ':tag,%' || tag LIKE '%,:tag' || tag LIKE '%,:tag,%' || tag LIKE ':tag')";
        $query .= $clause;
        $boundParams[0] = $query;
        $stmt           = $this->getPreparedStatement($query);
        $stmt->bindParam(':tag', $tag, \PDO::PARAM_STR, 100);
        $stmt->execute($boundParams);
        $collection = new Collection();
        while ($data = $stmt->fetch(\PDO::FETCH_ASSOC)) {
            $collection->add($this->createObject($data));
        }
        $collection->resetChanges();
        return $collection;
    }
Aviral Srivastava
  • 13
  • 4
  • You can only use the `:tag` alias once. So if you have multiple you should use `:tag1`, `:tag2`, `:tag3`, etc. – KIKO Software Feb 08 '18 at 07:58
  • @KIKOSoftware I think that it is wrong (I've tested that). The problem come from the fact that the OP `:tag` are wrapped by single quotes. Using `:tag` multiple times is OK. – Syscall Feb 08 '18 at 08:01
  • @Syscall __no__, you can use name for a placeholder only once. – u_mulder Feb 08 '18 at 08:03
  • @u_mulder I've tested : `'select * from words WHERE word=:word or word=concat(:word,"y")'` with parameters : `[':word'=>'cloud']`. It works : returns `cloud` and `cloudy`. – Syscall Feb 08 '18 at 08:06
  • Then there's something wrong in a manual, I suupose – u_mulder Feb 08 '18 at 08:08
  • **Or** in your code `emulation mode is on`. – u_mulder Feb 08 '18 at 08:25
  • Sorry, I was gone for a little while. Yes, http://php.net/manual/en/pdo.prepare.php states that you can use 'named parameter marker' multiple times when emulation mode is on. I wouldn't switch that on, however, it is less secure: https://michaelseiler.net/2016/07/04/dont-emulate-prepared-statements-pdo-mysql – KIKO Software Feb 08 '18 at 08:56
  • And how can I use this in my case? I have to use where tag like '$tag,%' and so on as depicted in the code sample. – Aviral Srivastava Feb 08 '18 at 09:07
  • @KIKOSoftware Thanks a lot for precisions! – Syscall Feb 08 '18 at 09:38
  • @u_mulder Could you please help me out here? I need to know how can I use the ticks in my case where there are multiple OR cases and regular expression to be used? Also, you should not mark this question as a duplicate since it is not! The link that you have provided does not cover my question. – Aviral Srivastava Feb 08 '18 at 10:05
  • 1
    What u_mulder means is that you cannot place `:tag` inside quotes. So `'%,:tag'` won't work because `:tag` will become just part of that string. Use `'CONCAT('%,',:tag)` instead. – KIKO Software Feb 08 '18 at 10:12
  • @KIKOSoftware hi, I tried your way and it throws me an error. Error is: Syntax error: unexpected ':' expecting ']' – Aviral Srivastava Feb 08 '18 at 10:56
  • I see an unintended quote in front of my CONCAT, but that wouldn't cause that error. Since I have no idea what your query now looks like I cannot help you. – KIKO Software Feb 08 '18 at 11:27
  • @KIKOSoftware My query is: CONCAT('%,',:tag) instead of the current one listed in the question. Or can you help me form the query? – Aviral Srivastava Feb 11 '18 at 12:20
  • I cannot help you. Why not rework you code and, if you cannot work out the cause of the error, ask a new question? – KIKO Software Feb 11 '18 at 13:05
  • @KIKOSoftware Thanks Just in case: my code is: https://pastebin.com/0XP7ye4d – Aviral Srivastava Feb 12 '18 at 07:19
  • Your question is marked as duplicate, I cannot provide an answer. However, it seems like you're looking for one element of a set. Your query could be: `SELECT * FROM table WHERE FIND_IN_SET(seach,list);`. I cannot solve your problem with placeholders here, it requires a longer answer. Start by not using the name 'tag' for two completely different things in the same function, it is very confusing. – KIKO Software Feb 12 '18 at 07:50

0 Answers0