0

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

5
  • You may be asking too much here. A complete tutorial or specific task development is something that a Joomla professional should be paid for. Please edit your question to include all research and attempts you've made. Do your best to reduce the demand on volunteers in this community. The smaller the question requirements, the more likely you are to receive help.
    – mickmackusa
    May 22 at 13:39
  • Hey mickmack, that is understandable. So currently my thought is to create a script that will perform the following: CURL the API data and run it every so often with a CRON. IF the Game ID from that data is not == to another already in the database then it will INSERT into mySQL. I havent started to code this yet, I just dont know if that is the best way to do it or not?
    – Brandon
    May 22 at 14:29
  • 2
    Create a unique primary key for each game, and look into INSERT IGNORE May 22 at 15:15
  • The data linked to in your question only shows data for a team and nothing relating to a game as far as I can work see, eg. results, unless 35561 is some sort of unique game id which could be used per @barrycarter suggestion as the unique key.
    – Irata
    May 23 at 1:25
  • In your other questions you have flagged it as Joomla 4 environment so I would suggest you look at using the Task Scheduler that came in 4.1 rather than using CRON to make managing this available from the admin pages of the site rather than logging in to the host control panel.
    – Irata
    May 23 at 1:31

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.