0

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.

my first solution was this way but wasn't working as well although it seems more correct to me.

$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'] ]);

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

DevTN
  • 511
  • 2
  • 9
  • 35
  • Where you have something such as `'?,%'`, you need to make the bound value `variable,%` and just have `?` in the SQL. – Nigel Ren Feb 26 '21 at 13:34
  • @NigelRen that's what I tried before I posted my thread and wasn't working. $stmt->execute([ $_POST['username'] .',%', $_POST['username'] .','.... and so on – DevTN Feb 26 '21 at 15:09
  • Can you post that version then rather than what you currently have in the question. – Nigel Ren Feb 26 '21 at 15:11
  • @NigelRen I updated my question. Could you please check the added part ? do you see something wrong in my code ? Thank you very much. – DevTN Feb 26 '21 at 15:18

0 Answers0