Results 1 to 4 of 4

Thread: update json value in mysql_row

  1. #1
    Join Date
    Dec 2012
    Location
    Kunnamkulam
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default update json value in mysql_row

    I have a json data in a mysql row value like this :
    Code:
    {AF:'0',AX:'0',AL:'0',DZ:'0',AS:'0',AD:'0',AO:'0',AI:'0',AQ:'0',AG:'0',AR:'0',AM:'0',AW:'0',AU:'0',AT:'0',AZ:'0',BS:'0',BH:'0',BD:'0',BB:'0',BY:'0',BE:'0',BZ:'0',BJ:'0',BM:'0',BT:'0',BO:'0',BA:'0',BW:'0',BV:'0',BR:'0',IO:'0',BN:'0',BG:'0',BF:'0',BI:'0',KH:'0',CM:'0',CA:'0',CV:'0',KY:'0',CF:'0',TD:'0',CL:'0',CN:'0',CX:'0',CC:'0',CO:'0',KM:'0',CG:'0',CD:'0',CK:'0',CR:'0',CI:'0',HR:'0',CU:'0',CY:'0',CZ:'0',DK:'0',DJ:'0',DM:'0',DO:'0',EC:'0',EG:'0',SV:'0',GQ:'0',ER:'0',EE:'0',ET:'0',FK:'0',FO:'0',FJ:'0',FI:'0',FR:'0',GF:'0',PF:'0',TF:'0',GA:'0',GM:'0',GE:'0',DE:'0',GH:'0',GI:'0',GR:'0',GL:'0',GD:'0',GP:'0',GU:'0',GT:'0',GG:'0',GN:'0',GW:'0',GY:'0',HT:'0',HM:'0',VA:'0',HN:'0',HK:'0',HU:'0',IS:'0',IN:'0',ID:'0',IR:'0',IQ:'0',IE:'0',IM:'0',IL:'0',IT:'0',JM:'0',JP:'0',JE:'0',JO:'0',KZ:'0',KE:'0',KI:'0',KP:'0',KR:'0',KW:'0',KG:'0',LA:'0',LV:'0',LB:'0',LS:'0',LR:'0',LY:'0',LI:'0',LT:'0',LU:'0',MO:'0',MK:'0',MG:'0',MW:'0',MY:'0',MV:'0',ML:'0',MT:'0',MH:'0',MQ:'0',MR:'0',MU:'0',YT:'0',MX:'0',FM:'0',MD:'0',MC:'0',MN:'0',ME:'0',MS:'0',MA:'0',MZ:'0',MM:'0',NA:'0',NR:'0',NP:'0',NL:'0',AN:'0',NC:'0',NZ:'0',NI:'0',NE:'0',NG:'0',NU:'0',NF:'0',MP:'0',NO:'0',OM:'0',PK:'0',PW:'0',PS:'0',PA:'0',PG:'0',PY:'0',PE:'0',PH:'0',PN:'0',PL:'0',PT:'0',PR:'0',QA:'0',RE:'0',RO:'0',RU:'0',RW:'0',BL:'0',SH:'0',KN:'0',LC:'0',MF:'0',PM:'0',VC:'0',WS:'0',SM:'0',ST:'0',SA:'0',SN:'0',RS:'0',SC:'0',SL:'0',SG:'0',SK:'0',SI:'0',SB:'0',SO:'0',ZA:'0',GS:'0',ES:'0',LK:'0',SD:'0',SR:'0',SJ:'0',SZ:'0',SE:'0',CH:'0',SY:'0',TW:'0',TJ:'0',TZ:'0',TH:'0',TL:'0',TG:'0',TK:'0',TO:'0',TT:'0',TN:'0',TR:'0',TM:'0',TC:'0',TV:'0',UG:'0',UA:'0',AE:'0',GB:'0',US:'0',UM:'0',UY:'0',UZ:'0',VU:'0',VE:'0',VN:'0',VG:'0',VI:'0',WF:'0',EH:'0',YE:'0',ZM:'0',ZW:'0'}
    suppose I want to update the AF string's value in the JSON data. I am using PHP. How can I update the value with the value+1. For example AF value should be AF+1.
    Please help.

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by subins2000 View Post
    I have a json data in a mysql row value like this :
    Code:
    {AF:'0',AX:'0',AL:'0',DZ:'0',AS:'0',AD:'0',AO:'0',AI:'0',AQ:'0',AG:'0',AR:'0',AM:'0',AW:'0',AU:'0',AT:'0',AZ:'0',BS:'0',BH:'0',BD:'0',BB:'0',BY:'0',BE:'0',BZ:'0',BJ:'0',BM:'0',BT:'0',BO:'0',BA:'0',BW:'0',BV:'0',BR:'0',IO:'0',BN:'0',BG:'0',BF:'0',BI:'0',KH:'0',CM:'0',CA:'0',CV:'0',KY:'0',CF:'0',TD:'0',CL:'0',CN:'0',CX:'0',CC:'0',CO:'0',KM:'0',CG:'0',CD:'0',CK:'0',CR:'0',CI:'0',HR:'0',CU:'0',CY:'0',CZ:'0',DK:'0',DJ:'0',DM:'0',DO:'0',EC:'0',EG:'0',SV:'0',GQ:'0',ER:'0',EE:'0',ET:'0',FK:'0',FO:'0',FJ:'0',FI:'0',FR:'0',GF:'0',PF:'0',TF:'0',GA:'0',GM:'0',GE:'0',DE:'0',GH:'0',GI:'0',GR:'0',GL:'0',GD:'0',GP:'0',GU:'0',GT:'0',GG:'0',GN:'0',GW:'0',GY:'0',HT:'0',HM:'0',VA:'0',HN:'0',HK:'0',HU:'0',IS:'0',IN:'0',ID:'0',IR:'0',IQ:'0',IE:'0',IM:'0',IL:'0',IT:'0',JM:'0',JP:'0',JE:'0',JO:'0',KZ:'0',KE:'0',KI:'0',KP:'0',KR:'0',KW:'0',KG:'0',LA:'0',LV:'0',LB:'0',LS:'0',LR:'0',LY:'0',LI:'0',LT:'0',LU:'0',MO:'0',MK:'0',MG:'0',MW:'0',MY:'0',MV:'0',ML:'0',MT:'0',MH:'0',MQ:'0',MR:'0',MU:'0',YT:'0',MX:'0',FM:'0',MD:'0',MC:'0',MN:'0',ME:'0',MS:'0',MA:'0',MZ:'0',MM:'0',NA:'0',NR:'0',NP:'0',NL:'0',AN:'0',NC:'0',NZ:'0',NI:'0',NE:'0',NG:'0',NU:'0',NF:'0',MP:'0',NO:'0',OM:'0',PK:'0',PW:'0',PS:'0',PA:'0',PG:'0',PY:'0',PE:'0',PH:'0',PN:'0',PL:'0',PT:'0',PR:'0',QA:'0',RE:'0',RO:'0',RU:'0',RW:'0',BL:'0',SH:'0',KN:'0',LC:'0',MF:'0',PM:'0',VC:'0',WS:'0',SM:'0',ST:'0',SA:'0',SN:'0',RS:'0',SC:'0',SL:'0',SG:'0',SK:'0',SI:'0',SB:'0',SO:'0',ZA:'0',GS:'0',ES:'0',LK:'0',SD:'0',SR:'0',SJ:'0',SZ:'0',SE:'0',CH:'0',SY:'0',TW:'0',TJ:'0',TZ:'0',TH:'0',TL:'0',TG:'0',TK:'0',TO:'0',TT:'0',TN:'0',TR:'0',TM:'0',TC:'0',TV:'0',UG:'0',UA:'0',AE:'0',GB:'0',US:'0',UM:'0',UY:'0',UZ:'0',VU:'0',VE:'0',VN:'0',VG:'0',VI:'0',WF:'0',EH:'0',YE:'0',ZM:'0',ZW:'0'}
    suppose I want to update the AF string's value in the JSON data. I am using PHP. How can I update the value with the value+1. For example AF value should be AF+1.
    Please help.
    This is an excellent example of why you shouldn't be storing multiple values (in JSON or any other format) in a single column in your DB. The data is completely opaque to MySQL: it becomes very difficult and inefficient to search, read, or update.

    You're going to have to:
    • retrieve the entire JSON string
    • decode it into a standard PHP array
    • find and update the value(s)
    • re-encode the array as JSON
    • ...and then update the row.


    Whereas, if you had a nicely designed table with single values in single columns, you could simply query
    Code:
    UPDATE `tablename` SET `AF` = `AF`+1
    and MySQL would happily "just do it."

  3. #3
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,162
    Thanks
    263
    Thanked 690 Times in 678 Posts

    Default

    JSON would be a good idea if you are only ever storing and retrieving all of that information as a whole JSON array. As traq said, it just won't work if not.

    (Technically I suppose you could try to use LIKE '%some value%' to see if the JSON entry has some value within it, but I don't think that would be too reliable.)
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  4. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by djr33 View Post
    JSON would be a good idea if you are only ever storing and retrieving all of that information as a whole JSON array
    true - if you always need all of the info at once, and never need to search through it beforehand - i.e., you can just treat it like a "lump" when it's in the DB. Even then, however, you run the risk of outgrowing that situation and being stuck with a bad design.

    From your question, though, it doesn't seem like that's the case here.
    Does all this answer your question? If you have a more specific problem, please explain.

Similar Threads

  1. Json Problem
    By manish soni in forum Other
    Replies: 1
    Last Post: 07-25-2012, 05:40 PM
  2. Resolved array jSon
    By ggalan in forum PHP
    Replies: 2
    Last Post: 07-26-2011, 02:30 AM
  3. Best XML to JSON converter?
    By jlizarraga in forum JavaScript
    Replies: 2
    Last Post: 03-15-2011, 08:48 PM
  4. reading JSON data
    By hsfrey in forum JavaScript
    Replies: 0
    Last Post: 09-19-2009, 05:46 AM
  5. AJAX Routine and JSON
    By lgman in forum Dynamic Drive scripts help
    Replies: 0
    Last Post: 08-21-2007, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •