Laravel - Store JSON response in SQL database (via Guzzle)

Question

First post! New to php & Laravel, figured I'd learn by creating a test project. I've been following Laracasts for guidance but run into issues.

What I'm looking to achieve:

Utilise Guzzle to call for an API
Store response into mySQL database
Setup a route for this
Setup a schedule so the controller runs once a day on schedule (I can start a new thread if need be)

I've got a controller setup for the Guzzle & storing data. I've got a database created which works as intended. The route I'm unsure about though on what exactly needs to be done so I'm struggling here on how to actually run the controller and store the data in the database.

I'd appreciate if anyone could review my code if I've done anything wrong, and give some guidance on routes in context of this.

Controller

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use GuzzleHttp\Client;

class DataController extends Controller
{
    public function index()
        {
            $client = new Client(['base_uri' => 'https://api.ratings.food.gov.uk/ratings']);
            $response = $client->request('GET', [
                'headers' => [
                    'x-api-version' => '2',
                    'Accept'        => 'application/json'
                ]
            ]);
            $mydata = json_decode($response->getBody()->getContents(), true);
            $object = new Object();
            $object->ratingId = $mydata->ratingId;
            $object->ratingName = $mydata->ratingName;
            $object->ratingKey = $mydata->ratingKey;
            $object->ratingKeyName = $mydata->ratingKeyName;
            $object->schemeTypeId = $mydata->schemeTypeId;
            $object->save();
            Requests::insert($object);
        }
}
?>

Migration

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateRatingsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('ratings', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->timestamps();
            $table->integer('ratingId');
            $table->string('ratingName');
            $table->string('ratingKey');
            $table->string('ratingKeyName');
            $table->integer('schemeTypeId');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('ratings');
    }
}

API JSON Response Example


{
    "ratings": [
        {
            "ratingId": 12,
            "ratingName": "5",
            "ratingKey": "fhrs_5_en-gb",
            "ratingKeyName": "5",
            "schemeTypeId": 1,
            "links": [
                {
                    "rel": "self",
                    "href": "http://api.ratings.food.gov.uk/ratings/12"
                }
            ]
        },
        {
            "ratingId": 11,
            "ratingName": "4",
            "ratingKey": "fhrs_4_en-gb",
            "ratingKeyName": "4",
            "schemeTypeId": 1,
            "links": [
                {
                    "rel": "self",
                    "href": "http://api.ratings.food.gov.uk/ratings/11"
                }
            ]
        },
        {
            "ratingId": 10,
            "ratingName": "3",
            "ratingKey": "fhrs_3_en-gb",
            "ratingKeyName": "3",
            "schemeTypeId": 1,
            "links": [
                {
                    "rel": "self",
                    "href": "http://api.ratings.food.gov.uk/ratings/10"
                }
            ]
        }
}

can you explain to me what are you doing here Requests::insert($object); — Mohammed Aktaa
– Mohammed Aktaa, Commented Jul 27, 2019 at 13:57
@MohammedAktaa that's one part of the controller that I was unsure about. I picked it up from a guide and thought it may be needed for storing it into the database. Can I remove? — user10796238
– user10796238, Commented Jul 27, 2019 at 14:11
yes you can remove it because it won't work. and now can you explain to me what jso you want to insert it and where? — Mohammed Aktaa
– Mohammed Aktaa, Commented Jul 27, 2019 at 14:13
@MohammedAktaa Thanks, will do! In my code format, there's a JSON response that I obtained from hitting the API in Postman. I'm looking to store data into the SQL database I created (Migration code block). My table is called 'ratings' — user10796238
– user10796238, Commented Jul 27, 2019 at 14:18
yes you can save it as text and when you get it you can decode it as json like this $json=json_decode($rating->content); — Mohammed Aktaa
– Mohammed Aktaa, Commented Jul 27, 2019 at 14:22

Rehmat · Accepted Answer · 2019-07-27 17:50:27Z

You should leverage the power of Eloquent ORM. I've found some issues in your code. You need to remove unnecessary lines that you have written there in order to create an object. Considering that you have created the columns with same names as returned by the API response, and considering that your model name is Rating and it should be, here are my suggestions:

Your controller:

<?php
namespace App\Http\Controllers;

use Illuminate\Http\Request;
use GuzzleHttp\Client;
use App\Rating;

class DataController extends Controller
{
    public function index()
    {
        $client = new Client(['base_uri' => 'https://api.ratings.food.gov.uk/ratings']);
        $response = $client->request('GET', [
            'headers' => [
                'x-api-version' => '2',
                'Accept'        => 'application/json'
            ]
        ]);

        $mydata = json_decode($response->getBody()->getContents(), true);

        /* You don't need to create an object as you are already parsing the response as an array, so remove below lines */
        // $object = new Object();
        // $object->ratingId = $mydata->ratingId;
        // $object->ratingName = $mydata->ratingName;
        // $object->ratingKey = $mydata->ratingKey;
        // $object->ratingKeyName = $mydata->ratingKeyName;
        // $object->schemeTypeId = $mydata->schemeTypeId;
        // $object->save();

        Rating::create($mydata);
    }
}

And add make the columns fillable in your Rating model by adding a protected static $fillable property to your Rating model:

protected static $fillable = ['ratingId', 'ratingName', 'ratingKeyName', 'schemeTypeId'];

If above solution isn't the one you liked, then you need to either treat $mydata as an array, i.e. do $mydata['ratingId'] to get ratingId, not $mydata->ratingId or remove true argument from json_decode() to parse the response as an object, not an array.

Thanks Rehmat! I think based on what you've said, I'm going to hit the books again as it looks like I missed out big time on the Eloquent stuff that would help a lot here.

Collectives™ on Stack Overflow

Laravel - Store JSON response in SQL database (via Guzzle)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related