MySQL > update Query is not working while PDO type

debugcn Published at Dev

Digvijay Rathod

I'm working on this update query, But it's not working.. Here is query

UPDATE `tbl_abc` SET `field_name` = (CASE WHEN `field_name` LIKE '%:sub_string%' THEN `field_name` ELSE CONCAT(`field_name`, ' ### ', :sub_string) END) WHERE `field` = :value

I'm here trying to do is... update one record if sub string exist in field value then return field value it self.. and if sub string does not exist then append with old value. But problem is field_name LIKE '%:sub_string%' is not working... if i write direct field_name LIKE '%abc%' then it is working. I want to know that why this is not working??

whole code part is..

$query = $conn->prepare("UPDATE `tbl_abc` SET `field_name` = (CASE WHEN `field_name` LIKE '%:sub_string%' THEN `field_name` ELSE CONCAT(`field_name`, ' ### ', :sub_string) END) WHERE `field` = :value");
$query->bindParam(':sub_string', 'abc');
$query->bindParam(':value', 1);
$query->execute();

I hope you guys understand..

Masivuye Cokile

Remove the Wildcard Characters from the actual query and add it with your bindParams

    <?php

    $myString = "abc";
    $value = 1;

    $query = $conn->prepare("UPDATE `tbl_abc` SET `field_name` = (CASE WHEN `field_name` LIKE :sub_string1 THEN `field_name` ELSE CONCAT(`field_name`, ' ### ', :sub_string) END) WHERE `field` = :value");
    $query->bindParam(':sub_string1', '%'.$myString.'%');
    $query->bindParam(':sub_string', $myString);
    $query->bindParam(':value', $value);
    $query->execute();

?>

also remove the quotes from parameters

Update

Or might try with Question mark placeholders ?

<?php

    $myString = "abc";
    $value = 1;

    $query = $conn->prepare("UPDATE `tbl_abc` SET `field_name` = (CASE WHEN `field_name` LIKE ? THEN `field_name` ELSE CONCAT(`field_name`, ' ### ', ?) END) WHERE `field` = ?");
    $query->bindParam(1, '%'.$myString.'%');
    $query->bindParam(2, $myString);
    $query->bindParam(3, $value);
    $query->execute();

?>

Collected from the Internet

Please contact [email protected] to delete if infringement.