MySQL Forums
Forum List  »  Newbie

MYSQL SELECT .. WHERE... AGAINST issue
Posted by: Dan Pahl
Date: July 15, 2016 05:46PM

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

Options: ReplyQuote


Subject
Written By
Posted
MYSQL SELECT .. WHERE... AGAINST issue
July 15, 2016 05:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.