0

I'm trying to do a full-text search in two separate tables and sort the results by relevancy. I am not getting a relevant data. below is my tables structure and my query which I am using - I have two tables "deals" and "outlets" now I want to do full text search as user can search with title only and with the address as well. Please see the tables and query

Table1:

CREATE TABLE `deals` (
  `id` int(11) NOT NULL,
  `outlet_store_id` int(6) DEFAULT NULL,
  `title` varchar(250) DEFAULT NULL,
  `description` text,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `deals` (`id`, `outlet_store_id`, `title`, `description`, `created_at`, `updated_at`) VALUES
(1, 1, 'Great Deal Flat 50% Off', '40% of on all the shoes', '2017-09-10 00:00:00', '2017-09-05 05:08:14'),
(5, 4, 'Flat 50% off', 'sdfsdf sfsdf ds', '2017-09-12 11:54:14', '2017-09-12 11:57:03');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `deals`
--
ALTER TABLE `deals`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `deals` ADD FULLTEXT KEY `title` (`title`);

**Table 2**
CREATE TABLE `outlet_stores` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `address` text,
  `longitude` varchar(30) DEFAULT NULL,
  `latitude` varchar(30) DEFAULT NULL,
  `status` tinyint(2) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `outlet_stores`
--

INSERT INTO `outlet_stores` (`id`, `name`, `address`, `longitude`, `latitude`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Polo-011', 'SCO-89,90,91, 1st Floor, Sector 17 D', '76.755181', '30.734015', 0, '2017-08-22 00:00:00', '2017-09-06 11:47:49'),
(3, 'Tommy Highflier', 'SCO 11-12, Sector 17D, Chandigarh', NULL, NULL, 1, '2017-09-05 11:09:48', '2017-09-05 11:09:48'),
(4, 'Kapson Store', 'Elante Mall, Chandigarh', NULL, NULL, 1, '2017-09-12 11:20:50', '2017-09-12 11:20:50');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `outlet_stores`
--
ALTER TABLE `outlet_stores`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `outlet_stores` ADD FULLTEXT KEY `address` (`address`);

What I want

There is only one input on my website and User can search with "Flat 50% off" and "Flat 50% off in elante mall" so after my query I am getting same records I want if user search with "flat" then it returns all the records and if user search with location then it return actual match and if there is no records then it returns empty but some suggestions suppose "Flat 50% off in elante mall" if there is no record then it search with flat 50% and returns some suggestion results.

My query which I am using

SELECT *, 
 MATCH(deals.title) AGAINST($q) as deals,
 MATCH(outlet_stores.address) AGAINST($q) as outlet_stores
FROM deals 
LEFT JOIN outlet_stores ON deals.outlet_store_id = outlet_stores.id  
WHERE 
 MATCH(deals.title) AGAINST($q)
 OR MATCH(outlet_stores.address) AGAINST($q);
Shiv Aggarwal
  • 499
  • 6
  • 14
  • What is `$q`? Is this query being created in PHP? – Barmar Sep 15 '17 at 06:49
  • The list after `AGAINST` has to contain quoted strings. Does the value of `$q` have quotes around it? – Barmar Sep 15 '17 at 06:50
  • $q is string which users send to sql and yes this string would be covered with "" means "Flat" – Shiv Aggarwal Sep 15 '17 at 06:53
  • Are you checking for errors when you do the query? Show your PHP code. – Barmar Sep 15 '17 at 06:59
  • bro there is o role of PHP I need only sql query for this and valid results – Shiv Aggarwal Sep 15 '17 at 07:06
  • Isn't `$q` a PHP variable? – Barmar Sep 15 '17 at 07:09
  • Yes $q is PHP variable but as I have mentioned you can add hard core in the place of $q SELECT *, MATCH(deals.title) AGAINST('"flast 50% off in elante"') as deals, MATCH(outlet_stores.address) AGAINST('"flast 50% off in elante"') as outlet_stores FROM deals LEFT JOIN outlet_stores ON deals.outlet_store_id = outlet_stores.id WHERE MATCH(deals.title) AGAINST('"flast 50% off in elante"') OR MATCH(outlet_stores.address) AGAINST('"flast 50% off in elante"'); – Shiv Aggarwal Sep 15 '17 at 07:12
  • OK, so what's the problem you're having? I can't understand the run-on sentence in the question. – Barmar Sep 15 '17 at 07:14
  • I need a relevant result but I am getting all the results – Shiv Aggarwal Sep 15 '17 at 07:16
  • By default words shorter than 4 characters aren't included in full text indexes, see https://stackoverflow.com/questions/14646738/set-new-value-for-ft-min-word-len-fulltext-in-mysql for how to change this. – Barmar Sep 15 '17 at 07:17
  • ALso, if you need to match punctuation, you need extra quotes around the word. – Barmar Sep 15 '17 at 07:18

0 Answers0