Level 102
Is it the same value for all elements? Or different for each?
If its different you will need to build up a huge case switch in sql. Here is an example: https://stackoverflow.com/a/25674827/1305117
Dec 11, 2020
9
Level 4
Get collection from DB, update items in collection and save back to DB (but vanilla PHP)
I'm processing XML and updating MySQL (vanilla PHP).
I would like to do it in 2 MySQL queries (GET ALL DATA -> (edit whole set) -> SAVE ALL DATA BACK)
$allData = mysqli_query($mysqli, "select * from orders'");
$xml = simplexml_load_file('basic.xml');
$list = $xml->order;
//loop XML
for ($i = 0; $i < count($list); $i++) {
//find order inside $allData by record_id
//if status changed, update in $allData
//if doesn't exists, insert new row in $allData
}
//delete from DB rows that weren't found in XML
//save $allData back to database
In Laravel words get collection from DB, update stuff inside and then save it back to DB.
Can you get me started?
Level 4
@sinnbeck What if I used Laravel eloquent. Would my situation be easier with Product::get();
Level 102
No it still runs sql statements unde the hood, so you would still need to set up a case statement. How many records needs to be updated and how often?
Level 4
400 - 800 records every 20 seconds
Level 4
Do you have a different solution?
Level 102
Would it be possible to delete the records and insert them again?
- Get all records
- Change the records in memory
- Delete records in db
- Insert changed records as new records
Level 4
I could run this, process whole XML and save it at the end with single query:
$xml = simplexml_load_file('basic.xml');
$list = $xml->order;
for ($i = 0; $i < count($list); $i++) {
$order = $order . "('$ID', '$order_id', '$order_vs'....................
}
mysqli_query($mysqli, "TRUNCATE TABLE `orders`");
$queryOrders = "INSERT INTO orders (`id`, `order_id...............) VALUES " . $order;
mysqli_query($mysqli, "$queryOrders") or die($mysqli->error);
Which satisfies:
- insert new into DB
- delete old from DB
But doesn't:
- if order_status in XML changes from NEW to COMPLETED save in DB date_completed
Level 4
Level 4
@sinnbeck I just need secondary table connected by order_id, that will hold last STATUS and COMPLETED_DATE.
After truncate and insert new data into DB I will check what have changed.
Right?
Please or to participate in this conversation.