UPDATE with IN using PDO

Question

I am migrating an old system to PDO for security reasons. Now I'm having a hard time making an UPDATE with filtering with IN.The filter in the WHERE is working but the UPDATE vestments id_user and date_prog are not.

Currently I don't get any error message and when I print the stmt I get:

UPDATE os SET id_user =?, date_prog =? WHERE words IN (?,?)

The Array ():

Array ([1] => 30762507, [2] => 30762508)

Even so, UPDATE does not happen. Where can I go wrong.

So far I've used an old question as a basis: PDO: Invalid parameter number: mixed named and positional parameters

Thanks in advance for any help. Hug.

My complete code:

$data = date('Y-m-d H:i:s', time());
$id_user = $_POST['id_user'];
$date_prog = $_POST['date_prog'];
$list_words = $_POST['words'];


$where = array();

$words = preg_split('/[\s]+/',$list_words);

array_unshift($words, '');
unset($words[0]);
        
$IN  = str_repeat('?,',count($words) - 1) . '?';
    
$stmt = "UPDATE os SET id_user = ?, date_prog = ? WHERE words IN ($IN)";
$stmt = $conn->prepare($stmt);

foreach($words AS $index => $word){
    $sqlupos->bindValue($index, $word, PDO::PARAM_STR);
}
$stmt->bindValue($index+1, $id_user, PDO::PARAM_INT);
$stmt->bindValue($index+2, $date_prog, PDO::PARAM_STR);
$stmt->execute();
$data_stmt = $stmt->fetchAll(PDO::FETCH_ASSOC);

It should be `... where number in ...`, not `where in number...` — Shadow, Oct 06 '20 at 12:25
It was just a typo in the example. As I reported the problem is to filter the data using the IN and when doing this in UPDATE. — Bonfim Jr, Oct 06 '20 at 12:35
In this case either the parameters do not match any records or the update will not change any of the fields. — Shadow, Oct 06 '20 at 12:48
Please look this solution: https://phpize.online/?phpses=a412e4c8fa64a1b9e9d35c59733bf436&sqlses=eb53699a14e52c37aa37dff42c04166c&php_version=php7&sql_version=mysql57 — Slava Rozhnev, Oct 06 '20 at 13:01

score 1 · Accepted Answer · answered Oct 06 '20 at 13:04

In PHP the PDO method bindValue uses named placeholders, so you can use next code:

$IN = ':a' . implode(', :a', array_keys($words));

$sql = "UPDATE os SET id_user = :id_user, date_prog = :date_prog WHERE words IN ($IN)";

$stmt = $conn->prepare($sql);

foreach($words AS $index => $word){
    $stmt->bindValue("a$index", $word, PDO::PARAM_STR);
}

$stmt->bindValue(':id_user', $id_user, PDO::PARAM_INT);
$stmt->bindValue(':date_prog', $date_prog, PDO::PARAM_STR);
$stmt->execute();

Look live code on PHPize.online

UPDATE with IN using PDO

1 Answers1