[thelist] Fun with MySQL
Phil Turmel
philip at turmel.org
Wed Jul 5 19:58:39 CDT 2006
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....
More information about the thelist
mailing list