By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,405 Members | 1,531 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,405 IT Pros & Developers. It's quick & easy.

Help with SQL "sets"

P: n/a
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,

Jul 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
gl***@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.
js*******@attglobal.net
==================
Jul 17 '05 #2

P: n/a
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
pe******@eminent.demon.co.uk.not.this.bit.no.html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.demon.co.uk>
Jul 17 '05 #3

P: n/a


Jerry Stuckle wrote:
gl***@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.

Jul 17 '05 #4

P: n/a
gl***@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.
js*******@attglobal.net
==================
Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.