Update table mysql via PHP
Bonjour;
J'ai un problème de remplissage de table sql via php.
Pour les autres tables , elle prennent bien en compte les mise à jour.
En gros je parse des fichiers xlsx via php et j'ai une classe update pour faire les mise à jour.
La structure de la table est :
table lru(ID_LRU, LRU) ==>(int Auto_INCREMENT, varchar(40))
mon code php qui contientles requete est le suivant:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
| <?php
namespace Floose\DAO;
use Floose\DAO\DataBaseConnect;
class LRUDAO{
private $_db;
public function __construct() {
$this->setDb();
}
public function setDb() {
$cnx= new DataBaseConnect();
$this->_db = $cnx->run();
}
public function save(\Floose\Parse\LRU $LRU){
return $this->_db->query("INSERT INTO lru (`ID_LRU`, `LRU`) VALUES (NULL, '".$LRU->getLRU()."');");
}
public function saveAll(array $LRUdao) {
$count=0;
foreach ($LRUdao as $LRU) {
if($this->save($LRU))
$count++;
}
return $count;
}
public function update(Floose\Parse\LRU $LRU) {
}
public function delete($id) {
}
public function findByID($id) {
}
} |
La fonction de parsing est la suivante, en fait deux fonction parce que je parse deux fichiers, presque similaire:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
| public function parseEquipement($filePath = null) {
set_time_limit(0);
//$listEquipement = [];
$listEquipement = [];
$count = 0;
$chunkSize = 1024;
$objReader = PHPExcel_IOFactory::createReader(PHPExcel_IOFactory::identify($filePath));
$spreadsheetInfo = $objReader->listWorksheetInfo($filePath);
$chunkFilter = new \Floose\Parse\ChunkReadFilter();
$objReader->setReadFilter($chunkFilter);
$objReader->setReadDataOnly(true);
$chunkFilter->setRows(0, 1);
$objPHPExcel = $objReader->load($filePath);
$totalRows = $spreadsheetInfo[0]['totalRows'];
for ($startRow = 1; $startRow <= $totalRows; $startRow += $chunkSize) {
$chunkFilter->setRows($startRow, $chunkSize);
$objPHPExcel = $objReader->load($filePath);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, null, true, false);
$startIndex = ($startRow == 1) ? $startRow : $startRow - 1;
if($dataToAnalyse[0][0]==NULL){
break;
}
if (!empty($sheetData) && $startRow < $totalRows) {
$dataToAnalyse = array_slice($sheetData, $startIndex, $chunkSize);
for ($i = 0; $i < $chunkSize; $i++) {
if ($dataToAnalyse[$i]['0'] != NULL) {
//$listEquipement[] = new Article($dataToAnalyse[$i]['0'], '', $dataToAnalyse[$i]['1']);
$listEquipement[] = new LRU(NULL, $dataToAnalyse[$i]['0']);
$count++;
}
}
}
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel, $sheetData);
}
return $listEquipement;
print_r($count);
}
public function parseEquipment($filePath = null) {
set_time_limit(0);
$listEquipment = [];
//$listEquipmentLRU = [];
$count = 0;
$chunkSize = 1024;
$objReader = PHPExcel_IOFactory::createReader(PHPExcel_IOFactory::identify($filePath));
$spreadsheetInfo = $objReader->listWorksheetInfo($filePath);
$chunkFilter = new \Floose\Parse\ChunkReadFilter();
$objReader->setReadFilter($chunkFilter);
$objReader->setReadDataOnly(true);
$chunkFilter->setRows(0, 1000);
$objPHPExcel = $objReader->load($filePath);
$totalRows = $spreadsheetInfo[0]['totalRows'];
for ($startRow = 1; $startRow <= $totalRows; $startRow += $chunkSize) {
$chunkFilter->setRows($startRow, $chunkSize);
$objPHPExcel = $objReader->load($filePath);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, null, true, false);
$startIndex = ($startRow == 1) ? $startRow : $startRow - 1;
if($dataToAnalyse[0][0]==NULL){
break;
}
if (!empty($sheetData) && $startRow < $totalRows) {
$dataToAnalyse = array_slice($sheetData, $startIndex, $chunkSize);
for ($i = 0; $i < $chunkSize; $i++) {
if ($dataToAnalyse[$i]['0'] != NULL) {
//$listEquipment[] = new Article($dataToAnalyse[$i]['2'], $dataToAnalyse[$i]['3'], $dataToAnalyse[$i]['5']);
$listEquipment[] = new LRU(NULL, $dataToAnalyse[$i]['1']);
$count++;
}
}
}
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel, $sheetData);
}
print_r($count);
return $listEquipment;
// echo(array_slice($sheetData, $startIndex, $chunkSize));
} |
Ainsi qu'une petite fonction dans le controlleur pour importer les fichiers selon leurs PATH
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
| $app->get('/admin/update/article', function() use ($app) {
$parse = new Floose\Parse\Parse();
$filePathEquipement = __DIR__ . "/../../DATA/UpdateFiles/Equipement.xlsx";
$filePathEquipment = __DIR__ . "/../../DATA/UpdateFiles/equipment.xlsx";
$equipement = $parse->parseEquipement($filePathEquipement);
$equipment = $parse->parseEquipment($filePathEquipment);
//$updateArticle = new Floose\DAO\ArticleDAO();
$updateLRU = new Floose\DAO\LRUDAO();
$countEquipementLRU = $updateLRU->saveAll($equipement);
$countEquipmentLRU = $updateLRU->saveAll($equipment);
//$countEquipementArticle = $updateArticle->saveAll($equipement['1']);
//$countEquipmentArticle = $updateArticle->saveAll($equipment['1']);
$stat = stat($filePathEquipement);
$dateImport = date(d . '-' . m . '-' . Y, $stat['mtime']);
$date = new DateUpdate(null, "Equipement", $dateImport, getdate()['mday'].'-'.getdate()['mon'].'-'.getdate()['year']);
$dateDAO = new DateUpdateDAO();
$dateDAO->save($date);
$stat = stat($filePathEquipment);
$dateImport = date(d . '-' . m . '-' . Y, $stat['mtime']);
$date = new DateUpdate(null, "equipment", $dateImport, getdate()['mday'].'-'.getdate()['mon'].'-'.getdate()['year']);
$dateDAO = new DateUpdateDAO();
$dateDAO->save($date);
$result = '<div class="alert alert-success alert-dismissable">
<button type="button" class="close" data-dismiss="alert" aria-hidden="true">×</button>
<h4> <i class="icon fa fa-check"></i> "LRU" update finished successfully!</h4> ' . ($countEquipementLRU + $countEquipmentLRU). ' Article(s) has been saved/modified.<br/>
' . $countEquipementLRU . ' --> Equipement.xlsx<br/>
' . $countEquipmentLRU . ' --> Equipement.xlsx
</div>';
return $result;
}); |
