Inserting and updating data, also keeping the old datas at the same time? By Laravel

Question

I am inserting data if there is none(no problem with this part). But if there are same datas. I also want to update that and if data updated. Then I want it to keep old datas. Because I will use those.

Facing some trouble after inserting part. Updating data and keeping the old ones.

Here is my inserting and updating codes;

$oldData= allestates::where('link',json_encode($outlineUrl))->addSelect('id' , 'price')->limit(1)->get()->toArray();

    if(!empty($oldData)) { 

        $oldData = (array)$oldData[0]; 

        $oldData['updated_estate_id'] = $oldData['id']; 

        unset($oldData['id']); 

        \DB::table('updated_estates')->insertGetId($oldData); 

        allestates::where('link',json_encode($outlineUrl))->update(['price' => $changeForMyDB['price'] ]);



    }else{ 

        allestates::insertGetId($changeForMyDB);
        $this->line('Data saved.');

    }

I am having this error now:

SQLSTATE[HY000]: General error: 1364 Field 'link' doesn't have a default value (SQL: insert into updated_estates (price, updated_estate_id) values (1648万円（1戸）～3298万円（1戸）, 1))

Also here is my database: the main table: https://i.sstatic.net/Kjncd.jpg

the updated table: https://i.sstatic.net/cxQJc.jpg

Answer 1

If i understand your question correctly, you need to insert data if there is none and update if it exists but you also want to save the data you had before updating right ?

First thing you need is an unique identifier ( a parameter by which you check if an entry exists or not), in your case I suppose it's company name, or ID or something.

You also need another table, for instance "updated_entries", with columns: ID | UPDATED_ENTRY_ID | PRICE | (MAYBE DATE); where you'll keep all the old data.

When you check whether you need to insert or update, if it's update first you select the data that you are going to update and insert it into the above mentioned "updated_entries" , and pass the entry's id as UPDATED_ENTRY_ID, so whenever you want to see update history for a certain entry you just select it by UPDATED_ENTRY_ID.

You could do the above described like this:

$oldData= Estates::where('link',json_encode($outlineUrl))->addSelect('id' , 'price')->limit(1)->get()->toArray();
//so now old data only contains ID and PRICE

if(!empty($oldData)) { // If an entry exists you need to update it;

  $oldData = (array)$oldData[0]; // You can't converts ->first() to an array so this is the only way I found;

  $oldData['updated_entry_id'] = $oldData['id']; // You save id of the entry you are going to update;

  unset($oldData['id']); // You don't need to insert this entry in saving table by it's old ID, you already stored it in 'updated_entry_id';

  \DB::table('updated_entries')->insertGetId($oldData); // You save your old data and now you can proceed to updating;

 //And if you only need to update price just do it like this: 
Estates::where('link',json_encode($outlineUrl))->update(['price' => $changeForMyDB['price'] ]);

  //Or you can update the whole row like this: 
  //Estates::where('link',json_encode($outlineUrl))->update($changeForMyDB);

}else{ // If you don't have any entries you just insert a new one;

  Estates::insertGetId($changeForMyDB);
  $this->line('Data saved.');

}

• edited code to only save prices;
• thus you dont need to have any other parameters in your second table("updatedestates") it only needs: ID, UPDATED_ENTRY_ID, PRICE;

After this you can easily update your table and be sure that every old instance of that entry is saved.