Page 1 of 1

Complex Explode Search! Please Help

Posted: Sat Dec 27, 2008 1:48 pm
by guruk
Hi,
I have a Database in which I have a Table "Keys".
Inside Keywords are seperated with Delemiter ",".
This Keywords should be exploded and be searched especialy for one Keyword.

For Example the Table looks like:

nr keys
rec1 Levigatura concava, Levigatura corcava, concava
rec2 Protesi ibrida, protesi, ibrida, ancoraggio, palat
rec3 Corone parziali, intarsio, mezza
rec4 Intarsio in ceramica, in corona, corona parziale

-> and I search for example for %cor%
->> The Result should be like:

Levigatura corcava
ancoraggio
Corone parziali
in corona
corona parziale

And in best Case (or as extension so I understand whats the different)
to delete all double Entries (non case sensitive)
So I get a Result like:

Levigatura corcava
ancoraggio
Corone parziali
in corona

Is this Possible? and How. And for the Wizards, if I would search only
for "cor%" (not "%cor%") is it also possible to do that.
.. The Result would be only "corina parziale"

Now I am very excited if someone is able to solve that .. just with Mysql

Thanks
Chris

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Sat Dec 27, 2008 2:51 pm
by jaoudestudios
Well...I would try and help, but WTF are you talking about! :P

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Sun Dec 28, 2008 2:53 am
by guruk
sorry first Post was in German.. I changed it now to english :)

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Sun Dec 28, 2008 3:16 am
by jaoudestudios
Well. To remove duplicate results (if there are multiplies of the same thing in the database) is easy to do, with DINSTINCT or GROUP BY.

It would be easier if you put each entry in its own row. i.e dont do the explodes! Is this possible? (i.e. normalise the database)

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Tue Dec 30, 2008 4:13 am
by guruk
ok.. that with the double entries i got..
but whats about a sql statement to explode one specific entry from a row and
bring all in a result list?

I could get easy all rows with that keyword and later filter with php the specific
keyword, but i would wish to do that with mysql, just to make it in one step

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Tue Dec 30, 2008 4:23 am
by VladSun
As jaoudestudios suggested, you should normalize your DB.
You need to have another table - filled with just one "key" per a row.

Or you can rebuild your current table like this:
nr keys
rec1 Levigatura concava, Levigatura corcava, concava
rec2 Protesi ibrida, protesi, ibrida, ancoraggio, palat
rec3 Corone parziali, intarsio, mezza
rec4 Intarsio in ceramica, in corona, corona parziale
will be:
nr keys
rec1 Levigatura concava
rec1 Levigatura corcava
rec1 concava
rec2 Protesi ibrida
rec2 protesi
rec2 ibrida
rec2 ancoraggio
rec2 palat
Also, use numeric field for the "nr" one (i.e. the one filled with "rec1", "rec2" etc. values) - your query will be faster (by using a prebuild index of course).

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Tue Dec 30, 2008 4:38 am
by guruk
snief :)

exactly like that I have it! (because i just did not know how to solve better :)

I just thought (also learn something new) if its generaly possible to do that with mysql?

Maybe you can give me a small teaching.

For example table "movies"

Name Keywords
rec1 Levigatura concava, Levigatura corcava, concava
rec2 Protesi ibrida, protesi, ibrida, ancoraggio, palat
rec3 Corone parziali, intarsio, mezza
rec4 Intarsio in ceramica, in corona, corona parziale

" select `keywords` from 'movies' where keywords = '%cor%'"

would give me all that FULL Keyword Strings where "cor" is include. Fine!
And now just for learning Is there a Explode Like Command in MYsql where
I just would get the keyword inside the delimeter for example
not "corone parziali, intarsio, mezza" ... instead just "corona parziali"

Maybe something with the Mysql FIND_IN_SET, LEFT or REG command??
Or even with a Select command that includes a second Select command.

Now its not about the perfect code.. its just about "is a solution like that possible with mysql?"

thank for your research :)
chris

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Tue Dec 30, 2008 4:54 am
by VladSun
If I was a member of MySQL developer team, I would never provide a explode like functionality to the end users - so I would keep them using properly normalized DBs :)
But I'm not ;)

http://dev.mysql.com/doc/refman/5.0/en/ ... ind-in-set

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Tue Dec 30, 2008 5:04 am
by guruk
haha.. i know what you mean... yeahh you are right.. as i told, right now i do it with a normalized db
but now my scientic research interest come through and I just like to know how it "would" be possible
to solve it in mysql alone.

yes I know this "Find_in_Set", but what I dont know.. how to exctract a simple Result lets say

Name Keywords
rec1 Levigatura concava, Levigatura corcava, concava
rec2 Protesi ibrida, protesi, ibrida, ancoraggio, palat
rec3 Corone parziali, intarsio, mezza
rec4 Intarsio in ceramica, in corona, corona parziale

SELECT FIND_IN_SET tell me: "%cor%"
is in rec1 field 2
rec2 field 4
rec3 field 1

how do I say. .. now give me exactly that Fields content?

:)

chris

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Tue Dec 30, 2008 5:12 am
by VladSun
Maybe
http://dev.mysql.com/doc/refman/5.0/en/ ... ring-index

Also, you will be interested in reading the google results of "MySQL split"

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Tue Dec 30, 2008 5:27 am
by guruk
thanks :)

right now i play with this

1. step
-------------------------
SELECT id, keywords
FROM `cdent_movies`
WHERE `keywords` LIKE '%cor%'
LIMIT 0 , 30

is my standart search and give me a list of all movieids with related keywords.

12304870167619019 Levigatura concava, Levigatura concava, concava
1230487017303650 Protesi ibrida, protesi, ibrida, ancoraggio, palat
12304870173920594 Corone parziali, intarsio, corona parziale, mezza
---------------------

2. Step

SELECT FIND_IN_SET('b','a,b,c,d');
ok, here i can find in what field my searchterm is located

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

3. step

SELECT MAKE_SET(1,'alles ist gut','bist du ok','chupi dapi');
that should give me the Result of the Find_in_Set as Content


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

and now how to bring that all together, i guess that would be the solution

like: (i know it works not, i did not combined select functions before and dont know right now how:)

SELECT FIND_IN_SET
('%cor',

(
SELECT keywords
FROM `cdent_movies`
WHERE `keywords` LIKE '%cor%'
LIMIT 0 , 30
)

)

any Idea?

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Tue Dec 30, 2008 5:46 am
by guruk
ok, comes funny.
I saw make_set is bitoriented. so not just 1 = first entry and 3 = 3rd.. (3 would make 1&2 like 3 = bin 11)

Ok, so i have a table like
make_set (1.... = 1 entry
"" 2 = 2nd entry
4 = 3rd entry
8 = 4th entry
16
32
64

... but the good thing.. i dont have to do anything with left or right operations.
Just to know before I need lets say the 4th entry.. i have to put in Make_set (8, ---)

but there are still some steps.

1. i do my normal search for %cor%
2. around (with that results) I search find_in_set .. just Where my keywords in that results are located
3. change that numbers with the content

so for example a list like:

id keywords
id1 Levigatura concava, Levigatura corana, concava
id5 Protesi ibrida, protesi, ibrida, ancoraggio, palat
id12 Corone parziali, intarsio, corona parziale, mezza

from my 1st Search only for keywords with %cor%

2.nd after "Find_in_Set" i would need

id whereinkeywords
id1 2
id5 4
id12 1
id12 3

in in 3rd step just after "Make_set"

id contentfound
id1 Levigatura corana
id2 ancoraggio
id12 Corone parziali
id12 corona parziale

:)
i guess ...

Re: Complex Explode Search! Please Help [auf Deutsch, bitte]

Posted: Tue Dec 30, 2008 6:03 am
by VladSun

Re: Complex Explode Search! Please Help

Posted: Tue Dec 30, 2008 1:28 pm
by califdon
Just a bit of history to clarify why SQL doesn't include syntax for recovery from non-normalized databases. Before there was SQL, the theory of relational databases was developed by an IBM mathematician, Dr. E. F. ("Ted") Codd, around 1970. Codd built a whole calculus based on certain rules of "normal" database structure. When these conditions are met, the mathematics guarantees that results will be consistent. The conditions, or rules, are now recognized as the "normal forms" of relational database structure. The very first rule of normalization is that values stored as an attribute (or field) must be "atomic", that is, single-valued, or putting it a different way, cannot be divided into smaller values. This is called "First Normal Form" or 1NF.

SQL (Structured Query Language) was developed to implement Codd's description of a relational database. Thus, if a database is normalized in accordance with Codd's rules, and you use SQL, all operations are guaranteed to work consistently. Of course, you can violate some of the rules and still get valid results--some of the time! But there are no longer mathematically proven assurances. This is why SQL doesn't support splitting an attribute; attributes are required by the rules of normalization to be unsplittable! You can surely program operations in your application code to solve a specific problem, but it would violate the foundation of relational databases to allow SQL to perform un-guaranteed operations on data.

There's lots of information on this, such as at Wikipedia.