MySQL syntax error when UPDATEing a record with JSON

Question

Im mainly a JavaScript guy and im building a library for a client and the library caches some data in MySQL per HTTP_REFERRER. If the data is different than it is on the server it updates the cache. I don't know what exactly I'm doing wrong but its saying there is a syntax error with this:

if(mysql_query("UPDATE `cache-test` (cache) SET ('".addslashes(preg_replace('/\s\s+/', ' ', $referrer['cache']))."') WHERE url = '".$referrer['current']."'",$con)){ echo "saved"; }
else { echo mysql_error($con); }

The error Im getting is:

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 '(cache) SET ('[{\"LatLng\":{\"Ba\":45.531124,\"Ca\":-122.68374699999998},\"InfoW' at line 1

The data being sent looks like (before being stripped and slashes added, of course):

[{"LatLng":{"Ba":45.531124,"Ca":-122.68374699999998},"InfoWindow":"\n        <address>1125 NW 12th Ave, Portland, OR</address>\n        <p>My first apartment</p>\n      ","originalAddress":"1125 NW 12th Ave, Portland, OR"},{"LatLng":{"Ba":45.5144501,"Ca":-122.67644239999998},"InfoWindow":"\n        <address>1230 SW 2nd Ave, Portland, OR</address>\n        <p>My 2nd apartment</p>\n      ","originalAddress":"1230 SW 2nd Ave, Portland, OR"},{"LatLng":{"Ba":45.748955,"Ca":-122.47959000000003},"InfoWindow":"\n        <address>17501 NE 188th Ct, Brush Prairie, WA</address>\n        <p>The first place I lived by my own</p>\n      ","originalAddress":"17501 NE 188th Ct, Brush Prairie, WA"},{"LatLng":{"Ba":45.756944,"Ca":-122.43575800000002},"InfoWindow":"\n        <address>18607 NE Erickson Rd, Brush Prairie, WA</address>\n        <p>Last place I lived with my parents</p>\n      ","originalAddress":"18607 NE Erickson Rd, Brush Prairie, WA"}

Tudor Constantin · Accepted Answer · 2011-05-29 21:15:06Z

1

You don't have the field you wish to set:

UPDATE `cache-test` SET field_name = 'your huge val'

answered May 29, 2011 at 21:15

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

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

1 Comment

Oscar Godson Over a year ago

Thanks! that was it. I thought it worked like insert where you define the fields like (a,b) ('a data','b data').

Ibu · Accepted Answer · 2011-05-29 21:13:02Z

0

Replace addslashes with:

mysql_real_escape_string()

$cache = mysql_real_escape_string(preg_replace('/\s\s+/', ' ', $referrer['cache']));

mysql_query("UPDATE `cache-test`  
            SET (cache = '$cache') 
          WHERE url = '".$referrer['current']."'",$con));

answered May 29, 2011 at 21:13

Ibu

44k14 gold badges82 silver badges109 bronze badges

Collectives™ on Stack Overflow

MySQL syntax error when UPDATEing a record with JSON

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related