So, I am trying to pull data from EA Sports API for games like NHL or FIFA. The issues with EAs API is it only shows the last 5 games played. I am trying to have a script run every minute and save the decoded json as individual fields in mySQL and removing duplicates. Can anyone help explain how to do running every so often and saving to mySQl without duplicates? API being pulled is here:
https://proclubs.ea.com/api/nhl/clubs/info?clubIds=35561&platform=xbox-series-xs
My approach would be to create Scheduler Task plugin to allow you to run it periodically and manage it from the Admin pages. The code in the task plugin could either do the steps to call the API, process the data and save to the record to the data base or it could simply call that same processing that is coded into your Model of your component. The latter is probably the better approach for code reuse reasons and keeping to the MVC rules.
I will assume that you can call the API and loop through the returned data to get the individual games so that just leaves you with storing the data in each iteration. Using the games unique ID as the primary key allows you to use the INSERT IGNORE statement in MySQL as suggested by @barrycarter. The short explanation of INSERT IGNORE is that it will add the record if the primary key is not found in the DB or ignore the insert if it does exists.
However Joomla core only provides the INSERT statement so if you want to use the normal Joomla approach to build your query you will need do a little extra step.
This is a method I use in my code to handle the situation that I call from anywhere, however the important line you need to run after the query has been created and before it is executed is the str_replace to change the statement.
/**
* Replaces the word INSERT with INSERT IGNORE in the provided string
*/
protected function _useInsertIgnore(string $query)
{
$query = str_replace("INSERT", "INSERT IGNORE", $query);
return $query;
}
To show the above function being called in context this is how I use it, but you will need to adjust it to suit your code.
$this->query->insert($this->table); // <-- After this line
$this->query->columns($columns);
$this->query->values(implode(',', $values));
$this->query = $this->_useInsertIgnore($this->query);
$this->db->setQuery($this->query); // <-- Before this line
