SELECT `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` FORCE INDEX (`id_product`) WHERE `id_product` = 235408123 ORDER BY `date`;SELECT `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` USE INDEX (`id_product`) WHERE `id_product` = 235408123 ORDER BY `date`;$sql = "SELECT DATE_FORMAT(date,'%d.%m.%Y') as date1, `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` WHERE `id_product` = ".$row_product['id_product']." ORDER BY `date`";$sql = "SELECT `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` WHERE `id_product` = ".$row_product['id_product']." ORDER BY `date` DESC";
$result = mysqli_query($link6,$sql);
$row1 = [];
while ($row2 = mysqli_fetch_assoc($result)) {
if (count($row1)) {
echo $row1['date'] . ' - ' . $row2['prevDate'] . ' - ' . $row1['price_product'] . "<br>";
}
$row1 = $row2;
}
if (count($row1)) {
echo $row1['date'] . ' - ' . date('Y-m-d') . ' - ' . $row1['price_product'] . "<br>";
}
DELIMITER $$
CREATE TRIGGER `update_price` before update ON `products`
FOR EACH ROW BEGIN
IF NEW.`price` != OLD.`price` THEN
INSERT INTO prices Set id_products = OLD.id_products , price_product = OLD.price, `date` = OLD.`date`;
END IF;
END;
$$
DELIMITER ;DELIMITER $$
CREATE TRIGGER `insert_price` AFTER INSERT ON `products`
FOR EACH ROW BEGIN
IF NEW.`price` != NEW.`price` THEN
INSERT INTO prices Set id_products = NEW.id_products , price_product = NEW.price, `date` = NEW.`date`;
END IF;
END;
$$
DELIMITER ;DELIMITER $$
CREATE TRIGGER `update_price` before update ON `products`
FOR EACH ROW BEGIN
IF NEW.`price` != OLD.`price` THEN
INSERT INTO prices Set id_products = OLD.id_products , price_product = OLD.price, `date` = OLD.`date`;
END IF;
END;
$$
DELIMITER ;while ($z->read() && $z->name !== 'offer');
$data = [];
while ($z->name === 'offer') {
//$node = new SimpleXMLElement($z->readOuterXML());
$node = simplexml_import_dom($doc->importNode($z->expand(), true));
$id_product = mysqli_real_escape_string($link6,$node['id']);
$url = mysqli_real_escape_string($link6,$node->url);
$price = $node->price;
$group = $node->categoryId;
$image = mysqli_real_escape_string($link6,$node->picture);
$name = mysqli_real_escape_string($link6,$node->name);
$vendor = mysqli_real_escape_string($link6,$node->vendor);
$vendorCode = mysqli_real_escape_string($link6,$node->vendorCode);
$description = mysqli_real_escape_string($link6,$node->description);
$barcode = mysqli_real_escape_string($link6,$node->barcode);
$data[] = '("'.$id_product.'","'.$vendorCode.'","'.$vendor.'","'.$barcode.'","'.$name.'","'.$description.'","'.$image.'","'.htmlspecialchars(urldecode($url)).'","'.$group.'","'.$today.'",'.(int)$price.')';
if (count($data) > 99) {
$sql = 'INSERT INTO `products` (`id_product`,`vendorCode`,`vendor`,`barcode`,`name`,`description`,`image_product`,`link_product`,`group`,`date`,`price`) VALUES '
. implode(',', $data)
. 'ON DUPLICATE KEY UPDATE `link_product` = VALUES(`link_product`), `price` = VALUES(`price`)';
echo $sql.'</br>';
mysqli_query($link6,$sql);
$data = [];
}
$z->next('offer');
}
if (count($data) > 0) {
$sql = 'INSERT INTO `products` (`id_product`,`vendorCode`,`vendor`,`barcode`,`name`,`description`,`image_product`,`link_product`,`group`,`date`,`price`) VALUES '
. implode(',', $data)
. 'ON DUPLICATE KEY UPDATE `link_product` = VALUES(`link_product`), `price` = VALUES(`price`)';
mysqli_query($link6,$sql);
}
}DELIMITER $$
CREATE TRIGGER `update_price` before update ON `products`
FOR EACH ROW BEGIN
INSERT INTO prices Set id_products = OLD.id_products , price_product = OLD.price, `date` = OLD.`date`;
END;
$$
DELIMITER ;CREATE TRIGGER `update_price` before update ON `products`
FOR EACH ROW BEGIN
INSERT INTO prices Set id_products = OLD.id_products , price_product = OLD.price;
END;
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "prices" "ALL" "id_product" \N \N \N "12975903" "Using where; Using filesort"