Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    May 2013
    Posts
    45
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Update MySql via JSON

    I query something on mySql. The result is an array filled with JSONs, one for each row.

    Now, I'd like to take a JSON and do the opposite: update mySQL with my JSON in 1 line if possible.

    Right now I'm doing it by looping throught all attributes of my JSON but I'm sure there's a way to directly do it.

    Thanks.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    And we are supposed to guess what the JSON you are sending to the server looks like? And what your DB table(s) look like?

    We need tons more info.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    May 2013
    Posts
    45
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    And we are supposed to guess what the JSON you are sending to the server looks like? And what your DB table(s) look like?

    We need tons more info.
    All the attributes of the JSON fit with the ones of the DB. Actually, the JSON comes from the DB.

    Ex;
    SQL: name = sam ,x = 0,y = 0

    Export that and put all the info in a JSON:
    var sam = {name:"sam",x:0,y:0}

    Then I change sam.x = 1;

    Then I want to put the new updated JSON back to the DB.

    ###############

    If I'm planning to work that way, instead of having multiple columns with each attributes, should I use only 2 columns, 1 for username and 1 for all the data in a single string/JSON?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    But the big question: Will you update 1 or many records back in the DB?

    If only 1 record, then quite frankly who cares you you do it?

    And I might even suggest the sending data back to server via JSON isn't necessarily the best choice. But it's certainly workable.

    Assuming you only need to update one record, and assuming that the JSON is as simple as you showed it, I'd probably just do
    Code:
    $json = $_GET["json"]; // or POST of course
    $data = json_decode( $json, true ); // creates an associative array
    $sql = "UPDATE table SET x = " . ( (int) $data["x"] ) . ", y = " . ( (int) $data["y"] )
         . " WHERE name = '" . mysql_real_escape_string($data["name"]) . "' ";
    ...
    And, really, even if you had multiple records to update, I'd do it similarly. One record at a time in a loop. MySQL does understand multi-record updates base on distinct keys. (It could do multi-record INSERTs, though you don't gain a whole bunch.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    IdkWhatsRc (06-19-2013)

  • #5
    New Coder
    Join Date
    May 2013
    Posts
    45
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    But the big question: Will you update 1 or many records back in the DB?

    If only 1 record, then quite frankly who cares you you do it?

    And I might even suggest the sending data back to server via JSON isn't necessarily the best choice. But it's certainly workable.

    Assuming you only need to update one record, and assuming that the JSON is as simple as you showed it, I'd probably just do
    Code:
    $json = $_GET["json"]; // or POST of course
    $data = json_decode( $json, true ); // creates an associative array
    $sql = "UPDATE table SET x = " . ( (int) $data["x"] ) . ", y = " . ( (int) $data["y"] )
         . " WHERE name = '" . mysql_real_escape_string($data["name"]) . "' ";
    ...
    And, really, even if you had multiple records to update, I'd do it similarly. One record at a time in a loop. MySQL does understand multi-record updates base on distinct keys. (It could do multi-record INSERTs, though you don't gain a whole bunch.)
    Okay so from what I understand, there's no direct/built-in way to use a JSON to update MySQl, I simply need to loop through all attributes.

    Actually, I think I'll go with only 1 VARCHAR(8000) containing all the info of my player so I got far more freedom for its attributes and it's easier for me to set into an array or update it.

    Thanks anyway.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Quote Originally Posted by IdkWhatsRc View Post
    I think I'll go with only 1 VARCHAR(8000) containing all the info of my player so I got far more freedom for its attributes and it's easier for me to set into an array or update it.
    Well, that's the worst worst worst possible solution from a database standpoint.

    At this point in time, you are no longer really using a database, at all. You might as well store the strings in text files for all the good a database will do you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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