Member Avatar for vibhaJ

HI All,

my requirement is as below:
lets product name is "aaa bbb ccc".
Now in search coding i have use LIKE statement.

"... where product_name LIKE '%".$keyword."%'"

Now when user insert 'aaa' or 'bbb' or 'ccc' or 'aaa bbb' or 'bbb ccc' search works.
But if user insert 'aaa ccc' then it is not working.
I want such search result where 'aaa ccc' will also comes in result.

Help me !!

  • 3 Contributors
  • 8 Replies
  • 106 Views
  • 13 Hours Discussion Span
  • Latest Post Latest Post by vibhaJ

Dani AI

Generated

Short answer: the simplest fix for the exact problem in this thread is to require every search token to appear (the AND approach suggested and corrected). For production use, prefer a full‑text index instead of many LIKE '%...%' checks — it is faster, supports relevance ranking, and can do phrase/boolean searches (so "blackberry 2200" and "2200 blackberry" can be handled more cleanly).

A practical full‑text workflow:

  • Add a full‑text index on the product name.
  • Use a boolean MATCH...AGAINST query to require tokens (+token) or to search as a phrase ("phrase").

Example:

ALTER TABLE product ADD FULLTEXT (product_name);

SELECT product_id, product_name,
       MATCH(product_name) AGAINST('+aaa +ccc' IN BOOLEAN MODE) AS score
FROM product
WHERE MATCH(product_name) AGAINST('+aaa +ccc' IN BOOLEAN MODE)
ORDER BY score DESC;

To match an exact phrase (order and adjacency) use quotes in boolean mode:

... AGAINST('"blackberry 2200"' IN BOOLEAN MODE)

Caveats and tips:

  • Full‑text behavior depends on server settings (stopwords, minimum token length). If short words are ignored, either change the server setting and rebuild the index or add a small fallback that matches short tokens differently. See MySQL full‑text docs for details: MySQL full‑text search and Boolean full‑text searches.
  • Normalize input (trim, collapse whitespace, lowercase) before building the query.
  • Never concatenate raw user input into SQL; use prepared statements or properly escape tokens to avoid injection.
  • If you need advanced fuzzy, typo-tolerant, or cross-field ranking, consider a search engine (Sphinx, ElasticSearch) instead of MySQL full‑text.

Recommended Answers

All 8 Replies

Member Avatar for Daiva

Just a quick thought: you can explode your search term in to words and check with each of them.

Member Avatar for vibhaJ

I think it wont work,

e.g product name "blackberry bold 2200",
and i would search with "blackberry 2200"
then if i explode then all "blackberry" would comes.. i just want blackberry 2200.

This is also what google do.
But i don't know how they checks?

Member Avatar for Daiva

I am not a professional person... But shouldn't it be working?

"... where (product_name LIKE '%".$keyword[1]."%') AND (product_name LIKE '%".$keyword[2]."%')..."

Hope somebody will help you. I am interested in this problem too :]

Edited by Daiva because: n/a
Member Avatar for urtrivedi 
<?php 		

$_POST['keywords']="aaa    ccc";

for($i=0;$i<10;$i++)
	$_POST['keywords']=str_replace("  "," ",$_POST['keywords']);

$arrkey=explode(" ",trim($_POST['keywords']));	


$query="select * from product where somecol='somevalue' ";

$keycount=count($arrkey);
if($keycount>0 and trim($_POST['keywords'])!="")
{
	$query.=" and (";
	$operator="";
	for($i=0;$i<$keycount;$i++)
	{
		$query.=$operator."(product_name like '%{$arrkey[$i]}%')";
		$operator=" or ";		
	}
	$query.=")";
}
echo $query;

?>
Member Avatar for vibhaJ 
<?php 		

$_POST['keywords']="aaa    ccc";

for($i=0;$i<10;$i++)
	$_POST['keywords']=str_replace("  "," ",$_POST['keywords']);

$arrkey=explode(" ",trim($_POST['keywords']));	


$query="select * from product where somecol='somevalue' ";

$keycount=count($arrkey);
if($keycount>0 and trim($_POST['keywords'])!="")
{
	$query.=" and (";
	$operator="";
	for($i=0;$i<$keycount;$i++)
	{
		$query.=$operator."(product_name like '%{$arrkey[$i]}%')";
		$operator=" or ";		
	}
	$query.=")";
}
echo $query;

?>

This code will generate below query:

$query="select * from product where somecol='somevalue' and ( (product_name like '%aaa%') or (product_name like '%ccc%') ) ";

But again, this will popup all record which have 'aaa' but not 'ccc'.

Member Avatar for vibhaJ

I am not a professional person... But shouldn't it be working?

"... where (product_name LIKE '%".$keyword[1]."%') AND (product_name LIKE '%".$keyword[2]."%')..."

Hope somebody will help you. I am interested in this problem too :]

This is simple logic and it should work.. let me check.

Member Avatar for urtrivedi

CHANGE MY LINE NO 21 OF PREVIOUS POST LIKE GIVEN BeLOW, remove or then write AND

$operator=" and ";

Member Avatar for vibhaJ

Thanks urtrivedi and Daiva.
The logic of both is same and it is so simple.
I dont know why it was not sparked for me even its so simple, anyways it happens sometimes :)

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.