Member Avatar for daocntt

I have two table data as:
- table Classifieds
id | title | body | city | district|
- table Locations
id | label
Relationship
City of table Classifieds is Id two Locations
Now I want using Fulltext Search when users enter keyword (title, body, label(USA, Canada...)) then it will return records by keyword users enter.

  • 4 Contributors
  • 6 Replies
  • 345 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Short summary tied to the thread: wants users to find classifieds by title/body and by the related location label. Both approaches already suggested in the thread are valid: a simple JOIN + LIKE (as suggested) works for exact/short-label lookups, while MATCH...AGAINST (full‑text) gives relevance ranking for longer text (as noted). For a robust, maintainable search there are three practical options: (A) keep the normalized tables and run full‑text against the classifieds columns and a separate match/like against the location label, (B) add a stored/cached label into classifieds (or a single combined searchable column) and index that together with title/body, or (C) use an external engine (Elasticsearch/Meilisearch/Algolia/Typesense) via Laravel Scout for scale. Full‑text in MySQL uses MATCH() ... AGAINST() and is intended for relevance searches on text columns. (dev.mysql.com)

Example SQL pattern (create FULLTEXT indexes first, then combine scores from both tables):

-- create fulltext indexes beforehand (example only)
ALTER TABLE classifieds ADD FULLTEXT ft_cb (title, body);
ALTER TABLE locations  ADD FULLTEXT ft_label (label);

SELECT c.*, l.label,
  MATCH(c.title,c.body) AGAINST(? IN BOOLEAN MODE) AS score_c,
  MATCH(l.label) AGAINST(? IN BOOLEAN MODE) AS score_l
FROM classifieds c
JOIN locations l ON l.id = c.location_id
WHERE MATCH(c.title,c.body) AGAINST(? IN BOOLEAN MODE)
   OR MATCH(l.label) AGAINST(? IN BOOLEAN MODE)
ORDER BY GREATEST(COALESCE(score_c,0), COALESCE(score_l,0)) DESC;

Note: each MATCH(...) needs a FULLTEXT index on the columns used (indexes are defined per table). (dev.mysql.com)

Laravel (Eloquent / Query Builder) example sketch using bindings and raw MATCH:

$term = '+USA';
$rows = DB::table('classifieds as c')
  ->join('locations as l','l.id','=','c.location_id')
  ->select('c.*','l.label')
  ->whereRaw('MATCH(c.title,c.body) AGAINST(? IN BOOLEAN MODE)', [$term])
  ->orWhereRaw('MATCH(l.label) AGAINST(? IN BOOLEAN MODE)', [$term])
  ->get();

Use parameter binding to avoid injection and fall back to ->where('l.label', $term) or ->where('l.label','like',"%$term%") for very short or exact labels. See Laravel query builder raw methods. (laravel.com)

Troubleshooting & practical cautions: short words and stopwords can be ignored by MySQL full‑text — InnoDB typically ignores words shorter than 3 chars and MyISAM shorter than 4 by default; change innodb_ft_min_token_size or ft_min_word_len and then rebuild FULLTEXT indexes if you need to index short tokens like "USA". For production-scale, prefer a search service and Laravel Scout (driver ecosystem available) rather than pushing complex text ranking into SQL. (dev.mysql.com)

Member Avatar for Dani

What's the guide? I'm confused. Is this a question you're asking?

Member Avatar for daocntt

I'm working on a project with MYSQL and FW PHP Laravel where I believe full-text search is the best way to go. Imagine the following table structure:
Classifieds
id
title
boby
location_id
Location
id
label
Classified

ID  Title   Body    Location_id
1   ABC     ABCD    1
2   BCD     BCDE    2
3   ABC     ABCD    1
4   BCD     BCDE    3

Location

ID  Label
1   USA
2   Italia
3   Canada

As you can see. Now i want when users one keywork (USA) and will it show all recoders has keywork USA.

Member Avatar for ko ko

Can't you write down an JOIN query ? Like below:

SELECT c.*, l.Label AS Location FROM Classifieds AS c JOIN location AS l ON c.Location_id = l.ID WHERE l.Label LIKE '%USA%'
Member Avatar for daocntt

In this case, I have a search input box. When users enter a title, body, or label, it will also find recoder containing such information. And the only way I used to work fulltext search with it instead of using not Like

Member Avatar for Member #120589

So what specifically do you need? AN example of fulltext in SQL? Did you check the online mysql manual?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.