how to use PDO prepared statements in PHP with SQL LIKE operator

I have an SQL query as below :

update tableShared SET shareWith = case
when shareWith like 'alex@gmail.com,%'   then replace(shareWith,'alex@gmail.com,','')
when shareWith like '%,alex@gmail.com,%' then replace(shareWith,',alex@gmail.com,',',')
when shareWith like '%,alex@gmail.com'   then replace(shareWith,',alex@gmail.com','')
when shareWith = 'alex@gmail.com' then ''
else shareWith
end
where createdBy = 'marc@gmail.com' AND reportName = 'sales Report 2020'

I want to use this query dynamically in PHP PDO prepared statements. Here is my code :

    $stmt = $conn->prepare("update tableShared 
       SET shareWith = case
          when shareWith like '?,%'   then replace(shareWith,'?,','')
          when shareWith like '%,?,%' then replace(shareWith,',?,',',')
          when shareWith like '%,?'   then replace(shareWith,',?','')
          when shareWith = '?' then ''
          else shareWith
          end
       where createdBy = ? AND reportName = ?");

    $stmt->execute([ $_POST['username'], $_POST['username'], $_POST['username'], $_POST['username'], 
      $_POST['username'], $_POST['username'], $_POST['username'], $_POST['createdBy'], $_POST['reportName'] ]);

I believe my code is not working because the parameters ? aren't recognized between the quotes when I execute the query.

Any suggestions please how can I use PDO prepared statements inside multiple SQL LIKE operator if this is possible ? Thank you very much.



Read more here: https://stackoverflow.com/questions/66386671/how-to-use-pdo-prepared-statements-in-php-with-sql-like-operator

Content Attribution

This content was originally published by DevTN at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: