Unable to bindparam custom regular expression with LIKE

Question

I am unable to create a query with custom defined reg-ex using LIKE operator while using bindParam.

Here is my code:

public function findAllByTag($tag)
    {
        $expressionOne = $tag.",%";
        $expressionTwo = "%,".$tag;
        $expressionThree = "%,".$tag.",%";
        $expressionFour = $tag;
        $query  = "SELECT * FROM Conditions WHERE  ";/*'CONCAT(:tag, ',%')*/
        $clause = "(tag LIKE :expressionOne || tag LIKE :expressionTwo || tag LIKE :expressionThree|| tag LIKE :expressionFour)";
        $query .= $clause;
        $boundParams[0] = $query;
        $stmt           = $this->getPreparedStatement($query);
        $stmt->bindParam(':expressionOne', $expressionOne, \PDO::PARAM_STR);
        $stmt->bindParam(':expressionTwo', $expressionTwo, \PDO::PARAM_STR);
        $stmt->bindParam(':expressionThree', $expressionThree, \PDO::PARAM_STR);
        $stmt->bindParam(':expressionFour', $expressionFour, \PDO::PARAM_STR);
        $boundParams[0] = $query;
        $stmt->execute($boundParams);
        $collection = new Collection();
        while ($data = $stmt->fetch(\PDO::FETCH_ASSOC)) {
            $collection->add($this->createObject($data));
        }
        $collection->resetChanges();
        return $collection;
    }

It throws an error: unexpected ':' expecting ']' in the code below:

class ExceptionResponse extends Response
{
    /**
     * It prepares response message from message and code of exception, statusCode is initialized to exception code and
     * message is an array of code and message of format {_errors: [ {'code' : int, 'message': string }, .. ]}
     *
     * @param Exception $e exception from which return response will be created
     */
    public function __construct(\Exception $e)
    {
        if (!($e instanceof GenericException)) {
            $e = new GenericException($e->getMessage());
        }
        $data = [
            'error' => true,
            'failure' => [
                [
                    'code'    => $e->getCode(),
                    'message' => $e->getMessage(),
                ],
            ],
            'success': null
        ];
        $this->setHeader('Content-Type', 'application/json');
        $this->setMessage(json_encode($data));
        $this->setStatusCode($e->getCode());
    }
}

Line #39: 'success': null

How can I remove this error and make my query work? Note: I am using a framework of my firm, griffin and writing code over it. All is working fine but bindParam.

Possible duplicate of [What is the difference between bindParam and bindValue?](https://stackoverflow.com/questions/1179874/what-is-the-difference-between-bindparam-and-bindvalue) — Rotimi, Feb 12 '18 at 07:38
@vijoc I am sorry that you didn't get the question. It is the first line Unable to bindparam custom regular expression with LIKE — Aviral Srivastava, Feb 12 '18 at 09:24
@Akintunde007 Its working with bindValue but not with bindParam. Can you please explain me why you suggested this? — Aviral Srivastava, Feb 12 '18 at 09:50
@AviralSrivastava there are no question marks and no questions in your "question", but rather statements. You say you are "unable to create a query", and include a syntax error report. It is unclear to me what you would like to have answered. — vijoc, Feb 12 '18 at 10:21
@vijoc I have edited it and I am sorry for the confusion caused. Could you please remove the downvote? Thanks :) — Aviral Srivastava, Feb 12 '18 at 12:13

score 0 · Accepted Answer · answered Mar 13 '18 at 06:56

I tried using bindValues and it worked just fine.

        $expressionOne = $tag.",%";
        $expressionTwo = "%,".$tag;
        $expressionThree = "%,".$tag.",%";
        $expressionFour = $tag;
        $clause = "(tag LIKE :expressionOne || tag LIKE :expressionTwo || tag LIKE :expressionThree|| tag LIKE :expressionFour)";
        $query  = "SELECT * FROM Conditions WHERE ";
        //$clause = "(tag LIKE :expressionOne)";
        $query .= $clause;
        $boundParams[0] = $query;
        $stmt           = $this->getPreparedStatement($query);
        //$stmt->bind_param("s", $tag);
        $stmt->bindValue(':expressionOne', $expressionOne, \PDO::PARAM_STR);
        $stmt->bindValue(':expressionTwo', $expressionTwo, \PDO::PARAM_STR);
        $stmt->bindValue(':expressionThree', $expressionThree, \PDO::PARAM_STR);
        $stmt->bindValue(':expressionFour', $expressionFour, \PDO::PARAM_STR);

Unable to bindparam custom regular expression with LIKE

1 Answers1