如何使用JSON在PHP中更新MYSQL表

I am working with jstree. The tree works fine.I send JSON to a PHP file with JQuery. This works fine.

我正在使用jstree。树工作正常。我使用JQuery将JSON发送到PHP文件。这很好用。

$("#button3").click(function(){
    //json object
    var objtree = $("#container").jstree(true).get_json('#', { 'flat' : true });
    var fulltree = JSON.stringify(objtree);
    var myarray = $.parseJSON(fulltree);
    var params = { myarray: myarray };
    var paramJSON = JSON.stringify(params);

    //sending to php file
    $.post('update.php',{ data: paramJSON });

});

Then in the php file (update.php), I update mySQL table by: deleting all the records in $tablename ($sql1) and inserting the information gotten from the JSON ($sql2). This works fine.

然后在php文件（update.php）中，我通过以下方式更新mySQL表：删除$ tablename（$ sql1）中的所有记录并插入从JSON（$ sql2）获取的信息。这很好用。

<?php

    $connection = mysqli_connect($servername, $user,$password,$database) or die("Error " . mysqli_error($connection));

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

    //first query
    $sql1 = "DELETE FROM $tablename";
    $connection ->query($sql2);

    foreach($data as $val){
        //second query
        $sql2 = "INSERT INTO $tablename(id,parent,text) VALUES('".$val['id']."', '".$val['parent']."', '".$val['text']."')";
        $result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));
    }

    mysqli_close($connection);

?>

But what I want is not to delete everything in the table then insert them brand new. But an SQL statement to update the old values (using the id maybe?) and insert the new values.

但我想要的不是删除表中的所有内容，然后将它们全新插入。但是一个更新旧值的SQL语句（可能使用id？）并插入新值。

So like:

所以喜欢：

If id exists:
   update row
else:
   insert new row

I am new to PHP and SQL. So my problem is knowing the PHP syntax for accessing JSON array information. So please any example would be much appreciated!

我是PHP和SQL的新手。所以我的问题是知道用于访问JSON数组信息的PHP语法。所以请任何例子将不胜感激！

3 个解决方案

#1

First, you'll use a PHP function to get all rows matching the given ID. if this returns one, you'll use another function to UPDATEwhere the ID == valueFromPreviousFunction, else you'll call a function to INSERT a new row.

首先，您将使用PHP函数来获取与给定ID匹配的所有行。如果这返回一个，你将使用另一个函数UPDATEwhere ID == valueFromPreviousFunction，否则你将调用一个函数来插入一个新行。

#2

    $check = mysqli_query($connection,"SELECT * FROM `your_table_name` WHERE `id`='".$val["id"]."'");
    if(mysqli_num_rows($check)==1)
    {
        //Update the row
        $update = mysqli_query($connection,"UPDATE `table_name` SET `parent`='".$val["parent"]."', `text`='".$val["text"]."' WHERE `id`='".$val["id"]."'");
    }
    else
    {
        //Insert the row 
    }

#3

There isn't a command to do what you want in a single operation.

没有命令可以在单个操作中执行您想要的操作。

You should either fetch all ids beforehand and run an update for each one of them separately, or use REPLACE INTO.

您应该事先获取所有ID并分别为每个ID运行更新，或使用REPLACE INTO。

Be careful with REPLACE INTO, though: if a row matching a primary/unique key exists, it first deletes it and then inserts a new one rather than updating the existing row.

但是要注意REPLACE INTO：如果存在与主/唯一键匹配的行，它首先删除它然后插入一个新行而不是更新现有行。

#1

#2

    $check = mysqli_query($connection,"SELECT * FROM `your_table_name` WHERE `id`='".$val["id"]."'");
    if(mysqli_num_rows($check)==1)
    {
        //Update the row
        $update = mysqli_query($connection,"UPDATE `table_name` SET `parent`='".$val["parent"]."', `text`='".$val["text"]."' WHERE `id`='".$val["id"]."'");
    }
    else
    {
        //Insert the row 
    }

#3