How to store API JSON response in MYSQL database-CodePudding

I'm using an API that provides the data in JSON format. I'm trying to store the JSON response in the MySQL database (just as it is)

and then refetch it from the database in JSON format. You may be wondering why I'm doing this, well, I'm using a paid API that has limited no. of requests. To prevent multiple API calls, I wanted to serve API responses through my server (So basically application users would be polling my server to fetch the JSON response Instead of directly calling an API)

So I created a table named "matchinfo" and there is a column named "jsondata" which has a type of LONGTEXT

$json_response = file_get_contents("api_url"); // storing json format response

$update_data = "UPDATE matchinfo SET jsondata = '$json_response'"; // Succesfully stored it

$update_query = mysqli_query($conn,$update_data); 


// how can I again fetch it in the JSON format

CodePudding user response：

I think your question is "How can I pull data with mysqli".

//connection
$mysqli = new mysqli("localhost", "user", "pass", "database");

//obtaining the jsondata.
$result = $mysqli->query("SELECT jsondata FROM matchinfo");
$row = $result->fetch_array(MYSQLI_ASSOC);

//The data is like file_get_contents("api_url");
$json_response = $row['jsondata']; //json string

//If you want to use data as array.
$json = json_decode($json_response, true); //array

CodePudding user response：

I guess what you are looking for is the function "json_decode".

You can use it like that:

$databaseValue = '{"field": "value"}';    // Example, this represents your string you have stored in your database
$fetchedJson = json_decode($databaseValue, true); // JSON Value

You need the true inside json_decode, because you want the result of the function to be a JSON array. If you'd put a false there, the result of the function will be an object.

You can read more about it in the php documentation.