[thelist] Fun with MySQL

[Phil Turmel]

Hershel Robinson wrote:
> This is a non-trivial SQL question in a long-ish letter. The question is 
> going to be:
> 
> Does anyone know how I can do full-text searching on a temporary table? 
> Or perhaps an alternative solution to this problem?

How about an alternative?  Skip the temp table:

MySQL v4 and up allow UNION clauses.  Expanding on Anthony's suggestion, 
you might end up with something like this:

(SELECT ID, author, title, blurb, pageNum,
   MATCH (author,title,blurb) AGAINST ('Weiss') AS relevance
   FROM `indexes`
   WHERE MATCH (author,title,blurb) AGAINST ('Weiss'))
UNION ALL
(SELECT ID, author, title, '' as blurb, 0 as pageNum,
   MATCH (author,title) AGAINST ('Weiss') AS relevance
   FROM `radio_details`
   WHERE MATCH (author,title) AGAINST ('Weiss'))
ORDER BY relevance DESC

Note the extra parenthesis to permit sorting by relevance across the 
combined result set.

References here:
http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html
http://dev.mysql.com/doc/refman/4.1/en/union.html

Hope this helps,

Phil

ps.  I suspect you already know, but mysql_escape_string() would be 
appropriate on $_REQUEST....  kinda like one of those latex thingies 
some people use....