Link to home
Start Free TrialLog in
Avatar of Strif
Strif

asked on

SQL Search Engine and Order by most relative field

Hello Im Developing a Search engine for my website!

The query looks like this
Select id,name,views,tags,description from tutorials WHERE name LIKE'%$search_variable%' or tags LIKE'%$search_variable%' or description LIKE'%$search_variable%' ;

*$search_variable stands for the search keyword

1]I would like to order my search results first by those which have similarity on the name then the tags and then the description.


2] Im using php , is there any way to show next to result where the similarity was found ?
example :
Result1 [ Similarity found on name field]  
Result2 [ Similarity found on tag field]  
Result3 [ Similarity found on tag field]
Result4 [ Similarity found on description field]  

if yes , how ?

Thanks!
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

yes, that is possible:
Select id,name,views,tags,description 
from tutorials 
WHERE name LIKE'%$search_variable%' 
   or tags LIKE'%$search_variable%' 
   or description LIKE'%$search_variable%' 
order by case
  when name LIKE'%$search_variable%' then 1
  when tags LIKE'%$search_variable%' then 2
  when description LIKE'%$search_variable%' then 3
  else 99 end 
;

Open in new window

Avatar of Strif
Strif

ASKER

angellll ! , thanks for the solution ! it worked perfectly!

about the second question , when I echo the results is possible to echo where the similarity was found ?
( name , tag , descreption ) is there any smart way to do that ?

It should be but , i cant think of it!

thanks again!
yes, that is possible to.
btw, I will suggest a variant that will show those where you have 2 matches ever further up
Select id,name,views,tags,description 
, case when name LIKE'%$search_variable%' then 1000 else 0 end
+ case when tags LIKE'%$search_variable%' then 100 else 0 end
+ case when description LIKE'%$search_variable%' then 10 else 0 end
  match_total
from tutorials 
WHERE name LIKE'%$search_variable%' 
   or tags LIKE'%$search_variable%' 
   or description LIKE'%$search_variable%' 
order by match_total desc
;

Open in new window

Avatar of Strif

ASKER

thanks for the kind reply angellll !
the second example you gave me shows me the results but ,
the first results are from the description then the tag and then the name !

I tried to use asc instand of desc but i didn't work !
you must have applied something incorrectly. I did put 1000, 100 and 10 instead of 1,2 and 3 ...
Avatar of Strif

ASKER

yes but you've changed the structure of the query.
Anyway , i will again !

any suggections about my 2] question ?

Thanks again for the first solution!!
you simply have to "decode" the value of match_total:
if it's 1110, then the similarity is on all 3 values
if it's 1000, then the similarity is on name only
etc ...

you could do that in the sql itself also:
Select id,name,views,tags,description 
, case when name LIKE'%$search_variable%' then 1000 else 0 end
+ case when tags LIKE'%$search_variable%' then 100 else 0 end
+ case when description LIKE'%$search_variable%' then 10 else 0 end
  match_total
 , case when name LIKE'%$search_variable%' then 'name;' else '' end
+ case when tags LIKE'%$search_variable%' then 'tags;' else '' end
+ case when description LIKE'%$search_variable%' then 'description;' else '' end
  match_fields
from tutorials 
WHERE name LIKE'%$search_variable%' 
   or tags LIKE'%$search_variable%' 
   or description LIKE'%$search_variable%' 
order by match_total desc
;

Open in new window

Avatar of Strif

ASKER

your last example works perfectly !!

the only thing now is to convert the similarity(ies) into a variable!

my results after the query are like that
echo '$name - $tags - $description' ;

how can i print the possible  fields where the similarity found ?

is that possible?
actually, the corrected example should be like this (using CONCAT).

the field from the recordset to show where the similarities come from would be match_fields
Select id,name,views,tags,description 
, case when name LIKE'%$search_variable%' then 1000 else 0 end
+ case when tags LIKE'%$search_variable%' then 100 else 0 end
+ case when description LIKE'%$search_variable%' then 10 else 0 end
  match_total
 , CONCAT(CONCAT(case when name LIKE'%$search_variable%' then 'name;' else '' end
, case when tags LIKE'%$search_variable%' then 'tags;' else '' end
), case when description LIKE'%$search_variable%' then 'description;' else '' end)
  match_fields
from tutorials 
WHERE name LIKE'%$search_variable%' 
   or tags LIKE'%$search_variable%' 
   or description LIKE'%$search_variable%' 
order by match_total desc
;

Open in new window

Avatar of Strif

ASKER

is possible to make 3 variables ?

so after that , i will be able to list the variables next to my results like this

if (similarity on name) { echo 'Similarity Name on found'; }
if (similarity on tags) { echo 'Similarity on tags found'; }
if (similarity on desciption) { echo 'Similarity on desciption found'; }



Thanks!
Avatar of Strif

ASKER

with other words , is possible to create 3 variables (for the name,tags,description) where i can store the status of the similarity ( true or false ) so if any of them is true, i could print them next to my results.
Thanks
yes, that is possible. but why 3, instead of 1 concatenated value like:

similarity found on Name;tags;
similarity found on Name;description;
Avatar of Strif

ASKER

thats better!
could you tell me how to create that variable ?
thanks!!
>could you tell me how to create that variable ?
can you clarify what exactly is your trouble?
I mean, you already have some code to loop & fetch & display from the records?!
ASKER CERTIFIED SOLUTION
Avatar of Strif
Strif

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Strif

ASKER

I HAVE NOT WORDS TO SAY!

When i opened that thread (few hours ago) i didnt think that i will find a solution!

Thanks angellll , thanks EE !!