mysql json 数组删除操作,如何根据我从客户端获取的JSON数组删除mysql记录？

What I am doing in the code is basically getting all the ids currently in mysql database. Getting the ids from a JSON array. Comparing them and deleting the ids in the mysql table that are NOT in the JSON array.

Getting POST Data (Works)

$test = $_POST["data"];

$obj = json_decode($test, true);

$data = $obj["myarray"];

First sql query (Works)

$sql3 = "select id from pbs";

$current_ids= mysqli_query($connection, $sql3)

initialize arrays for IDs (Works)

$ids_array= array();

$ids_array2= array();

Get current set of ids from mysql table and putting them in ids_array (Works)

while($row = mysqli_fetch_array($current_ids)){

$ids_array[] = $row['id'];

}

Looping over each JSON array $data i.e [{$val}, {$val}, ...]

foreach($data as $val){

This is one of the things I'm unsure about. Can I push each id value into and array? i.e:

if $val = {"id": "1", ...} $ids_array2 should have the value "1". If this is correct, I should have an array of new ids that I got from the JSON array.

array_push($ids_array2, $val->id);

checks if row is in table by id. If it is, update the mysql row. If it is not, insert a new mysql row (Works)

$check = mysqli_query($connection,"SELECT * FROM `pbs` WHERE `id`='".$val["id"]."'");

if(mysqli_num_rows($check)==1){

//Update the row

}

else{

//Insert the row

}

}

For each item in $ids_array (current set of ids from mysql table). Delete it from table if it is not in $ids_array2 (newset of ids from JSON) where id = $item (Does not work)

foreach($ids_array as $item){

if(in_array($item, $ids_array2)==0){

$sql5 = "DELETE FROM pbs WHERE id='"$item"'";

$delete= mysqli_query($connection, $sql5);

}

}

mysqli_close($connection);

?>

So what I am trying to do is INSERT, UPDATE and DELETE records depending on the JSON array I get from the client. Thanks!

解决方案

I tested this code:

$test = '{ "myarray": [

{ "id": 1, "name": "Harry" },

{ "id": 2, "name": "Ron" },

{ "id": 3, "name": "Hermione" },

{ "id": 4, "name": "Neville" }

] }';

$obj = json_decode($test);

$data = $obj->myarray;

$id_list = implode(",", array_map(function ($val) { return (int) $val->id; },

$data));

$connection = mysqli_connect(...);

mysqli_query($connection, "DELETE FROM names WHERE id NOT IN ($id_list)");

foreach ($data as $val) {

$sql = "INSERT INTO names SET id=?, name=?

ON DUPLICATE KEY UPDATE name=VALUES(name)";

$stmt = mysqli_prepare($connection, $sql);

mysqli_stmt_bind_param($stmt, "ds", $val->id, $val->name);

mysqli_stmt_execute($stmt);

}

This achieves a few things:

Delete rows from the database that aren't in the JSON, in one step, without fetching the ids from the database. No need to use in_array() for every id value.

Insert rows that are in the JSON but aren't in the database.

Update rows that are both in the database and in the JSON.

Avoid SQL injection by using query parameters. Not for the delete query though, this is safe because I cast the id values to a comma-separated list of integers.

Avoid loading the entire contents of the database into your PHP app. What if the database grows large enough to exceed your memory limit?

Re your comment:

This code:

$id_list = implode(",", array_map(function ($val) { return (int) $val->id; },

$data));

gives the same result as this:

$id_list_array = [];

foreach ($data as $val) {

$id_list_array[] = $val->id;

}

$id_list = implode(",", $id_list_array);

Note that to access $val->id, I had to change the way you decode the JSON:

$obj = json_decode($test);

You passed the additional argument true, which changes the way the sub-fields of $obj are created.