JSON works from URL not from MySQL

Question

Ok, first of all, thanks for reading my question, I'll try and add as much information as possible. I'm currently making a leaderboard for a Garrysmod Gametype, I got an API key from the coders of the Gamemode and with that API key I can extract JSON encoded arrays using a STEAM_ID

Example: http://www.revoltgaming.com/api/?key=SENCORED_API_KEY&action=ajack_all&steamid=STEAM_0:0:5898349

If I use the code below everything works just fine:

$usr_url_everything = "http://www.revoltgaming.com/api/?key=114d788c3e40e91842f945f19c978e66&action=ajack_all&steamid=" . $_SESSION['STEAMID'];
$json_encoded = file_get_contents($usr_url_everything);
$json_decoded = json_decode($json_encoded, true);

But what I do is, when a user joins for the first time, I extract his JSON string and save it in a MySQL database:

$usr_url_everything = "http://www.revoltgaming.com/api/?key=114d788c3e40e91842f945f19c978e66&action=ajack_all&steamid=" . $_SESSION['STEAMID'];
$json_encoded = file_get_contents($usr_url_everything);
$query = "INSERT INTO vm_leaderboard (`txtUser`, `txtJson`) VALUES ('" . $_SESSION['STEAM64'] . "', '" . $json_encoded . "')";

But from the moment I try to DECODE the json string I pull from the database it just doesn't work, it return NULL. The json string I save in the database is identical as the string I get directly from the API page.

Here are the strings that get pulled from the database: http://www.ishots-cave.com/revolt_/lb_applejack.php

Just echo what you get from the database, and you'll probably see the answer to your question. — Zagor23
– Zagor23, Commented Jun 15, 2012 at 13:14
@Zagor23 The echo can be found here: ishots-cave.com/revolt_/lb_applejack.php (once for every row) and is identical to the one I pull from revoltgaming.com/api — Andries Verbanck
– Andries Verbanck, Commented Jun 15, 2012 at 13:17
@Waltzy The string is saved as 'TEXT BLOB' in the database $leaderboard = new Database; $query = "SELECT * FROM vm_leaderboard"; $leaderboard->query($query); while ($leaderboard->nextRecord()) { // Decode his json string $json_encoded = $leaderboard->Record['txtJson']; echo $json_encoded; echo json_decode($json_encoded, true, 2); — Andries Verbanck
– Andries Verbanck, Commented Jun 15, 2012 at 13:18
@AndriesVerbanck - added my comment as an answer to be accepted. You are welcome! — shadyyx
– shadyyx, Commented Jun 15, 2012 at 14:35

Andries Verbanck · Accepted Answer · 2012-06-15 15:22:59Z

2

So As I posted in comment, converting it to an answer as it was helpful - the JSON string needs to be sanitized before inserting into the DB:

$usr_url_everything = "http://www.revoltgaming.com/api/?key=&action=ajack_all&steamid=" . $_SESSION['STEAMID'];
$json_encoded = file_get_contents($usr_url_everything);
// sanitize the JSON string
$json_encoded = mysql_real_escape_string($json_encoded);
// insert in common way
$query = "INSERT INTO vm_leaderboard (`txtUser`, `txtJson`) VALUES ('" . $_SESSION['STEAM64'] . "', '" . $json_encoded . "')";

Enjoy!

edited Jun 15, 2012 at 15:22

Andries Verbanck

153 bronze badges

answered Jun 15, 2012 at 14:33

shadyyx

16.1k7 gold badges62 silver badges98 bronze badges

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

6 Comments

Andries Verbanck Over a year ago

I have tried your solution with TRUNCATE the table first so it could fully update and still it does not work

shadyyx Over a year ago

@AndriesVerbanck But in comment You replied that it seems to be validated :-) So where's the problem now?

Andries Verbanck Over a year ago

Yes the json is validated, but the script still won't work. I've worked around this problem now by updating the database directly from the URL using a CHRON job that fires every 24 hours.

Waltzy Over a year ago

You should accept an answer Andries, these people spent time helping you!

Andries Verbanck Over a year ago

@Waltzy Sorry, forgot about it. Done now

|

movabo · Accepted Answer · 2012-06-15 14:26:28Z

1

In the JSON file are backslashes and quotes which are probably changing your query. So try again the code as following:

$query = "INSERT INTO vm_leaderboard (`txtUser`, `txtJson`) VALUES ('" . $_SESSION['STEAM64'] . "', '" . mysql_real_escape_string($json_encoded) . "')";

answered Jun 15, 2012 at 14:26

movabo

4112 silver badges10 bronze badges

3 Comments

Andries Verbanck Over a year ago

It still seems to fail. Thanks for you help anyway.

movabo Over a year ago

Could you add to the line where you execute the query "or die(mysql_error());" and tell us the code? Or did you already and there isn't an error?

Andries Verbanck Over a year ago

The SQL never had any errors in all attempts to make this code work. Thank you for your time

Collectives™ on Stack Overflow

JSON works from URL not from MySQL

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related