Mysql Update query issue using json_encode in php

Question

When i update data using php mysql, got some issue, my code php code are here

$query = "UPDATE `wp_experience` SET 
                     `exp_from` ='". $exp_from."' ,
                     `exp_to` = '". $exp_to."' ,
                     `exp_title` = '". json_encode($exp_title)."',
                     `exp_desc` = '". json_encode($exp_desc)."' ,                               
                     `exp_cat` = '". $exp_cat."'                                
                     WHERE `id` =".$oldid;

it will produce data like,

UPDATE wp_experience SET exp_from ='2016-01-22 00:00:00' , exp_to = '2002-11-14 00:00:00' , exp_title = '{"en":" PSA Peugeot Citroën Automobiles, Mulhouse (F-68)","fr":"Technical Directué - FRENCH","de":"Responsable d'unité de maintenance"}', exp_desc = '{"en":" Test</p>","fr":" Test</p>","de":" H</p>"}' , exp_cat = '18' WHERE id =28

i got this issue,

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unité de maintenance"}', exp_desc = '{"en":" Test</p>","fr":" Test</p' at line 1

How to fix this issue??

Responsable d'unité string breaks your query. use mysqli_real_escape_string() as much as you can in order to avoid such problems. — mitkosoft
– mitkosoft, Commented Mar 22, 2016 at 15:13

mitkosoft · Accepted Answer · 2016-03-22 15:17:37Z

1

Some of your embedded strings breaks your query, so either use mysqli_real_escape_string() or prepared SQL statements:

$query = "
    UPDATE 
        wp_experience 
    SET 
        exp_from    = '" . $exp_from . "' ,  
        exp_to      = '" . $exp_to . "' , 
        exp_title   = '" . mysqli_real_escape_string($con, json_encode($exp_title)) . "',  
        exp_desc    = '" . mysqli_real_escape_string($con, json_encode($exp_desc)) . "' ,
        exp_cat     = '" . mysqli_real_escape_string($con, $exp_cat) . "'
    WHERE 
        id = " . $oldid;

answered Mar 22, 2016 at 15:17

mitkosoft

5,3361 gold badge15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Update query issue using json_encode in php

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related