Help with SQL "sets"

I want to build an application that would let people search records of
content, based on keywords. The content record table would have a field
called 'tags' which would be a comma-seperated string of keywords. It
would be very simple to search for records where the user enters only
one keyword, by searching for the keyword within the keyword string
in the record.

But I'd like to let the user enter more than one keyword, and be able
to select records that have -any- keywords that match -any- of the
keywords the user enters. And, have the results sorted by how many
matching keywords are found.

I know that MySQL has a "set" data type, but I have no idea whether
it would apply to this problem, or how to use it.

Can anyone help?
TIA,

glakk@potatoradio.f2s.com wrote:
> I want to build an application that would let people search records of
> content, based on keywords. The content record table would have a field
> called 'tags' which would be a comma-seperated string of keywords. It
> would be very simple to search for records where the user enters only
> one keyword, by searching for the keyword within the keyword string
> in the record.
>
> But I'd like to let the user enter more than one keyword, and be able
> to select records that have -any- keywords that match -any- of the
> keywords the user enters. And, have the results sorted by how many
> matching keywords are found.
>
> I know that MySQL has a "set" data type, but I have no idea whether
> it would apply to this problem, or how to use it.
>
> Can anyone help?
> TIA,
>

Look into normalizing databases.

A properly normalized database would never have multiple fields in a
column like this. Rather, create another table which two columns. The
first column is an id from the first table; the second column is ONE
keyword.

Now you can search the second table and get the appropriate keys for the
rows in the first table.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Following on from 's message. . .
>I know that MySQL has a "set" data type, but I have no idea whether
>it would apply to this problem, or how to use it.

Use Text and Full Text Search. This does all you want and probably with
a great deal less constraints than what you are thinking of. Using a
SET field definitely sounds like the wrong thing unless there are only a
few dozen fixed key words. The manual explains it all.


--
PETER FOX Not the same since the bridge building business collapsed
peterfox@eminent.demon.co.uk.not.this.bit.no.html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.demon.co.uk>

Jerry Stuckle wrote:
> glakk@potatoradio.f2s.com wrote:
> > I want to build an application that would let people search records of
> > content, based on keywords. The content record table would have a field
> > called 'tags' which would be a comma-seperated string of keywords. It
> > would be very simple to search for records where the user enters only
> > one keyword, by searching for the keyword within the keyword string
> > in the record.

[...]

> A properly normalized database would never have multiple fields in a
> column like this. Rather, create another table which two columns. The
> first column is an id from the first table; the second column is ONE
> keyword.

That makes perfect sense, but I'm still unsure about how to query the
tables with multiple search targets.
>
> Now you can search the second table and get the appropriate keys for the
> rows in the first table.

It's really a problem with syntax I'm having. I can intuit a query to
select
records given ONE search keyword, but more than one? It's in the
'where'
clause that I'm stumped. RTFM time, I guess. And MySQL documentation is
nowhere as easy to use as PHP's ref.

At least I know that sets won't do it.

Thanks.

glakk@potatoradio.f2s.com wrote:
>
>
> It's really a problem with syntax I'm having. I can intuit a query to
> select
> records given ONE search keyword, but more than one? It's in the
> 'where'
> clause that I'm stumped. RTFM time, I guess. And MySQL documentation is
> nowhere as easy to use as PHP's ref.
>
> At least I know that sets won't do it.
>
> Thanks.
>

OK, lets say you have:

Table1 Table2
id primary key id foreign key on Table1(id)
other stuff Keyword

If MySQL supported the INTERSECT SQL clause, this would be easier.
However, without INTERSECT, it's still possible, i.e. (not checked...)

SELECT * FROM Table1 WHERE
id IN (SELECT id FROM Table2 WHERE Keyword='Kwd1') AND
id IN (SELECT id FROM Table2 WHERE Keyword='Kwd2') AND
id IN (SELECT id FROM Table2 WHERE Keyword='Kwd3');

This gives you all the columns in all rows in Table1 which contain kwd1
kwd2 and kwd3 (assuming Table2 is set up correctly).



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================