CODEWITHSUNDEEP
phpmysqlstringtagsextract
DarkMaze
DarkMaze

Reputation: 263

How to extract tags from strings

I'm using PHP, PDO extension and MySQL as default DBMS. also I have a prepared tags table with two id and tag columns in my database.

my purpose is "auto extract matched tags from a string that exist in the tags table" and my problem is making a PDO query to perform that.

for example if the string is "An Expensive And High Quality Computer hardware" , after removing words with less than 3 characters, if I execute a query like: SELECT id FROM tags WHERE tag LIKE %Expensive%And%High%Quality%Computer%; , literal tags like "expensive" or "computer" will never found!

also executing a query like SELECT id FROM tags WHERE tag='expensive' OR tag='and' OR tag='high' OR tag='quality' OR tag='computer' OR tag='hardware'; will not contain tags like "High Quality" or "Expensive hardware".

this query will be executed once per each string and processing time will not matter in this case.

thank you in advance and sorry for my bad explain.

Upvotes: 1

Views: 91

Answers (2)

Digital Chris
Digital Chris

Reputation: 6202

You almost had it; just put wildcards around each term:

SELECT id 
FROM tags 
WHERE tag='%expensive%' 
    OR tag='%high%' 
    OR tag='%quality%' 
    OR tag='%computer%'
    OR tag='%hardware%';

Note that I excluded "and" from the terms. You probably want to check the string length and don't use anything 3 chars or less, since "%and%" will match lots of things like "sandy" "bland" "grandiose" etc.

Upvotes: 1

whizzzkid
whizzzkid

Reputation: 1295

i think you can use something like:

SELECT id FROM tags WHERE tag LIKE '%expensive%' OR tag LIKE '%and%' OR tag LIKE '%high%' OR tag LIKE '%quality%' OR tag LIKE '%computer%' OR tag LIKE '%hardware%';

In your query you're limiting your search to exact words like tag='hardware'. this will not match expensive hardware.

Upvotes: 0

Related Questions