Jump to content

Search across Joined tables


drcheez

Recommended Posts

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! :)

Link to comment
https://forums.phpfreaks.com/topic/180208-search-across-joined-tables/
Share on other sites

  • 2 weeks later...

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.