Updating database with check

Hello,

I have one big database that I fill with an extreranl API. Importing data from XML in my databse work slow since its large amount of data but .. it works fine. Now I have to make upates (Since I dont want to drop database and inport every time when I want to insert new data). So I need to make Update database from my external API (wich provide me a ddata via XML). Need sugestion about method to first check if data exist in my database, if exist it should pass to next one... and import only that I dnot have in my databse.

For example: I have a database with 100 cars in it. Now lets say I have 20 new cars on my API and I need to get new cars in to the database. How to check for existing cars, and import only that I dont have in my database.

Thx



64.2k
edited Apr '16

I ussually do this my self...

$check = cars::find([
    conditions => " brand = ?0 AND model = ?1 AND modification = ?2 ",
    bind => [
        0 => "lamborghini",
        1 => "diablo",
        2 => "VT"
    ]
])->count();

if ($check === 0){
    $rec = new Cars([ MY XML DATA AS AN ARRAY WITH KEYS AS column name AND VALUE the value to enter]);

    $rec->save();
}

?

edited Apr '16
// find if car exists
$car = Cars::findFirst(...);

// if not create new
if (empty($car)) {
    $car = new Cars();
    $car->create($data);
}


9.3k
edited Apr '16

First thx on fast replay both, but the thing is the I have like 500 000 "cars" in my database with a lot columns ... Do you think this is going to work on that ?

Use DB indexes for search query and you will be fine and it will run fast



64.2k

my cars database has only ints and does work quite fast, if you used only strings, and performance of this is that big of a deal is apache solr or sphinx or so to search before updating/inserting



9.3k
edited Apr '16

Well the thing is the, I first load XML file with SimpleXML, get data and Insert it in my database (fill almost 500k cars)... Now I have to do same, load XML and then check for cars that are not in my database and import them ... Huh this will take a time Im sure ..

What do you guys think about Events and Events Manager to do some valiation there? Or idk ..

How are you searching the cars? By some unique key?



9.3k
edited Apr '16

How are you searching the cars? By some unique key?

Well curently im using slugs wich are provied from URL. So searching for a selected car looks like this (SEO guys wish)....

myproject.com/cars/aston-martin-v8
public function carAction($slug)
    {
        try {
            $carByName = Products::find([
                'slug = ?0',
                'bind' => [$slug]
            ]);
            $this->api->withCollection($carByName, new CarTransformer())->send();

        } catch(Exception $e) {
            $this->api->withException($e)->send();
        }

    }

But in database offcourse i have PK and FK everywehere (almost in all tables) .. But Im not sure if you mean on this? .. You may thinking about search when updating? Im working on that right now, because that Im asking what is best solution to do this ..

edited Apr '16

Then the fastest way how you can check if car already exists will be

  1. load all slugs from database and save them as hashed array $cars[$slug] = true;
  2. go through your XML and use only if (empty($cars[$slug])) create new car

This should be faster than doing 500 000 selects.

http://mtdowling.com/blog/2014/03/17/hash-lookups-over-array-search/



9.3k

Then the fastest way how you can check if car already exists will be

  1. load all slugs from database and save them as hashed array $cars[$slug] = true;
  2. go through your XML and use only if (empty($cars[$slug])) create new car

This should be faster than doing 500 000 selects.

I dont have slugs in my XML, Im making them when Im doing import... Transforming cars title in to the slugs nad insert in new column that i made for slugs (So basicly I transforming titles in slugs during the inport).

edited Apr '16

Still generating one slug and checking ist existence in array will be much faster than DB query, but do as you want, I am only giving proposals :)



9.3k

Still generating one slug and checking ist existence in array will be much faster than DB query, but do as you want, I am only giving proposals :)

Sure man, thx on advice... Il take take everything into consideration. If u rememeber anything else, just shoot it here.