Consume External API in Laravel with Guzzle and Save into the Database

Question

I am using Laravel-5.8 in my server. I have this external endpoint given to me which I cannot alter:

https://api.studklm.net/students/allstudents

It is a GET Request and in JSON (application/json) format.

[
  {
    "Student Code": "S001",
    "Full Name": "Adeleke Omowale",
    "StudentClass": "JSS 1",
    "AcademicTerm": "First"
  },
    "Student Code": "S002",
    "Full Name": "James Smith",
    "StudentClass": "JSS 1",
    "AcademicTerm": "First"
  },
    "Student Code": "S003",
    "Full Name": "Haruna Muri",
    "StudentClass": JSS 2",
    "AcademicTerm": "First"
  }
]

Then, in my server I have this table:

 CREATE TABLE `students` (
`id` int NOT NULL auto_increment,
`registration_no` varchar(255) UNIQUE NOT NULL,
`full_name` varchar(255) UNIQUE NOT NULL,
`student_class` varchar(255) NOT NULL,
`academic_term` varchar(255) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I am writing a cron job that will save an external api into my database using Guzzle.

I checked the site below to read some information, but I got stuck along the way.

https://appdividend.com/2018/04/17/laravel-guzzle-http-client-example/

<?php

namespace App\Console\Commands;
use Illuminate\Console\Command;


use App\Student;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use GuzzleHttp\Exception\GuzzleException;
use GuzzleHttp;
use GuzzleHttp\Client;

class studentdataupdate extends Command {

 protected $signature = 'command:studentdataupdate';
 protected $description = 'Student Data Update';
 public function __construct() {
        parent::__construct();
    }

 public function handle() {  
 $client = new GuzzleHttp\Client();
 $res = $client->request('GET','https://api.studklm.net/students/allstudents');

        return $result->getBody(); 

        $clients = json_decode($result, true);

        foreach($clients as $client) {
            Student::updateOrCreate([
                'registration_no' => $client->Student Code,
                'full_name' => $client->Full Name,
                'student_class' => $client->StudentClass,
                'facademic_term' => $client->AcademicTerm
            ]);
        }              
    }
}

I want to achieve these results:

1. registration_no and full_name are unique, and should not allow duplicates

2. Some of the fields from the external api are not well formatted, there are spaces in between. For instance, Full Name and Student Code. How do I resolve this while trying to save into my database without error?

3. If Full Name or Student Code already exists, it updates. Else, it inserts the new record.

Answer 1

There are a couple of easy fixes for your updateOrCreate() method that may help you toward a solution.

First, you can pass strings as attributes in PHP by using curly braces. This comes in handy when there are issues like spaces, or you want to dynamically pass a variable with a string value.

// $client->{$string}
$client->{'Full Name'}

Second, updateOrCreate() accepts not one, but two arrays. The first is used to search for existing entries. The following example would update the database if the registration_no matches an existing entry, or create a new entry if not.

Student::updateOrCreate([
    'registration_no' => $client->{'Student Code'},
    // 'full_name' => $client->{'Full Name'}, // uncomment to use both
],
[
    'full_name' => $client->{'Full Name'}, // not required if uncommented above
    'student_class' => $client->StudentClass,
    'facademic_term' => $client->AcademicTerm
]);

EDIT: If you want to do registration_no OR full_name, you may have to search the values manually before updating, as updateOrCreate() uses an AND condition for searching when multiple fields are present.

Answer 2

1. Create a cron job for Laravel schedule

crontab -e

* * * * * cd /[path-to-your-laravel-project] && php artisan schedule:run >> /dev/null 2>&1

2. Add your console command to console/kernel.php

protected function schedule(Schedule $schedule)
{
     $schedule->command('command:studentdataupdate')->everyMinute(); 
}