1

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.stack.imgur.com/Kjncd.jpg

the updated table: https://i.stack.imgur.com/cxQJc.jpg

firefly
  • 876
  • 2
  • 15
  • 42

1 Answers1

2

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.

RamazG
  • 61
  • 4
  • yes you understand it very correctly mate. and also my unique identifier is the URLs that storing in a array. `$outlineUrls` Because each website has a different URL. – firefly Oct 12 '18 at 06:33
  • 1
    Is there anything else that you have a problem with ? – RamazG Oct 12 '18 at 07:20
  • actually mate, I couldn't managed it. I create a update table, and have urls. But couldn't manage the writing the codes under the my switch case. couldn't insert data first by "createorupdate" also the others... just want to update "price" column.... this is so challenging. – firefly Oct 12 '18 at 07:26
  • 1
    @KubilayTurgut please check discussion – Mehravish Temkar Oct 12 '18 at 07:40
  • 1
    I added a code guide if it's any help. If you only need to change price just change up my code to select just the price with entry id. It should work either way; – RamazG Oct 12 '18 at 07:44
  • Your specified way would hit the database too many times to fetch data and then update, right way but not efficient – Mehravish Temkar Oct 12 '18 at 07:45
  • 1
    Yes but the way you told him only can contain so many prices. What if the price changes 10 times ? you should't have 10 separate columns for 10 prices right ? – RamazG Oct 12 '18 at 07:50
  • thank you mate, and sorry for the burden I used your code. saving data but, when run the code again it gives this error. `Column not found: 1054 Unknown column updated_at in 'field list' (SQL: update allestates set` also I update the question for latest and put the pic of my databases – firefly Oct 12 '18 at 08:14
  • 1
    you can add `public $timestamps = false;` in your model or you can add `$table->timestamps();` to your table migration after the columns list – Mehravish Temkar Oct 12 '18 at 08:30
  • 1
    Hey, about the error i think Mehravish is right and, i changed up my answer to exactly fit your needs. Hope it helps. If you only update price you dont need a whole table copy. I explained it in the answer; – RamazG Oct 12 '18 at 08:37
  • Thank you mate, you really helped me out! :) – firefly Oct 12 '18 at 09:05
  • @RamazG and one last question. updated_estate table has the same price as the main table? that means if there is no update just taking the same price? – firefly Oct 12 '18 at 09:08
  • Yes, I did not take into account the case when you scrap the page and the price hasn't changed. you can add a simple if statement if $oldData['price'] == changeForMyDB['price'] And in case it's true just do not do anything. That's it – RamazG Oct 12 '18 at 13:03