fb_pixel

Parsing a CSV file in MySQL database using PHP (mysqli). How to set the encoding and condition of the selection of elements before entering into the database?

Development | Databases
Description
Input data.So, every day from 1C: Alpha-Auto(special edition 1C: Enterprise 8.3 for SRT and other auto-organizations) a CSV file is downloaded to a specific folder on the hosting(regular shared, not VDS): <

"Parameter 1","Parameter 2","Parameter 3","Parameter 4","Parameter 5"
"Parameter N","Parameter N","Parameter N","Parameter N","Parameter N"
...

This is a base of spare parts/parts in stock(q.&Gt;0).To parse a file and convert its values ​​into an array(for later use), I use the following code:

$file=file_get_contents('/home/user/site .ru/public_html/upload/catalog.csv');
$lines=explode(PHP_EOL, $file);
$array=array();
foreach($lines as $line) {
  $array[]=str_getcsv($line);
}


This is where the first plug appears.According to the good old tradition, 1C is all encoded in CP1251(or KOI8-R), and, instead of Cyrillic, there are only some crackers in the $array array.Also, in subsequent attempts to make INSERT these values ​​in the database, mysqli does not understand them and gives errors.

Is it possible to somehow save this CSV file or convert it on the fly to UTF-8 format(without BOM, for example) before parsing? Yes, there is Notepad ++, but doing it every day by hand is simply not physically human-resourceing, and I don’t want it anyway, though.

Good.Moving on!

To add values ​​to the database, I write like this:

//Init MySQLi.
$mysqli=mysqli_init();
//Connect to DB.
if(! $mysqli->real_connect('localhost', $login, $password, $table)) die('Connection error('.mysqli_connect_errno().')'.mysqli_connect_error());
//Loop INSERT.
foreach($array as $row) {
  if(! $mysqli->query("INSERT INTO catalog(id, name, brand, stock, price) VALUES('$row[0]','$row[1]','$row[2]','$row[3]','$row[4]')")) echo" Error(".$mysqli->errno.")".$mysqli->error;
}
//Close MySQLi connection.
$mysqli->close();

(Table ID cell has UNIQUE and PRIMARY values)

On successful days, this file weighs about 500 Kb(~ 4000 items of the nomenclature).This is the second plug-in, because to load each time anew such quantity, even if it is late at night by Cron, in MySQL it is not good(IMHO).Therefore, the question is: how best to compare each line(array element) from a CSV file with existing lines in the database and enter only those values ​​that have been changed(the main, logical CSV file)?

At the same time, you need to somehow specify the options:
1.If the ID is in the database, but it is not in the CSV file, then do the DELETE element;
2.If there is no such ID in the database, but it is in the CSV file, then do the INSERT, otherwise, skip the element;
3.If such an ID exists both in the database and in the CSV file, then do UPDATE, otherwise, skip the element.

I hope for your help.Thank you in advance!
Attachments
No attachments
Info
Description
By coding php.net/manual/ru/function.iconv.php

500 Kb

to load each time again such a number, even if it is late at night by Cron, in MySQL is not good


This is a meager amount, why not good? Especially if only once a day.

And yes, use at least PDO

on April 29th, 2020 (10:21 pm)
All coments
Nice
on May 14th, 2020 (11:39 pm)