PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE `db_keyword` ( `keyword_id` int(11) UNSIGNED NOT NULL, `type` enum('host','path') NOT NULL, `route` enum('location/location','product/product') NOT NULL, `key` varchar(191) NOT NULL, `value` varchar(191) NOT NULL, `name` varchar(255) NOT NULL, `keyword` varchar(191) NOT NULL, `group` varchar(191) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ALTER TABLE `db_keyword` ADD PRIMARY KEY (`keyword_id`), ADD KEY `type` (`type`), ADD KEY `route` (`route`), ADD KEY `key` (`key`), ADD KEY `value` (`value`), ADD FULLTEXT `keyword` (`keyword`); ALTER TABLE `db_keyword` MODIFY `keyword_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12; INSERT INTO `db_keyword` (`keyword_id`, `type`, `route`, `key`, `value`, `name`, `keyword`, `group`) VALUES (1, 'path', 'product/product', 'product_id', '1', 'ID', 'smartphone', '0'), (2, 'path', 'product/product', 'product_id', '2', 'ID', 'ventilador', '0'), (3, 'path', 'product/product', 'product_id', '3', 'ID', 'geladeira', '0'), (4, 'path', 'product/product', 'product_id', '4', 'ID', 'geladeira-frost-free', '0'), (5, 'path', 'location/location', 'location_id', '1', 'ID', 'sao-paulo-sp', '0'), (6, 'path', 'location/location', 'location_id', '2', 'ID', 'americana-sp', '0'), (7, 'path', 'location/location', 'location_id', '3', 'ID', 'campinas-sp', '0'), (8, 'path', 'location/location', 'location_id', '4', 'ID', 'sao-pedro-sp', '0'), (9, 'path', 'product/product', 'product_id', '5', 'ID', 'geladeira-mini', '0'), (10, 'path', 'product/product', 'product_id', '6', 'ID', 'geladeira-minis', '0'), (11, 'path', 'product/product', 'product_id', '7', 'ID', 'geladeira-duplex', '0'); #SELECT * FROM `db_keyword` WHERE MATCH (`keyword`) AGAINST ('smartphone-em-sao-paulo-sp' IN NATURAL LANGUAGE MODE);
Copy Clear
Copy Format Clear
<?php $start_time = microtime(true); $item = 'geladeira-mini'; $location = 'sao-pedro-sp'; $route_template = '{item}-em-{location}'; $route = str_replace(['{item}', '{location}'], [$item, $location], $route_template); $route_items = explode('-', $route); echo "-- Route --\n{$route}\n"; $query = "SELECT * FROM `db_keyword` WHERE MATCH (`keyword`) AGAINST ('{$route}' IN NATURAL LANGUAGE MODE) ORDER BY `keyword` DESC;"; echo "\n-- Query --\n{$query}\n\n"; $result = $mysqli->query($query); $rows = $result->fetch_all(MYSQLI_ASSOC); $result->free_result(); $mysqli->close(); echo "-- Found --\n"; $attribute = []; foreach ($rows as $row) { echo "{$row['route']} => {$row['keyword']}\n"; if (!isset($attribute[$row['key']]) && !array_diff(explode('-', $row['keyword']), $route_items)) { $attribute[$row['key']] = $row['value']; $attribute[$row['route']] = $row['keyword']; } } echo "\n-- Match --\n"; print_r($attribute); $end_time = microtime(true); echo "\n-- Time --\n" . ($end_time - $start_time);
Show:  
Copy Clear