MySQL REPLACE is not working if we store query into a variable

Question

My query is like this:

<?php
$sql = "UPDATE macs_temp SET home_page_heading='$home_page_heading', home_page_sub_heading='$home_page_sub_heading', home_page_description='$home_page_description', about_us_heading='$about_us_heading', about_us_description='$about_us_description', business_description='$business_description', we_do_description='$we_do_description', video_description='$video_description', photography_description='$photography_description', music_description='$music_description', web_and_app_description=REPLACE($web_and_app_description, '\'', '') WHERE id=1";

if ($conn->query($sql) === TRUE) {
//    echo "Record updated successfully";
header("Location: index.php");
   exit;
} else {
    echo "Error updating record(Contact System Admin): " . $conn->error;
    echo "Inside else error..!";
}
?>

Gives Error as: Error updating record(Contact System Admin): 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 ''j, '\'', '') WHERE id=1' at line 1Inside else error..!

Note: My form contains 11 field any/more of that field may contains apostrophe, so with the all apostrophes i want to update all data in mysql database.

score 0 · Answer 1 · answered Aug 18 '17 at 10:14

Try this you need to enclose $web_and_app_description in '' so that it can be consider as string

$sql = "UPDATE macs_temp SET home_page_heading='$home_page_heading', 
home_page_sub_heading='$home_page_sub_heading', 
home_page_description='$home_page_description', 
about_us_heading='$about_us_heading', about_us_description='$about_us_description', 
business_description='$business_description', 
we_do_description='$we_do_description', video_description='$video_description', 
photography_description='$photography_description', 
music_description='$music_description', 
web_and_app_description=REPLACE('$web_and_app_description', '\'', '') WHERE 
id=1";

MySQL REPLACE is not working if we store query into a variable

1 Answers1