query("UPDATE " . $items_table . " SET update_ready = 0");exit();
$starttime = microtime(true);
// Get items
$query = "
SELECT
id, number, name
FROM
" . $items_table . "
WHERE
update_ready = 0
-- number = '287513'
LIMIT
200
";
$rsItems = $mysqli->query($query);
$items2go = array();
while ($I = $rsItems->fetch_object()) {
$items2go[$I->number] = $I;
$item_ready_ids[] = $I->id;
}
// Get machine associations
$query = "
SELECT
anr, bezeichnungoriginal, bezeichnung1, nr
FROM
import_geraetezuordnung
WHERE
anr IN ('" . implode("','", array_keys($items2go)) . "')
";
$rsAssocs = $mysqli->query($query);
$bytesProcessed = 0;
while ($A = $rsAssocs->fetch_object()) {
if (isset($items2go[$A->anr])) {
if (!isset($items2go[$A->anr]->attributes) || !is_array($items2go[$A->anr]->attributes)) {
$items2go[$A->anr]->attributes = array();
}
foreach ($A as $fieldName => $fieldValue) {
if (!in_array($fieldName, $ignore_fields)) {
// echo $fieldValue, '
';
$searchWord = utf8_encode(str_replace(' ', '_', strtolower($fieldValue)));
$searchWord = preg_replace(
"@[^aA-zZ0-9_äÄüÜöÖß]@sui", '', $searchWord
);
$searchWord = utf8_decode(str_replace('_', ' ', $searchWord));
$items2go[$A->anr]->attributes[$searchWord] = $searchWord;
$bytesProcessed += strlen($searchWord);
}
}
}
// Way too slow; key = value instead
//$items2go[$A->anr]->attributes = array_keys($items2go[$A->anr]->attributes);
//$items2go[$A->anr]->attributes = array_unique($items2go[$A->anr]->attributes);
}
echo $bytesProcessed, 'b
';
// Make inserts for tmp table
$inserts = array();
foreach ($items2go as $itemNo => $itemData) {
if (isset($itemData->attributes) && is_array($itemData->attributes) && count($itemData->attributes) > 0) {
$inserts[] = "(" . $itemData->id . ", ' " . implode(' ', $itemData->attributes) . " ')";
}
}
// Create temp table for multi updates as a cache
$mysqli->query("
CREATE TEMPORARY TABLE
tmp_items_updates (
id INT,
text MEDIUMTEXT
)
");
// Insert changes into temp table
$insertQuery = "
INSERT INTO
tmp_items_updates (id, text)
VALUES
" . implode(', ', $inserts) . "
";
$mysqli->query($insertQuery);
// Update items
$updateQuery = "
UPDATE
" . $items_table . " i,
tmp_items_updates i_tmp
SET
i." . $attribute_field . " = i_tmp.text
WHERE
i.id = i_tmp.id
AND
i_tmp.id IS NOT NULL
";
$mysqli->query($updateQuery);
// Mark items as ready
if (count($item_ready_ids) > 0) {
$readyQuery = "
UPDATE
" . $items_table . "
SET
update_ready = 1
WHERE
id IN (" . implode(',', $item_ready_ids) . ")
";
$mysqli->query($readyQuery);
}
// Get items count to go
$queryCount = "
SELECT
COUNT(id) AS cnt
FROM
" . $items_table . "
WHERE
update_ready = 0
";
$rsCount = $mysqli->query($queryCount);
echo 'Items to go: ', $rsCount->fetch_object()->cnt, '
';
echo microtime(true) - $starttime;
?>