MYSQL SELECT .. WHERE... AGAINST issue
I am having a problem with the FULLTEXT search on multiple values in MYSQL.
I need to search a column with multiple values and return only the rows that have all of the values.
I am running WAMP Windows 64 bit platform. I downloaded it three months ago.
phpMyAdmin (don’t know version) and MYSQL 5.7.9.
I have read a lot on the internet and watched more youtube videos then I should have, but they did have one thing in common. In their AGAINST() parameters (in there SELECT statements), they all had multiple values, and in some cases, they had the ‘+’ or ‘-‘ character in the front of each value. i.e. (‘+apple’ ‘-junk’ …) that states to return rows with ‘apple’ and if it has ‘junk’ in the same row, do not return that row.
The only thing I find of any help is in the MYSQL documentation and that is:
http://dev.mysql.com/doc/refman/5.7/en/select.html
that shows the ‘[WHERE where_condition]’
and searching that clause further, I found:
http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-fulltext_index-tables.html
None of it helped me with the '+' and '-' or how to have multiple values.
As a side note, when using phpMyAdmin and I enter any of the SELECT statements (below), I get errors listed to the left of where I entered the statement. The statements still executes and returns results. I am not sure if this is the reason I am having my problems.
Here is my schema for the TABLE and the INSERT statements.
-- phpMyAdmin SQL Dump
-- version 4.5.2
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Table structure for table `testing`
--
DROP TABLE IF EXISTS `testing`;
CREATE TABLE IF NOT EXISTS `testing` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`events` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `testing`
--
INSERT INTO `testing` (`id`, `events`) VALUES
(8, 'apple pie my word'),
(9, 'apple junk mail'),
(10, 'junk apple'),
(11, 'food'),
(12, 'this is a test'),
(13, 'junk car red'),
(14, 'big red apple');
--
-- Indexes for table `testing`
--
ALTER TABLE `testing` ADD FULLTEXT KEY `events` (`events`);
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Here are the SELECT statements with their result.
-- with this one, I got 4 ROWS returned as I expected.
-- ROWS(8, 9, 10 & 14) returned
SELECT * FROM `testing` WHERE MATCH(`events`) AGAINST ('APPLE' IN BOOLEAN MODE);
-- with the next four, I got NO ROWS returned. Does not make sense to me.
SELECT * FROM `testing` WHERE MATCH(`events`) AGAINST ('junk' 'apple' IN BOOLEAN MODE);
SELECT * FROM `testing` WHERE MATCH(`events`) AGAINST ('+junk' 'apple' IN BOOLEAN MODE);
SELECT * FROM `testing` WHERE MATCH(`events`) AGAINST ('-junk' '-apple' IN BOOLEAN MODE);
-- with the next one, I got ROWS (9 & 10) returned. Here it does make sense.
SELECT * FROM `testing` WHERE MATCH(`events`) AGAINST ('+junk' '+apple' IN BOOLEAN MODE);
-- with the next one, I got ROWS(8, 9, 10 & 14) returned. Should have only gotten 9 & 10.
SELECT * FROM `testing` WHERE MATCH(`events`) AGAINST ('junk' '+apple' IN BOOLEAN MODE);
-- with the next one, I got ROW (13) returned. This makes sense.
SELECT * FROM `testing` WHERE MATCH(`events`) AGAINST ('junk' '-apple' IN BOOLEAN MODE);
-- AND YET, when i put the '-' on 'junk' only, I get no rows returned ???
SELECT * FROM `testing` WHERE MATCH(`events`) AGAINST ('-junk' 'apple' IN BOOLEAN MODE);
There appears to be some sort of logic to what I see. I am not totally sure and do not want to make any mistakes.
Any help would be greatly appreciated.
Thank You Dan