SELECT
*
FROM
company c
INNER JOIN
city ci ON ci.city_id = c.city_id
INNER JOIN
state s ON s.state_id = c.state_id
WHERE
MATCH (
c.name, ci.name, c.zipcode, s.name
)
AGAINST (
'los angeles'
)
The basic query format of full-text searches in MySQL should be similar to the following: SELECT * FROM table WHERE MATCH(column) AGAINST(“string” IN NATURAL LANGUAGE MODE); When MATCH() is used together with a WHERE clause, the rows are automatically sorted by the highest relevance first.
Full-text indexes are created on text-based columns ( CHAR , VARCHAR , or TEXT columns) to speed up queries and DML operations on data contained within those columns. A full-text index is defined as part of a CREATE TABLE statement or added to an existing table using ALTER TABLE or CREATE INDEX .
All columns in MATCH (...)
must be from the same table, because they must be fulltext indexed, and MySQL cannot create one index for data in multiple tables.
I had the same problem and solved it like this:
SELECT
*
FROM
company c
INNER JOIN
city ci ON ci.city_id = c.city_id
INNER JOIN
state s ON s.state_id = c.state_id
WHERE
MATCH (c.name) AGAINST ('los angeles')
OR MATCH (ci.name) AGAINST ('los angeles')
OR MATCH (c.zipcode) AGAINST ('los angeles')
OR MATCH (s.name) AGAINST ('los angeles')
But as I see, You are searching in simple fields like 'name' and 'zipcode'. As for me, it would be better to use LIKE
and just concatenate them
SELECT
*
FROM
company c
INNER JOIN
city ci ON ci.city_id = c.city_id
INNER JOIN
state s ON s.state_id = c.state_id
WHERE
CONCAT_WS (' ', c.name, ci.name, c.zipcode, s.name)
LIKE ('%los angeles%')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With