SEO

I am trying to write a video search engine for my website which has a
database of videos that stores the following information (among other
things): "Title", "Keywords", and "Description".

The goal of this search engine is to have a user type in a query and
then have it return results where any of those fields match using the
'LIKE' command. However, I want all of the videos with titles matching
to be shown before those with keywords matching, and then the ones
with description matching.

Obviously this can be done with 3 commands but based on my current
configuration it would be REALLY nice if I could do it with one result
set. For example, pagination. If I want to limit results to 10 per
page it is difficult to use the LIMIT command over 3 different sql
statements.

I was thinking something with GROUP maybe?? But I feel like that can
only be done over fields. Any thoughts??

matt schreef:
> I am trying to write a video search engine for my website which has a
> database of videos that stores the following information (among other
> things): "Title", "Keywords", and "Description".
>
> The goal of this search engine is to have a user type in a query and
> then have it return results where any of those fields match using the
> 'LIKE' command. However, I want all of the videos with titles matching
> to be shown before those with keywords matching, and then the ones
> with description matching.
>
> Obviously this can be done with 3 commands but based on my current
> configuration it would be REALLY nice if I could do it with one result
> set. For example, pagination. If I want to limit results to 10 per
> page it is difficult to use the LIMIT command over 3 different sql
> statements.
>
> I was thinking something with GROUP maybe?? But I feel like that can
> only be done over fields. Any thoughts??

select * from titlesearch LIMIT 10
union
select * from keywordsearch LIMIT 10
union
select * from descriptionsearch LIMIT 10

for ordering this resultset propperly you might even want to do this:

select 'A', * from titlesearch LIMIT 10
union
select 'B', * from keywordsearch LIMIT 10
union
select 'C', * from descriptionsearch LIMIT 10


--
Luuk

matt wrote:
>I am trying to write a video search engine for my website which has a
> database of videos that stores the following information (among other
> things): "Title", "Keywords", and "Description".
>
> The goal of this search engine is to have a user type in a query and
> then have it return results where any of those fields match using the
> 'LIKE' command. However, I want all of the videos with titles matching
> to be shown before those with keywords matching, and then the ones
> with description matching.
>
> Obviously this can be done with 3 commands but based on my current
> configuration it would be REALLY nice if I could do it with one result
> set. For example, pagination. If I want to limit results to 10 per
> page it is difficult to use the LIMIT command over 3 different sql
> statements.
>
> I was thinking something with GROUP maybe?? But I feel like that can
> only be done over fields. Any thoughts??

I think you're actually looking for FULLTEXT searching.