MySQL updating records with condition

Question

Table A

id | name | important |
-----------------------
 1   Abe        0
 2   Ann        1
 3   John       1
 4   Bill       0

I have a php array of id values

 $ids = [1, 4];

I want update table A setting important column equals 1 if id column value is in $ids array AND setting important column value equals 0 if not.

So for the example, the table after update would be:

id | name | important |
-----------------------
 1   Abe        1
 2   Ann        0
 3   John       0
 4   Bill       1

How to do?

Thanks for any help!

Are you looking to achieve this in a single query specifically or it doesn't matter? Have you made any attempts to write the query/queries? It's `update... where...`, nothing more. — El_Vanja, Nov 30 '20 at 19:11

a55 · Answer 1 · 2020-12-08T16:34:20.413

-1

First make array safe before insert

$ids = array_map('intval', $ids);

Then insert with one query

<?php


"UPDATE TableA SET important=1 WHERE id in(" . implode(',',$ids) . ");"

edited Dec 08 '20 at 16:34

answered Nov 30 '20 at 19:20

a55

376
3
13

The `mysql_` extension has been deprecated and removed from PHP as of v7. – El_Vanja Nov 30 '20 at 19:25
@el-vanja also i dont use `mysql_` in my answer, i write a string only ! – a55 Nov 30 '20 at 19:31
See about sql injection and the importance of prepared and bound queries – Strawberry Nov 30 '20 at 19:31
Yes, it is important. It's been deprecated since 5.5 (that was in 2013). Recommending to use functionalities that have been deprecated for over 7 years is irresponsible. – El_Vanja Nov 30 '20 at 19:32
@strawberry Can any body bypass `intval` to do sql injection!? i think you dont know what "sql injection" is, and what "intval" is, You dont read even first line of the answer :) – a55 Nov 30 '20 at 19:39
Still running PHP4 today is also irresponsible, from the security perspective. – El_Vanja Nov 30 '20 at 19:42
3

@a55 provided safe and elegant solution. – Milda Nov 30 '20 at 19:49
2

Thank you a55. Your solution for that matter is good enougth to me! – user2671169 Nov 30 '20 at 21:42

Slava Rozhnev · Accepted Answer · 2020-11-30T22:43:21.397

Basically you need to run next query:

UPDATE TableA SET important = (id IN (1,4));

Below php implementation of this:

<?php
$ids = [1,4];

//prepare SQL statement with plaseholders
$placeholder =  implode(',', array_pad([], count($ids) , '?'));
$sql = "UPDATE TableA SET important = (id IN ($placeholder));";

// run update with ids
$pdo->prepare($sql)->execute($ids);

// Get updated data for check result
$sth = $pdo->prepare("SELECT * FROM TableA");
$sth->execute();

print_r($sth->fetchAll());

And here you can test the code.

score -1 · Answer 3 · answered Dec 02 '20 at 05:39

i am update id value using php pdo. and its work successfully.

first i can try given id value update in 1. and then after try to all ids value update in 0.

so please try this code.

<?php

//This is a database connection

$host_name = 'localhost';
$db_name = 'stackoverflow';
$user_name = 'root';
$password = '';

$conn = new PDO("mysql:host=$host_name; dbname=$db_name;", $user_name, $password);
 
$ids = [1, 4]; //This is a id for update value is 1

$sqlforOneVal = "UPDATE important SET important = 1 WHERE id in (".(implode(', ', $ids)).")";
$updateforOne = $conn->prepare($sqlforOneVal);
$updateforOne->execute();

$sqlforZeroVal = "UPDATE important SET important = 0 WHERE id not in (".(implode(', ', $ids)).")";
$updateforZero = $conn->prepare($sqlforZeroVal);
$updateforZero->execute();

?>

MySQL updating records with condition

3 Answers3