Skip database record if exists

Question

I need to import records from an API (TeamLeader API 'old one').

I am using an ajax request to loop 5 times through the API "pages". The API has about 400 records and the limit to request is 100 at a time.

Ajax Request:

<button id="ajax" type="button" class="btn btn-primary ajax" title="Ajax Request">
            Import
</button>
<div class="card-body">
    <div class="ajax-content"></div>
</div>



<script type="text/javascript">

        $('#ajax').click(function () {
            $('.ajax-content').html('');
            getAjax(0);
        });

        var cur = 0;
        function getAjax(cur) {
            $.ajax({
                url: 'import/'+cur, success: function (result) {

                    if(result == 0 && cur < 9)
                    {
                        getAjax(cur+1)
                    }else{
                        $('.ajax-content').html('<hr>Import finished. Records : ' + result)
                    }
                }
            })
        }
    </script>

Then i have the controller to add the records to the database. The idea is to add all the records to the database and skip the records that already are in there.

Trying my best to explain the idea of this all:

So you import the records
The ajax request loops 5 times through the api to get all the records
The records get added in the database and skips the records that already are in there with an unique id : 'TlId'

Controller:

/**
     * @Route("/import/{page}", name="import_company")
     */
    public function importCompany($page, LoggerInterface $logger, CompanyRepository $companyRepository): Response
    {
        $fields = array(
                "api_group" => ("XXXXX"),
                "api_secret" => ("XXXXX"),
                "amount" => ("100"),
                "pageno" => ($page),
            );

            $client = HttpClient::create();
            $response = $client->request('POST', "https://app.teamleader.eu/api/getCompanies.php", [
                'body' => $fields,
            ]);

            $content = $response->getContent();

            $results = json_decode($content, true);

            $id = $companyRepository->findAllByTlId();
            $ids = array_column($id, "TlId");

            $number = 0;

            foreach ($results as $item) {
                if (!in_array($item['id'], $ids)) {

                    $number ++;

                    $company = new Company();

                    $company
                        ->setTlId($item['id'])
                        ->setName($item['name'])
                        ->setWebsite($item['website'])
                        ->setType(null)
                        ->setBillingAddress($item['street']. ' ' . $item['number'])
                        ->setBillingZip($item['zipcode'])
                        ->setBillingTown($item['city'])
                        ->setEmail($item['email'])
                        ->setPhone($item['telephone'])
                        ->setFax($item['fax']);

                    $this->em->persist($company);

                }
                $this->em->flush();

                return new Response($number);
            }

So the problem is that the ajax loops 1 time and get 1 record. after that it stops and shows the amount of records he got '1'.

If i try it again there is this exeption and error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '6837317' for key 'xxx'

And the exeption that tried to insert the values that already are in there.

I think the problem is in de controller but i can't figure it out. Maybe someone can see the problem, Thanks!

this is bad approach, before inserting you should check if already exist, also your `flush` should be after `persist`, because if in array you flush will be executed — Zeljka, Feb 07 '20 at 09:36
Possibly this will help ? https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql?rq=1 — John Cogan, Feb 07 '20 at 09:37
@JohnCogan I think that TlId isnt id in database, because id shoudnt be defined bu us — Zeljka, Feb 07 '20 at 09:37

Noah Boegli · Answer 1 · 2020-02-07T10:36:11.153

1

The AJAX loops one time because you return inside the foreach loop. That causes only one row to be inserted in the database.

Move this line outside the foreach loop:

return new Response($number);

And this should do the trick for the issue with only one row being added.

For the SQL error, you are adding new rows without checking if a unique column value already exists. You should design some form of checking the unique row as mentionned by @Zeljka in the discussion below.

edited Feb 07 '20 at 10:36

answered Feb 07 '20 at 09:37

Noah Boegli

558
6
21

why to move outside loop? what if he doesnt have rows to insert, if item doent pass if condition? – Zeljka Feb 07 '20 at 09:44
The code above uses Doctrine. The row insertion is performed inside the if statement: `$this->em->persist($company);`. The `$this->em->flush();`,on the other hand, simply "validates" all the transactions. This "validation" should not be executed on every itteration but only once at the end. – Noah Boegli Feb 07 '20 at 09:48
when you call flush(), Doctrine sends all those insert queries to db at one time inside a loop(using a mechanizm like foreach(queries as query) { run->query.. }) In this case it will not take care about unique fields and he cant flush them at once – Zeljka Feb 07 '20 at 09:54
Yes, so why call insert on every itteration and not once at the end ? Anyway, the flush operation can either be done in our outside the foreach loop, does not make any difference. It just makes more sense to do it once at the end, which is the purpose of this mechanism. The `return new Response($number);` however, has to be outside the loop as it prevents more than one itteration to be performed, which is an issue stated in the question. – Noah Boegli Feb 07 '20 at 10:05
because before insert he needs to call the database to check if that row already exist and he needs to do it one by one – Zeljka Feb 07 '20 at 10:07
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/207407/discussion-between-zeljka-and-noah-boegli). – Zeljka Feb 07 '20 at 10:08

Skip database record if exists

1 Answers1