Desperate for Answer, 2 months looking

sbroad

Forward Info SET & ENUM field will not work for me. As this data is dynamic is those field types are limited.

I have a table with a VARCHAR field called "GROUPS". In that field I will store values such as (1,2,3,4,2,1,5). What I would like to do is group by that SET of numbers.

I am beyond stumped, I cannot figure how to get my sets out indivudally so I can group them. If I can even get the groups to list I will be happy.

Example (DBASE)

[Name] [groups]
dog 1,3,4,5,2
Cat 2,4,5
Bird 3,1,2

Now I need a sql statement which will group based on the sets

+++++++++++++++++++++++++++++++++++++++++++++

Group 1

DOG

BIRD

GROUP 2

DOG
CAT

BIRD

GROUP 3

DOG

GROUP 4

DOG

CAT

GROUP 5

DOG
CAT

I WOULD EVEN BE HAPPY WITH THIS
++++++++++++++++++++++++++

DOG 1
DOG 2
DOG 3
DOG 4
DOG 5
CAT 2
CAT 4
CAT 5
BIRD 1

Just a long as I can get the data to display based on the group information. This is really holding me back.

Thanks

volleytotal_ch

why don't you save the groups in a second table? makes it much easier to search ...

example:

table "animals"
[ID] [name]
1 dog
2 cat
3 bird

table "groups"
[anim_id] [group]
1 1
1 2
...
2 2
...

With those table you can search for the groups
SELECT g.group, a.name FROM groups g, animals a WHERE g.anim_id=a.id ORDER BY g.group ASC

hth

sbroad

I did have it set up that way. But it is just not the logical choice for me to go this route. Other wise for every table that needs goups I will need 2 more tables to hod different group information for that table.

Kind of hard to explain, but it needs to be this way.

I can do this by writing an iteration in php for each group number and then doing a find_in_set() to get the info.

But mysql has support for sets, so I would think it would be possible to get that data out the way I want.

It would be nice to for instance

select (1,2,3,2,1)
And have it retun as seperate wors
1
2
3
2
1

Mysql can make sets, and find in sets, but it cannot output the data this way. It doesn't make sense, I have to be missing something.

ratass2002

HI:

What you need is to use 3 tables:

Table1: Animal info
AniamlID:
Name:
Descr:

Table2: Group Info
GroupID:
Name:
Descr:

Table3: Animal grouping
GroupID
AnimalID

Now Group and Animal are combine through a third table. In theory this is the easiest way to go about it.
Otherwise I think you have to get the set then manually display the result one by one.

sbroad

Let's say you had a file which you wanted to implement security on. The user security is stored as id#'s in the table.

So....

File Security
mupic.jpg 2,5,3,7,15,22,101,33

Another way I do this is to create navigation. For instance:
my Message | 001,002,001,005

I have done something like this with relational tables and then did it with comma delimeted sets. The comma delimeted sets were three times after and much easire to work with. There are times to use relational DB's, this is not one of them.

These users would all have access to this file. I could make a secondary table and join the two together. But I would wrather store the data in a field and keep everything as a flat table.

The other problem is I also have user groups and file groups which will also be linked. If I create table for all of this, it is going to get harry.

Performance wise, relational DB for this will provide better readability and possibly a little more control, but I am going to take a hit on performance. And I will also need to do more programming for a variety of reasons.

The reason is, I have alot of tables that are going to need this kind of security. If I have to set up additional tables for security I am basically looking at 1 table + 2 tables for security. If I have 10 tables then I need 30 all together which is alot of maintenance.

Mysql has support built in for this type of activity. So I want to take advantage of what mysql is already offering. This stuff was created by MySql for this reason, for creating navigation and so on.

This is actually a much better method for me to deal with and requires far less programming and maintenance.

But I just need to know how to group on these fields. The abiliy must be there, MySql supports everything else but this. Hard for me to believe.

Alot of writing, uh? 🙂

sbroad

<NEXT>([[:alnum:]]|_)*::🙁([[:alnum:]]|@)?|([[:alnum:]]|@)+)(SEARCH INFO)+

ratass2002

what was the answer?

sbroad

I found out the MySQL 3.23 does not suppot Perl Regular expressions. It supports the older style Reg Exp that are used by awk and so forth.

I am not familiar with the older style, but I was able to write the above uglty regexp to get me through the problem.

I hope mysql 4.0 will support perl (prce) regular expression.

[deleted]

Just use a proper relational model, the 'comma delimited values' method just doesn't work and you know that you have no valid reason for not using a relational model.

" The reason is, I have alot of tables that are going to need this kind of security. If I have to set up additional tables for security I am basically looking at 1 table + 2 tables for security. If I have 10 tables then I need 30 all together which is alot of maintenance. "

Seems like you're missing the point of relational database design.

You would ofcourse only add two tables for the security and use them to store the security of all other tables. The rights work the same way for all tables, so you can just add a 'tablename' column or something and use that to define which tables the rights are for.

sbroad

Geeze. I responded to the wrong board with my answer to the problem. 🙂 No wonder you are asking me to normalize the data.

In regards, to your quest, vincette, I store the customers data in XML in a blob, that data is ENCRYPTED. I build md5 hashed on some of the more common searches and store that into fields and search on the md5's which is fast and has worked great on large searches.

As far as the comma delimeted stuff goes, I realized I was making a small error in normalization techniques. I did end up creating a GROUP table which all of my tables could use to join to, to get that data. It would prov to be way more efficient. But I left security in the comma delimeted format because I do not need to extract the data, just search on it.

I am still curious though, no matter what happens, I would still like to know how to explode/display comma delimeted fields in MySql. It is one of those things that are just urking me!

Mysql supports creating these sets, and searching on these sets, but not extracting them out seperately. There has got to be a way.

[deleted]

" I do not need to extract the data, just search on it. "

That's the point, you cannot search it without extracting it. Ofcourse you let the database extract the data, but you're still extracting.

That makes it much more efficient to 'properly' normalize the data and let SQL do an indexed 1:1 comparison on the values.

" Mysql supports creating these sets"

What sets are those? You are just using comma seperated values in a string...

sbroad

Lookup

find_in_set
makeset

and so forth.

There is alot of support for sets in mysql, I am not talking about 'SET' types fields, but traditional sets in the comma delimited sets which can be used in any type of string field.

It works very well, and is fast on the query. Alot of mysql functions exist for this type of work.

Example
Field Name 'Security'=2,10,8,3

SELECT * WHERE find_in_set(2,'security')

Works great and is accurate. Try it out, as far as I know I have not had performace problems with this format.

If you know of something that says this is terribly inneficient, let me know. But I have used this on tables with 50,000 records that I build and it has worked fine and I have noticed very little degradation.

It is also nice to have the data in one spot without joining to another table.

Have you used the find_in_set function?

[deleted]

The biggest problem with this method (as with most of the mysql functions) is that it doesn't support consistency at all.

The values in the 'set' can be anything, even non-existing values.

Note also that there is no add-to-set or 'remove from set' function, which means that if some right is revoked or becomes irrelevant alltogether, you will have to write a routine to process all records manually in order to remove the value from each CSV.

With the 'proper' method this change is a matter of running just one DELETE query to remove the value from all the rights-detail rows.

On top of that you are storing strings instead of integers, which means that you store a lot more data than you need to. Not that that is a buig problem, but when you get to a decent size database (2-5 million rows) it does add-up.

sbroad

Because you are right! 🙁

I sat down and thought about it and decided to draw out a new schematic. I conserdered the possibility of huge amounts of data, and in its current form this will work fine. But once it really grows, I will run into problems and that is because I cannot index the field (by unique numbers) properly and so forth. And not to mention problems in your new post if the functions cease to exist.

So I sat on the couch and consered what you said and decided to change direction to relational. It made more sense then the original design and actually offers a ton of new possibilities that I did not consider before, which I did not need to until the project grew in size. Short sightedness kills!

Damn you! 🙂 But thanks for the info. Sometimes you need a another point of view to get you back on the right track.

[deleted]

Hehe.. I told you so! 🙂

The relational model is hard to beat, expecially on large volumes of data.

But anyway, good luck with your project and if you have more questions, do post them here and we'll have a look!