UNION statement to sort search results twice 2005-02-11 - By Jan Eden
Back
Hi,
I wrote a search script which can search only the page titles and author names
for articles on my site or the titles/authors and the pages' content.
To get the results for the two types in two sets, I used a UNION construct:
if ($search_string) {
my $where_author_title = qq{(authors.last_name LIKE '\%$search_string\%' OR
authors.first_name LIKE '\%$search_string\%' OR pages.title LIKE '\%$search
_string\%')};
my $where_content = qq{MATCH(content) AGAINST ('$search_string' IN BOOLEAN
MODE)};
my @(protected) = ( $where_author_title );
push @(protected), $where_content if $volltext;
my $query = join 'UNION', map(qq{(SELECT pages.page_id, pages.title,
authors.first_name, authors.last_name, pages.visible, pages.user_id FROM pages
JOIN authors USING (author_id) WHERE $_ AND site_id = $sitemode ORDER BY pages
.title)}, @(protected));
Later on, I test each row returned by the UNION statement if the $search_string
is either in the title or in the author's name. If not, I print a dividing line
and a subheader once.
The result looks ok, something like:
Search results for 'Perl'
Title/author
...
...
...
Content
...
...
...
Now is there a better/more elegant way to do this? I am sure there is, and I
would be grateful for a pointer.
Thanks,
Jan
--
Hanlon's Razor: Never attribute to malice that which can be adequately
explained by stupidity.
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=mysql@(protected)
|
|
