Доброй ночи, есть задача при аплоаде файла .xlsx нужно читать его построчно и каждую строку перед тем как добавить нужно проверять на наличие уже в базе.
Задачу реализовал, при аплоаде файл заливается в папку, дальше посылается команда
$out = shell_exec("php "exec/insertTablePrice.php" {$file_url} {$ignore_first_row} {$col_article} {$brand} {$col_name} {$col_description} {$col_images} {$col_group_price} {$col_price} {$cy} {$col_min_value} {$col_exchange} {$col_unit} {$col_delivery} {$col_active} {$col_category} {$col_comment} {$date} 2>&1");
тут вопросов нет, все работает, дальше сам файл insertTablePrice.php имеет такой код:
ini_set('memory_limit', '-1');
set_time_limit('0');
require_once __DIR__ . "/../../vendor/autoload.php";
require_once __DIR__ . "/../../component/Db.php";
$file_url = $argv[1];
$ignore_first_row = $argv[2];
$col_article = $argv[3];
$brand = $argv[4];
$col_name = $argv[5];
$col_description = $argv[6];
$col_images = $argv[7];
$col_group_price = $argv[8];
$col_price = $argv[9];
$cy = $argv[10];
$col_min_value = $argv[11];
$col_exchange = $argv[12];
$col_unit = $argv[13];
$col_delivery = $argv[14];
$col_active = $argv[15];
$col_category = $argv[16];
$col_comment = $argv[17];
$date = $argv[18];
$file_url = trim( $file_url );
$brand = trim( $brand );
$col_name = trim( $col_name );
$col_description = trim( $col_description );
$col_images = trim( $col_images );
$col_group_price = trim( $col_group_price );
$col_price = trim( $col_price );
$cy = trim( $cy );
$col_min_value = trim( $col_min_value );
$col_exchange = trim( $col_exchange );
$col_unit = trim( $col_unit );
$col_active = trim( $col_active );
$col_category = trim( $col_category );
$col_comment = trim( $col_comment );
$col_article = intval( $col_article );
$col_name = intval( $col_name );
$col_description = intval( $col_description );
$col_images = intval( $col_images );
$col_group_price = intval( $col_group_price );
$col_price = intval( $col_price );
$col_min_value = intval( $col_min_value );
$col_exchange = intval( $col_exchange );
$col_unit = intval( $col_unit );
$col_active = intval( $col_active );
$col_category = intval( $col_category );
$col_comment = intval( $col_comment );
if($col_article == 0) {
$col_article = "";
}
if($col_name == 0) {
$col_name = "";
}
if($col_description == 0) {
$col_description = "";
}
if($col_images == 0) {
$col_images = "";
}
if($col_group_price == 0) {
$col_group_price = "";
}
if($col_price == 0) {
$col_price = "";
}
if($col_min_value == 0) {
$col_min_value = "";
}
if($col_exchange == 0) {
$col_exchange = "";
}
if($col_unit == 0) {
$col_unit = "";
}
if($col_active == 0) {
$col_active = "";
}
if($col_comment == 0) {
$col_comment = "";
}
if($ignore_first_row == "yes") {
$row = 2;
} else {
$row = 1;
}
$db = Db::connectDB();
$filePath = realpath(__DIR__ . "/../../" . $file_url);
/*
* Обновляем статус всех прайсов на old
*/
$sql = "UPDATE `dk_prices` SET status = 'old', price = '0' WHERE brand = '$brand'";
$db->query($sql);
$reader = PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
foreach ($reader->getWorksheetIterator() as $line) {
$highestRow = $line->getHighestRow();
for($row; $row <= $highestRow; $row++) {
$article = $line->getCellByColumnAndRow($col_article, $row)->getValue();
$articleSearch = str_replace(' ', '', $article);
$name = $line->getCellByColumnAndRow($col_name, $row)->getValue();
$description = $line->getCellByColumnAndRow($col_description, $row)->getValue();
$images = $line->getCellByColumnAndRow($col_images, $row)->getValue();
$group_price = $line->getCellByColumnAndRow($col_group_price, $row)->getValue();
$price = $line->getCellByColumnAndRow($col_price, $row)->getValue();
$min_value = $line->getCellByColumnAndRow($col_min_value, $row)->getValue();
$exchange = $line->getCellByColumnAndRow($col_exchange, $row)->getValue();
$unit = $line->getCellByColumnAndRow($col_unit, $row)->getValue();
$delivery = $line->getCellByColumnAndRow($col_delivery, $row)->getValue();
$active = $line->getCellByColumnAndRow($col_active, $row)->getValue();
$category = $line->getCellByColumnAndRow($col_category, $row)->getValue();
$comment = $line->getCellByColumnAndRow($col_comment, $row)->getValue();
if(empty($article)) {
$article = "";
}
if(empty($name)) {
$name = "";
}
if(empty($description)) {
$description = "";
}
if(empty($images)) {
$images = "";
}
if(empty($group_price)) {
$group_price = "";
}
if(empty($price)) {
$price = "";
}
if(empty($min_value)) {
$min_value = "";
}
if(empty($exchange)) {
$exchange = "";
}
if(empty($unit)) {
$unit = "";
}
if(empty($delivery)) {
$delivery = "";
}
if(empty($active)) {
$active = "";
}
if(empty($category)) {
$category = "";
}
if(empty($comment)) {
$comment = "";
}
/*
* Если такой артикул и бренд существует, то обновляем данные
*/
$sql = "SELECT searchArticle and brand from `dk_prices` where searchArticle = ?s and brand = ?s";
$result = $db->query($sql, $articleSearch, $brand);
if(mysqli_num_rows($result) > 0) {
$sql = "UPDATE `dk_prices` SET ?u WHERE searchArticle = ?s and brand = ?s";
$user_data = array(
'name' => $name,
'description' => $description,
'images' => $images,
'group_price' => $group_price,
'price' => $price,
'cy' => $cy,
'date' => $date,
'min_value' => $min_value,
'exchange' => $exchange,
'unit' => $unit,
'delivery' => $delivery,
'active' => $active,
'category' => $category,
'comment' => $comment,
);
$db->query($sql, $user_data, $articleSearch, $brand);
} else {
/*
* Если такого бренда и артикула нету, то добавляем нововую строку
*/
$sql = "INSERT INTO `dk_prices` SET ?u";
$user_data = array(
'article' => $article,
'searchArticle' => $articleSearch,
'brand' => $brand,
'name' => $name,
'description' => $description,
'images' => $images,
'group_price' => $group_price,
'price' => $price,
'cy' => $cy,
'date' => $date,
'min_value' => $min_value,
'exchange' => $exchange,
'unit' => $unit,
'delivery' => $delivery,
'active' => $active,
'category' => $category,
'comment' => $comment,
'status' => 'new'
);
$db->query($sql, $user_data);
}
}
}
С файлом около 1.5мб работает все отлично. Пробовал залить файл 28 мб ничего не происходит. Решил напрямую через putty запустить уже с введенными данными после долгого ожидания выскакивает такая ошибка:
Warning: The use statement with non-compound name 'SafeMySQL' has no effect in /home/j/j3852609/site-test01.tk/public_html/component/Db.php on line 5
Warning: Error while sending QUERY packet. PID=39959 in /home/j/j3852609/site-test01.tk/public_html/vendor/colshrapnel/safemysql/safemysql.class.php on line 466
Fatal error: Uncaught exception 'Exception' with message 'SafeMySQL: MySQL server has gone away. Full query: [SELECT searchArticle and brand from `dk_prices` where searchArticle = '0' and brand = 'FIAT']' in /home/j/j3852609/site-test01.tk/public_html/vendor/colshrapnel/safemysql/safemysql.class.php:623
Stack trace:
#0 /home/j/j3852609/site-test01.tk/public_html/vendor/colshrapnel/safemysql/safemysql.class.php(483): SafeMySQL->error('MySQL server ha...')
#1 /home/j/j3852609/site-test01.tk/public_html/vendor/colshrapnel/safemysql/safemysql.class.php(140): SafeMySQL->rawQuery('SELECT searchAr...')
#2 /home/j/j3852609/site-test01.tk/public_html/admin/exec/text.php(177): SafeMySQL->query('SELECT searchAr...', '0', 'FIAT')
#3 {main}
thrown in /home/j/j3852609/site-test01.tk/public_html/vendor/colshrapnel/safemysql/safemysql.class.php on line 623
Я так понимаю тут слишком сильно нагрузило базу или класс, вопрос как решить эту проблему ?
Где то нашел ответ что нужно в цикле скидывать переменную $db и запускать заново
$db = null;
$db = new SafeMySql();
не помогло, может не так делал.
Средний
Простой