douxian3170 2016-10-26 20:54
浏览 97

如何根据我从客户端获得的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.

<?php

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!

  • 写回答

2条回答 默认 最新

  • doumei1955 2016-10-26 21:34
    关注

    You have syntax error in the query. This will work:

       foreach($ids_array as $item){
           if(!in_array($item, $ids_array2)){
    
                   $sql5 = "DELETE FROM pbs WHERE id='".$item."'";
                   $delete= mysqli_query($connection, $sql5);
               }    
       }
    
       mysqli_close($connection);
    

    or use a single DELETE query:

       $deleteIds = [];
       foreach($ids_array as $item){
           if(!in_array($item, $ids_array2)){
               $deleteIds[] = $item;
           }    
       }
    
       $sql5 = "DELETE FROM pbs WHERE id IN (".implode(',', $deleteIds).")";
       $delete= mysqli_query($connection, $sql5);
    
    评论

报告相同问题?

悬赏问题

  • ¥15 C# P/Invoke的效率问题
  • ¥20 thinkphp适配人大金仓问题
  • ¥20 Oracle替换.dbf文件后无法连接,如何解决?(相关搜索:数据库|死循环)
  • ¥15 数据库数据成问号了,前台查询正常,数据库查询是?号
  • ¥15 算法使用了tf-idf,用手肘图确定k值确定不了,第四轮廓系数又太小才有0.006088746097507285,如何解决?(相关搜索:数据处理)
  • ¥15 彩灯控制电路,会的加我QQ1482956179
  • ¥200 相机拍直接转存到电脑上 立拍立穿无线局域网传
  • ¥15 (关键词-电路设计)
  • ¥15 如何解决MIPS计算是否溢出
  • ¥15 vue中我代理了iframe,iframe却走的是路由,没有显示该显示的网站,这个该如何处理