MySQL > update Query is not working while PDO type

Question

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..

remove the %% from the query and do like $query->bindParam(':sub_string', '%abc%'); — Masivuye Cokile
– Masivuye Cokile, Commented May 17, 2017 at 11:05
(named) parameters/placeholders in values should not contain quotes, for one thing. — Funk Forty Niner
– Funk Forty Niner, Commented May 17, 2017 at 11:15

Masivuye Cokile · Accepted Answer · 2017-05-17 11:23:45Z

2

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();

?>

edited May 17, 2017 at 11:23

answered May 17, 2017 at 11:06

Masivuye Cokile

4,7733 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Digvijay Rathod Over a year ago

Yes I've done that but it is returning Fatal error: Cannot pass parameter 2 by reference.. sue to '%' . $mystring . '%'.. then I've tried by doing this.... $mystring = '%' . $mystring . '%' but it is taking as whole string... like it is finding... for %abc%....

Rotimi Over a year ago

@MasivuyeCokile you must remove the quotes around ':sub_string1'

Funk Forty Niner Over a year ago

@Akintunde "might"? => "must". ;-)

Funk Forty Niner Over a year ago

@Akintunde hehe ;-)

Masivuye Cokile Over a year ago

@DigvijayRathod can u try the second method

|

Akam · Accepted Answer · 2017-05-17 11:07:17Z

0

You can't enclose the parameters with quotes, instead, you can use as follow:

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

Then

$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_string1', '%abc%');
$query->bindParam(':sub_string2', 'abc');
$query->bindParam(':value', 1);
$query->execute();

answered May 17, 2017 at 11:07

Akam

1,05216 silver badges25 bronze badges

Comments

xRahul · Accepted Answer · 2017-05-17 11:12:59Z

0

bindParam binds to a variable name and not the constant. Use this instead-

$abc = 'abc';
$one = 1;
$query->bindParam(':sub_string', $abc);
$query->bindParam(':value', $one);

PDOStatement::bindParam — Binds a parameter to the specified variable name

Relevant comment regarding using %% with like and bindParam.

PHP Documentation- PDOStatement::bindParam

edited May 17, 2017 at 11:12

answered May 17, 2017 at 11:07

xRahul

3646 silver badges18 bronze badges

Comments

Rotimi · Accepted Answer · 2017-05-17 11:30:01Z

0

PDO's bindParam will ensure that all string data is properly quoted when sent to the SQL query

Try this:

<?php

    $term = "random";
    $finalTerm= "$term%";
    $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, $finalTerm);
    $query->bindParam(2, $myString);
    $query->bindParam(3, $value);
    $query->execute();

?>

answered May 17, 2017 at 11:30

Rotimi

4,7804 gold badges21 silver badges28 bronze badges

Collectives™ on Stack Overflow

MySQL > update Query is not working while PDO type

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related