*************

SQL code: Upload dump 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);

SQL Server: Preserve result

Result: Copy Clear

PHP code: Copy Format Clear

<?php $start_time = microtime(true); $item = 'geladeira-mini'; $location = 'sao-pedro-sp'; $route_template = '{item}-em-{location}'; $location_sep = str_replace(['{item}', '{location}'], '', $route_template); $route = str_replace(['{item}', '{location}'], [$item, $location], $route_template); $route_terms = explode('-', $route); //print_r($route_terms); //exit; $terms = explode($location_sep, $route); echo "\n-- Route --\n{$location_sep}\n{$route_template}\n{$route}\n\n\n-- Terms --\n"; print_r($terms); $query = "SELECT * FROM `db_keyword` WHERE MATCH (`keyword`) AGAINST ('{$route}' IN NATURAL LANGUAGE MODE);"; 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) { $route_items = explode('-', $row['keyword']); echo "{$row['route']} => {$row['keyword']}\n"; /*if (!isset($attribute[$row['key']]) && in_array($row['keyword'], $terms, true)) { $attribute[$row['key']] = $row['value']; $attribute[$row['route']] = $row['keyword']; }*/ //if (!isset($attribute[$row['key']]) && in_array($row['keyword'], $terms, true)) { //if (!array_diff($route_terms, $route_items)) { if (!array_diff($route_items, $route_terms)) { $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);

PHP version:

Show: Warnings Notice

Result: Copy Clear

PHPize Online / SQLize Online / SQLtest Online