skip to Main Content

my code:

$newElements=json_encode(['name'=>'jorge','age'=>23]);

mysqli_query($db, "UPDATE dl SET data=json_replace(data, '$.list', cast('$newElements' as json)) WHERE id = '$ID' ")

my Error:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘json)) WHERE id = ‘8451’…

That my list of:

['name' => 'jak', 'age' => 13] let it be: ['name' => 'jorj', 'age' => 21]

2

Answers


  1. 
    $newElements = json_encode(['name' => 'jorge', 'age' => 23]);
    
    // Ensure the new elements are properly escaped for use in the SQL query
    $newElements = mysqli_real_escape_string($db, $newElements);
    
    $query = "
        UPDATE dl 
        SET data = JSON_REPLACE(data, '$.list', CAST('$newElements' AS JSON))
        WHERE id = '$ID'
    ";
    
    mysqli_query($db, $query) or die(mysqli_error($db));
    
    Login or Signup to reply.
  2. Check our path (JSON path) and also try to pass the elements with full path like the code below:

    UPDATE `users` SET `chamber`  =  JSON_REPLACE(`chamber`, '$.name', 'Ahmed', '$.age', 31);
    

    Suppose we have users table and chamber col (JSON col), containing this ({"age": 33,"name": "Khaled"}) after the above query you will get the updated values ({"age": 31,"name": "Ahmed"}) and don’t forget the where clause ;).

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search