473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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,

Jul 17 '05 #1
4 1212
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
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


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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Adam Toline | last post by:
In reference to the following: http://www.bellecose.com/form.htm At the top of each column there is a box for "All". When one is checked I need to check all of (and only) those boxes...
19
by: Christian Fowler | last post by:
I have a VERY LARGE pile of geographic data that I am importing into a database (db of choice is postgres, though may hop to oracle if necessary). The data is strictly hierarchical - each node has...
21
by: Helge Jensen | last post by:
I've got some data that has Set structure, that is membership, insert and delete is fast (O(1), hashing). I can't find a System.Collections interface that matches the operations naturally offered...
1
by: Jay | last post by:
I'm installing one of my dlls to the GAC but no matter what attribute I try, the name that shows up in explorer under global assembly name is always just the name of my assembly file. Is there a...
9
by: rsine | last post by:
I have developed a program that sends a command through the serial port to our business system and then reads from the buffer looking for a number. Everything worked great on my WinXP system, but...
7
by: Erik Funkenbusch | last post by:
I've been thinking about different ways to address this problem, and I figured i'd just toss this out and see if anyone has a good solution. I've got database field of type char(1), this field...
1
by: laredotornado | last post by:
Hi, I'm using PHP 4.4.4 on Apache 2 on Fedora Core 5. PHP was installed using Apache's apxs and the php library was installed to /usr/local/php. However, when I set my "error_reporting"...
3
by: =?Utf-8?B?Sm9l?= | last post by:
I know that I have posted this question before, but it is still unresolved and I don't know where to turn to next. I have code that is creating a user (works fine), then sets the account flags...
0
by: DomiNeug | last post by:
Hello, Since a while i have to find a way of comparing "Sets" (multiple int Values) and so to find equal sets. There simply 3 tables ValueList with: ID int ValueListHasValue: ID int, ValueListID...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.