如何根据我从客户端获取的JSON数组删除mysql记录? [英] How to DELETE mysql records depending on the JSON array I get from the client?

问题描述

我在代码中所做的基本上是获取当前mysql数据库中的所有id.从JSON数组获取ID.比较它们并删除不在JSON数组中的mysql表中的ID.

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.

<?php

获取POST数据(有效)

Getting POST Data (Works)

       $test = $_POST["data"];
       $obj = json_decode($test, true);
       $data = $obj["myarray"];

第一个sql查询(Works)

First sql query (Works)

       $sql3 = "select id from pbs";
       $current_ids= mysqli_query($connection, $sql3)

初始化ID的数组(Works)

initialize arrays for IDs (Works)

       $ids_array= array();
       $ids_array2= array();

从mysql表中获取当前ID集并将其放入ids_array(Works)

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'];
       }

遍历每个JSON数组$ data，即[{$ val}，{$ val}，...]

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

       foreach($data as $val){

这是我不确定的事情之一.我可以将每个id值推入and数组吗?即:

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

如果$ val = {"id":"1"，...} $ ids_array2应该具有值"1".如果正确，我应该有一个从JSON数组中获得的新ID数组.

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

按ID检查行是否在表中.如果是，请更新mysql行.如果不是，请插入一个新的mysql行(Works)

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

           }
       }

对于$ ids_array中的每个项目(mysql表中的当前ID集).如果它不在$ ids_array2(JSON中的ID新集)中，则将其从表中删除，其中id = $ item(不起作用)

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

?> 

所以我要尝试的是INSERT，UPDATE和DELETE记录，具体取决于我从客户端获取的JSON数组.谢谢！

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

推荐答案

我测试了这段代码:

<?php

$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);
}

这实现了一些目的:

• 只需一步就可以从数据库中删除不在JSON中的行，而无需从数据库中获取ID.无需为每个id值使用in_array().
• 插入JSON中但不在数据库中的行.
• 更新数据库和JSON中的行.
• 通过使用查询参数避免SQL注入.不过，对于删除查询而言，这不是安全的，因为我将id值强制转换为以逗号分隔的整数列表.
• 避免将数据库的全部内容加载到您的PHP应用程序中.如果数据库增长到足以超出内存限制的程度，该怎么办?

• 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?

发表您的评论

此代码:

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

请注意，要访问$val->id，我必须更改解码JSON的方式:

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

$obj = json_decode($test);

您传递了附加参数true，该参数更改了$obj子字段的创建方式.

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

这篇关于如何根据我从客户端获取的JSON数组删除mysql记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！