Feasibility of mysql query

[darcuss]

Hi.

 

I have multiple tables containing 'tokens' where token is a descriptive characteristic of the item. I wish to query these tables with the contents of a textarea so each row is tokenized and compared against the tokens. Where a match is found, the query would return the item referenced by the token. A further problem exists where different combinations of 'words' of each row of the texarea input may form a token.

 

For example a table of tokens and their related items:

 

itemToks1

+-------+---------------+

| itemId | tok |

+-------+---------------+

| 1 | bright green |

| 1 | purple |

| 2 | dull yellow |

| 2 | green |

| 3 | azure blue  |

| 4 | red |

| 4 | gold |

| 5 | sun orange  |

| 5 | bright white |

| 6 | garish girlie pink |

| 6 | yellow and blue |

| 7 | purple |

+-------+---------------+

 

itemToks2

+-------+---------------+

| itemId | tok |

+-------+---------------+

| 1 | almost round|

| 1 | fairly large |

| 2 | very small |

| 3 | fairly small |

| 3 | roundish |

| 4 | quite small |

| 5 | not square |

| 5 | a little large |

| 6 | equilateral triangular |

| 6 | medium proportioned |

| 7 | round rotund |

| 7 | massive giant |

+-------+---------------+

 

 

 

 

items

+------+-------+--------------------------------+

| id | name | des | itemCat |

+------+-------+--------------------------------+

| 1 | item 1 | item desc 1 | 2 |

| 2 | item 2 | item desc 2 | 3 |

| 3 | item 3 | item desc 3 | 7 |

| 4 | item 4 | item desc 4 | 3 |

| 5 | item 5 | item desc 5 | 5 |

| 6 | item 6 | item desc 6 | 6 |

| 7 | item 7 | item desc 7 | 1 |

+------+--------------+-----------------|---------+

 

 

If for example the user entered to the text box:

----------------------------

| quite small red

| bright white not square

| purple a little large

----------------------------

 

... i would like returned

 

----------------------------

| item 4

| item 5

----------------------------

 

This seems to me to be unfeasible, though it is desirable for my project. For one, the requirement to tokenize different combination's of words seems unfeasible to me as far as mysql is concerned. For example from the first line the query would need to check 'quite' and then 'quite small' where it finds a match, then red where it finds a match.

 

The tables of tokens may become fairly large which is why I'm inquiring whether this is feasible over grabbing the tables from mysql and processing with PHP. This whole explanation looks horrible to me and I hope someone in the know can understand the jist of it.

 

Thanks in advance.

[Adam]

I'm not sure I understand what you're asking. I think the names of your tables and fields are a little confusing, 'token'? Would a better word be 'attribute'? If so I reckon I know what you'r asking; to find "items" matching the description entered into the textarea, basically?

 

This is possible, but certain things you'll need to think about in order to make it usable to a new user. Like say someone entered "quite round" and you have "almost round", you'd probably need to figure out a way of suggesting other terms to improve results. Also people make spelling and grammer mistakes, case-sensitivitey, etc. Lots of things to consider.

 

If they entered a criteria for each new line you can just explode the string using "\n" and you have each attribute nicely set in an array, which you can then use with your query. I don't think this would just be a simple query though..

 

Adam

[darcuss]

Hi Adam, thanks for your reply.

 

Yes, attribute would probably be more descriptive in the context of this conversation. The word token came up as I wish the user to be able to search using variations of the same word, name or attribute and the data provided is purely for testing purposes.

 

My explanation wasn't too clear and I need to stress what I consider to be the difficulty surrounding what I'm proposing. That is, having multiple tokens (characteristics) of varying word length on each row of the text area.

 

Thanks again.