php-MySQL Full Text Search

nbk 2021-10-14 04:27:35

You can use extss but youtrr query actually gives back zero rows back

CREATE TABLE `table` (
  `ID` INTEGER,
  `detail_id` INTEGER,
  `detail_value` VARCHAR(20),
  `book_id` INTEGER
  ,
       FULLTEXT idx (detail_value)
);

INSERT INTO `table`
  (`ID`, `detail_id`, `detail_value`, `book_id`)
VALUES
  ('1', '11', 'Warszawa', '103'),
  ('2', '14', 'Grażyńskiego', '123'),
  ('3', '11', 'st.Warszawa m', '123'),
  ('4', '14', 'Michała Grażyńskiego', '222'),
  ('5', '11', 'Warszawa', '222'),
  ('6', '14', 'Katowicka', '103'),
  ('7', '9', '923', '23'),
  ('8', '12', '432424', '23'),
  ('9', '14', 'Przykładowa', '23'),
  ('10', '11', 'Warszawa', '23');

SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE)) 
AND EXISTS (SELECT 1 FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('*rażyń*' IN BOOLEAN MODE))

ID | detail_id | detail_value | book_id
-: | --------: | :----------- | ------:

SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE))

ID | detail_id | detail_value  | book_id
-: | --------: | :------------ | ------:
 1 |        11 | Warszawa      |     103
 3 |        11 | st.Warszawa m |     123
 5 |        11 | Warszawa      |     222
10 |        11 | Warszawa      |      23

SELECT 1 as test FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('*rażyń*' IN BOOLEAN MODE)

| test |
| ---: |

db<>fiddle here

changing the second query

SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE)) 
AND EXISTS (SELECT 1 FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('grażyń*' IN BOOLEAN MODE))

ID | detail_id | detail_value  | book_id
-: | --------: | :------------ | ------:
 1 |        11 | Warszawa      |     103
 3 |        11 | st.Warszawa m |     123
10 |        11 | Warszawa      |      23

SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE))

ID | detail_id | detail_value  | book_id
-: | --------: | :------------ | ------:
 1 |        11 | Warszawa      |     103
 3 |        11 | st.Warszawa m |     123
 5 |        11 | Warszawa      |     222
10 |        11 | Warszawa      |      23

SELECT 1 as test FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('grażyń*' IN BOOLEAN MODE)

| test |
| ---: |
|    1 |
|    1 |

db<>fiddle here

Ok as further constraint both have to have the same bookng _id

SELECT * FROM `table` t1 
WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE)) 
AND EXISTS (SELECT 1 
FROM `table` 
WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('grażyń*' IN BOOLEAN MODE)
AND book_id = t1.book_id)

ID | detail_id | detail_value  | book_id
-: | --------: | :------------ | ------:
 3 |        11 | st.Warszawa m |     123
 5 |        11 | Warszawa      |     222

db<>fiddle here

Pavvcio 2021-10-13 19:53:53

change rażyń for grażyń and now it will be works fine but only when you use part of these queries. So this query: SELECT * FROM table WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*grażyń*' IN BOOLEAN MODE)) AND (detail_id = 14 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE)); not work but when you use ONE MATCH...AGAINST, it work fine. Together it doesn't work.

nbk 2021-10-13 20:00:55

see the example with graintz of course it works dbfiddle.uk/…

Pavvcio 2021-10-13 20:10:37

OK, I see now. But there is one problem - I need to get only these results, where book_id is the same. So book_number=222 (and 123) contains phrase warszawa as city name (detail_id=11) and phrase grażyń as part of street name also.

nbk 2021-10-13 20:28:12

add another where condition see changed answer

Pavvcio 2021-10-13 20:43:21

That look's good but what about more parameters? For example detail_id = 9 as house number and detail_id = 12 as post code? I have 38 parameters as total, so I need universal solution.

MySQL Full Text Search

热门帖子

热门github