CODEWITHSUNDEEP
mysql
Wojtek
Wojtek

Reputation: 67

select and skip MySQL results

I have a database with information about games. There are categories assigned to each game in the form cat1, cat2, cat3. The structure of my table

games (
    id int not null auto_increment,
    name varchar (100) not null,
    ...
    cats varchar (200) not null,
    FULLTEXT INDEX fulltext (cats)
    primary key (id)
)engine=myisam charset=utf8;

To search for games from a specific category I use the query:

SELECT * FROM games WHERE MATCH(cats) AGAINST('action' IN NATURAL LANGUAGE MODE)

Now, how can I display games that do not contain the "action" category ? For example, I want to display all games that do not contain "action" words in the cats field.

Do you have any idea?

Upvotes: 0

Views: 59

Answers (2)

Szekelygobe
Szekelygobe

Reputation: 2695

Try this : 

SELECT * FROM games WHERE cats  NOT LIKE '%action%'

When I put multiple values in on field I try to separat them with special separators not comma.

Like this : ##cat1##cat2##cat3##

So when I need to search I can do this : 

SELECT * FROM games WHERE cats LIKE '%##action##%'

OR 

SELECT * FROM games WHERE cats NOT LIKE '%##action##%'

But as @Madhur Bhaiya sad, you need to restructure you'r data. This is not the optimal datastructure.

Upvotes: 1

aviya.developer
aviya.developer

Reputation: 3613

You can use the NOT operator:

SELECT *
FROM game
WHERE cat1 NOT IN ('action');

of in your case would probably be like this:

SELECT * 
FROM games 
WHERE NOT MATCH(cats) AGAINST('action' IN NATURAL LANGUAGE MODE)

documentation: https://www.w3schools.com/sql/sql_and_or.asp

Upvotes: 1

Related Questions