Search across Joined tables

[drcheez]

I'm going mad trying to figure this out, can anyone help?

 

On my website I've got a product database, and an Ajax auto-complete product search - so that as you're typing, the search field is refining what you're looking for. You know the kind of thing I'm sure.

 

My SQL at the moment is

 

SELECT pr.prod_name AS prod_name,

au.author_last_name AS author,

ed.author_last_name AS editor

FROM products AS pr

LEFT JOIN authors AS au

ON (au.author_id=pr.author)

LEFT JOIN authors AS ed

ON (ed.author_id=pr.editor)

ORDER BY SUM("pr.prod_name LIKE '$search%', au.author_last_name LIKE '$search',  ed.author_last_name LIKE '$search%'") DESC

 

 

This is a simplified version of the code, as I'm iterating through the $search terms.

 

I have to use to the ORDER BY SUM() part, to order by relevance - as I can't MATCH() across joined tables (at least, so I believe - that's correct, right?). The SUM() is there to cound how many fields match the search. But it's here that the problem lies - I only get back the first row of the table, whether it is like $search or not.

 

Am I making a glaring mistake here? Also, the tables are very small at the moment - when it gets large, is this kind of query going to have users hanging themselves while they wait for a page to load?!

 

Thanks in advance - if you need more info I'll provide!

[fenway]

This comes down to an underlying limitation of the mysql fulltext search functions... you either need to roll-your-own, or use something like sphinx.