Mysql order by method

CΛSTΞX · 06-03-2010, 05:42 PM

For example, if the search is

Norton free 2010 Download

How to order results like this

Norton free download
Norton download
Norton 2010 for pc

The first one includes 3 words of the search, so it is first. How to do this, thanks...

adamdecaf · 06-04-2010, 02:10 AM

Are you looking to sort the results be word length or by relevance?

Village Idiot · 06-04-2010, 03:03 AM

Quote:

Originally Posted by CΛSTΞX

For example, if the search is

Norton free 2010 Download

How to order results like this

Norton free download
Norton download
Norton 2010 for pc

The first one includes 3 words of the search, so it is first. How to do this, thanks...

If I understand this correctly, you want to order by the number of words that are the same THEN the percentage of words the same. Here is another result set:
Search "Google SEO Tips"
1. Google SEO Tips
2. Google SEO Tips blog
3. Google search Tips
3. Google search Tips blog

If so there is no easy way. The query would require custom functions to be defined using stored procedures. If you want to hire me I will be willing to do this, but here is the best I can/will do for free:
This will give you the occurrences of a string in another string (we are finding the number of occurrences of stringB in stringA).

mysql Code:

set @stringA="Google Search Google", @stringB="Google";

SELECT ((LENGTH(@stringA) - LENGTH(REPLACE(@stringA,@stringB, '')))/LENGTH(@stringB)) AS `occurrences`

It will return 2.

CΛSTΞX · 06-04-2010, 12:30 PM

Hmm, thank you. How about this >

PHP Code:


			
SELECT *, ((CASE WHEN `description` LIKE '%brown%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%fox%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END)) AS relevance
FROM `items`
WHERE `description`LIKE '%quick%' AND (
  `description`LIKE '%brown%'
  OR `description` LIKE '%fox%'
  OR `description` LIKE '%lazy%'
  OR `description` LIKE '%dog%'
)
ORDER BY relevance DESC
LIMIT 0 , 30;

Do you think that works properly ? Or what money you want to do the best script for that ?

Village Idiot · 06-04-2010, 04:44 PM

Quote:

Originally Posted by CΛSTΞX

Hmm, thank you. How about this >

PHP Code:


			
SELECT *, ((CASE WHEN `description` LIKE '%brown%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%fox%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END)) AS relevance
FROM `items`
WHERE `description`LIKE '%quick%' AND (
  `description`LIKE '%brown%'
  OR `description` LIKE '%fox%'
  OR `description` LIKE '%lazy%'
  OR `description` LIKE '%dog%'
)
ORDER BY relevance DESC
LIMIT 0 , 30;

Do you think that works properly ? Or what money you want to do the best script for that ?

The first part looks good, but the second part doesn't make sense. From what it looks like, the description would have to have the word quick in it to be included. Change the and to or and you have have something here. The only issue I see is ordering passed the number of similar words, the last two examples from what you gave would not necessarily be in that order.

For me to do it in SQL would take me about an hour to write, debug and prefect, so $50. You can see my portfolio at http://www.qsdconsulting.com At the moment you seem to be on to something, so lets see how that goes first. I'm happy to help you as long as you are giving it your effort.

CΛSTΞX · 06-04-2010, 05:39 PM

Ok, I want you to do this for me, do you have msn ?

Village Idiot · 06-04-2010, 05:54 PM

Quote:

Originally Posted by CΛSTΞX

Ok, I want you to do this for me, do you have msn ?

Yes, phpnsql@hotmail.com