PHP/MySql sorting problem

How do you sort by column matches?

[Chran]

Hi everybody,

I am working on a search function for an e-commerce site and I just cant figure this one out. It is probably not that hard but I am stuck.

The search is searching three columns in a table (category, title and description) for matches in each column. Each column should now weigh differently (1. category, 2. title and 3. description) and I want the results to be ordered with hits in category on top, then title hits and last description hits. Then I want to group the hits into categories and present them category by category.

Is there a way to accomplish this in one single query or do I have to use more than one query and then merge the result somehow with PHP? I have tried both without success.

This is what I got right now:

SELECT category FROM 
 (SELECT category, title, descr, count(*) AS hits FROM csvdata_searchtest WHERE title LIKE '%$searchword%' ¦¦ descr LIKE '%$searchword%' GROUP BY category ORDER BY hits DESC LIMIT 0,50)
 csvdata_searchtest WHERE category LIKE '%$searchword%' ¦¦ title LIKE '%$searchword%' ¦¦ descr LIKE '%$searchword%'

Right now I am counting the hits in each category to sort the relevance and works fine but should come secondary after the column weight.

Any ideas?

[phparion]

can you show me a, command line recommended, output of this query? so that I can see how do you get data now.

[Chran]

I am not sure that I quiet understand what you want, but I get only categories out of it and it should be sorted by number of containing products with hits in the title and description fields. It seems to be working fine, but it is not exactly what I want.

[Chran]

Ok, its this simple. What I really want to do is:

$query1 = mysql_query("SELECT * FROM table WHERE category LIKE '%searchword%'");
$query2 = mysql_query("SELECT * FROM table WHERE title LIKE '%searchword%'");
$query3 = mysql_query("SELECT * FROM table WHERE description LIKE '%searchword%'");
$acarray1 = mysql_fetch_assoc($query1);
$acarray2 = mysql_fetch_assoc($query2);
$acarray3 = mysql_fetch_assoc($query3);
$result = array_merge($acarray1, $acarray2, $acarray3);

Ok, that doesnt work, but I think you get the idea of what I want to do. I tried to make three queries and merge the results together as an array but I havent found a way. Then I tried to accomplish it in one query but I just cant get that to work either. Can someone please guide me into the right direction.

[edited by: Chran at 8:02 pm (utc) on Jan. 16, 2008]