I have read almost all topics around about creating an URL from (or downloading) a blob in a computer, however, I need something different:
(1) to save a blob in own server into a JSON file, then
(2) to optimize the way to update a database using the data stored in the JSON.
I tried all variants of code below with no success
var blob = new Blob(response, { type: contentType });
var file = new File([blob], filename, {type: contentType, lastModified: Date.now()});
where the response was the parsedJSON from the code below.
Would it be better to already have a file with empty content in the server instead, open it and update it from the blob (then close it)?
My purpose is to download the response content from the AJAX query (from an API) onto my server (not into any client computer in browser) and then update the respective values in a database table.
The use case is as follows: I receive a price list (about 40,000 items) from an API using a cron then I have to update the product prices in the database on my server.
Let's look at the code below:
<div id="app">
<p style="text-align: center; margin-top: 50px;">
<i class="fa fa-spinner fa-spin" style="font-size: 15px;"></i><br>
please wait to load the list of items here...
</p>
</div>
<script>
var settings = {
"url": "https://www.notimportant/etc/",
"method": "GET",
"timeout": 0,
"headers": {
"GUID": "something"
},
};
$.ajax(settings).done(function(response) {
var parsedJSON = JSON.parse(response);
console.log(parsedJSON[0]); /* <= to see the structure of each object in the response array */
console.log('total items: ' + parsedJSON.length);
var template = `
<p>List of items</p>
{{#each this}}
<div>{{plusOne @index}} - Code: {{ProductCode}} - Name: {{ProductName}} - Price: {{ProductPrice}}</div>
{{/each}}
`;
Handlebars.registerHelper('plusOne', function(index) {
index++;
return index;
});
var show = Handlebars.compile(template);
$('#app').html(show(parsedJSON));
});
</script>
So far so good, I could check using Handlebars JS the results into a browser (and see also in console the object structure received from API). However my purpose is to use each object from the array parsedJSON to update values in a table. I could make something as
for(i = 0; i < parsedJSON.length; i++) { // use here $.post to send ProductCode, ProductName and ProductPrice to a php file where I execute a query to update each product into the database
var ProductCode = parsedJSON[i].ProductCode;
var ProductName = parsedJSON[i].ProductName;
var ProductPrice = parsedJSON[i].ProductPrice;
$.post("update_db.php", {code: ProductCode, name: ProductName, price: ProductPrice}, function(data, status){
console.log("Product " + ProductCode + " update = " + status);
});
}
alert("all products' update was finished");
however, that would create ~ 40,000 single queries into my database (which is not ok at all).
I would prefer instead to save the array in the server (to save the response blob, create a file from it, name it myJSONfile.json, then in php use $myfileaddress = $base + "myJSONfile.json", read it with $myfile = file_get_contents($myfileaddress), then $myJson = json_decode($myfile, true) and then update the database using foreach($array as $item) {// update each row in products table in database, etc. }
I doubt however that I could run a multi-query with 40,000 components (or a prepared statement that large, even if I largely increase the allowed memory - I would prefer not to do it). If I am wrong please explain me why.
For the moment, my code is just sending queries one by one, which is not something I would keep doing (I'd better use PHP only or JavaScript only in order to create a single connection to the db, not many as of now).
I actually receive an almost instant response - the alert all products' update was finished for full list of products, but then the PHP response messages keep coming in console, too, during some pretty long time about successfully updating each row (talk about few minutes) which isn't something I expected.
I would use something elegant, let's say to use 500 queries in a single transaction (and make the entire update in just 80 steps instead of a full 40,000 simple queries), but not sure how to set everything right.
My point in downloading the full JSON instead of updating each product through a separate API request (plus update) is to query the external server just one time (using a cron) instead of many times, and doing further processing of the data in my own server instead. In the real situation, each product comes from the API with about 20 parameters, not just 3 (Code, Name and Price), but that's not relevant for the issues in question. (Also, sorry for some small inadvertences in the code shown in separate snippets, but I typed quickly instead of any copy-paste from the production files - I'm not writing now from the computer where I work the project in question).
Final edit
I finally wrote the more compact code below which works just fine (prepared statement with UPDATE). Instead of posting it as a separate answer, I will insert it here:
<?php
$servername = "localhost";
$username = "someuser";
$password = "somepassw";
$dbname = "somedb";
// requiring data from API
$curl = curl_init();
curl_setopt_array($curl, array(
CURLOPT_URL => "https://www.notimportant/etc/",
CURLOPT_RETURNTRANSFER => true,
CURLOPT_ENCODING => "",
CURLOPT_MAXREDIRS => 10,
CURLOPT_TIMEOUT => 0,
CURLOPT_FOLLOWLOCATION => true,
CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
CURLOPT_CUSTOMREQUEST => "POST",
CURLOPT_POSTFIELDS =>"{\"name\": null, \"stores\": null, \"products\": []}",
CURLOPT_HTTPHEADER => array(
"GUID: something",
"Content-Type: application/json"
),
));
$response = curl_exec($curl);
curl_close($curl);
// echo $response;
$data = json_decode($response);
echo "Product count: " . count($data) . "<br>\n";
echo "Preparing to save products to database... <br>\n";
// end receiving data from API
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// prepare and bind
$stmt = $conn->prepare("UPDATE `my_table` SET `price` = ?, `stock` = ? WHERE `my_table`.`product_id` = ?");
$stmt->bind_param("dii", $price, $stock, $id);
// set parameters and execute
foreach($data as $productInfo) {
$price = $productInfo->RetailPrice;
$stock = $productInfo->Stock;
$id = $productInfo->ProductId;
$stmt->execute();
}
echo "Update was ok";
$stmt->close();
$conn->close();
?>
@Bravemaster suggestions were helpful, the code runs now in less than 10 seconds so it's an acceptable solution for the moment. Point (2) is pretty solved, and (1) seems to be not useful anymore (as it makes no sense to use both file_put_contents and file_get_contents). I also vote Bravemaster's input as the accepted answer - for his gracious contribution to my code. All 40k+ queries went smoothly in a single batch. What's left is to add some more data validation (to be in the safe side) and to set the cron process (not really an issue).
