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,descrip tion 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!
The query looks like this
Select id,name,views,tags,descrip
*$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!
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!
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
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
;
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 !
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 ...
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!!
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:
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
;
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?
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
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
;
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!
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!
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
Thanks
yes, that is possible. but why 3, instead of 1 concatenated value like:
similarity found on Name;tags;
similarity found on Name;description;
similarity found on Name;tags;
similarity found on Name;description;
ASKER
thats better!
could you tell me how to create that variable ?
thanks!!
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?!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 !!
When i opened that thread (few hours ago) i didnt think that i will find a solution!
Thanks angellll , thanks EE !!
Open in new window