php - PHP MySQL 使用带有数组的 JSON_ARRAY 更新 json 记录

Question

我有一个类可以将 json 数据更新到 restaurant_list 表中

表结构：

describe restaurant_list;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int unsigned | NO   | PRI | NULL    | auto_increment |
| restaurant_name  | varchar(50)  | NO   |     | NULL    |                |
| timing           | json         | NO   |     | NULL    |                |
| meals            | json         | NO   |     | NULL    |                |
| restaurant_order | json         | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
5 rows in set (0.10 sec)

该课程的简短示例：

public function jsonArray($table, $item, $values, $where = null)
{
    // i.e of updating data which works

    //UPDATE restaurant_list SET restaurant_order = JSON_ARRAY('1', '2') WHERE `id` = 1;
    
    $values   = ':'.$values;
    
    $sql = 'UPDATE '.$table.' SET '.$item.' = JSON_ARRAY('.$values.')';

    if ($where != null) {
        $sql .= ' WHERE ' . $where;
    }

    $this->sql    = $sql;
    return $this->sql;
}
    
/*** function to prepare query Start ***/
public function prepare()
{
    return $this->stmt = $this->dbh->prepare($this->sql);
}
/*** function to prepare query EnD ***/

/*** function to bind query Start ***/
public function bind($param, $value, $type = null)
{
    if (is_null($type)) :
        switch (true):
            case is_int($value):
                $type = PDO::PARAM_INT;
                break;

            case is_bool($value):
                $type = PDO::PARAM_BOOL;    
                break;

            case is_null($value):
                $type = PDO::PARAM_NULL;
                break;

            case is_string($value):
                $type = PDO::PARAM_STR;
                break;

            default: // array
                $type = null;
                //echo "string 44444<br>";

        endswitch; // end switch (true):
    endif; // end if (is_null($type)):

    if(is_array($value)):


        $value  = $value;
    else:
        $value = $this->strSafe($value);
    endif;

    $this->stmt->bindParam($param, $value, $type);
}

/*** function to bind query EnD ***/

/*** function to execute query Start ***/
public function execute()
{
    return $this->stmt->execute();
}
/*** function to execute query EnD ***/

/*** function to execute and fetch Start ***/
public function resultset()
{
    $this->execute();
    return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
}
/*** function to execute and fetch EnD ***/

public function strSafe($string)
{
    $string = trim(strip_tags(($string)));
    $string = preg_replace('/\s+/', ' ', $string); // remove more than one space
    $this->string    = $string;

    return $this->string;
}

当我使用该类时，该字段已更新，但结果如下所示：

SELECT restaurant_order FROM restaurant_list;
+----------------------+
| restaurant_order     |
+----------------------+
| ["1\",\"5"]          | ===> Concerned row, should be like below results
| ["1", "2", "3", "4"] |
| ["1", "2", "4"]      |
+----------------------+
3 rows in set (0.00 sec)

用于更新记录的代码：

require_once 'crud.php';
// initialize the connection class
$dbh    = new Database();

$p_cat      = [1,5];

$upOn   = $dbh->jsonArray('restaurant_list', 'restaurant_order', 'value', 'id = :id');
    
    $dbh->prepare($upOn);
    
    foreach($p_cat as $pc):
    $dbh->bind('value', implode('","', $p_cat));
    endforeach;

    
    $dbh->bind('id', 1);

    $dbh->execute();

如何使用有效数组更新记录？

上面的代码对任何攻击都安全吗？

标签： phpmysqlsqlpdo