Search code examples
mysqlsqlmatch-against

MySQL MATCH() AGAINST()

I want to find all records from database where date is "2013-04".

I use:

SELECT date, pastabos 
FROM aktai 
WHERE MATCH(uzsakovas) AGAINST ('Statyba ir Architektūra, UAB' IN BOOLEAN MODE) 
  AND MATCH(date) AGAINST ('2013-04*' IN BOOLEAN MODE)

but it does not give the correct answer, because I get records with date equal to '2013-01-29', '2013-03-28'.

My question is how to use the MATCH() AGAINST() like function LIKE() with "2013-04%" ?

Solution

  • Since the data type of date is Date why are you using text search? Just search on the date:

    SELECT date, pastabos 
FROM aktai 
WHERE MATCH(uzsakovas) AGAINST ('Statyba ir Architektūra, UAB' IN BOOLEAN MODE) 
  AND year(date) = :year and  month(date) = :month;

    where :year and :month are integer parameters with values 2013 and 04 respectively.

    Alternatively

    SELECT date, pastabos 
FROM aktai 
WHERE MATCH(uzsakovas) AGAINST ('Statyba ir Architektūra, UAB' IN BOOLEAN MODE) 
  AND date between :date_from and :date_to;

    where :date_from and :date_to are Date parameters with the values 2013-04-01 and 2013-04-30 respectively.