[SOLVED] searching patterns with a group of id's

[mungyun]

Im not sure if this would require more php or sql to do so im sorry if it is in the wrong place but I have this column in my table that contains entries with various part id's separated by a delimiter that i need to be able to match. Something like: "2|6|7|9|1"

 

On the users side they will select various parts and i need to take the id's from their selections and compare it to find closest matches

 

this is for a machines search by parts that needs to return by relevance so the table column would be like this:

 

[machine_part_ids]

"1|5|6|3|9"

"9|1|7|5|23|21"

"18|4|23|22"

"5|1|23|7"

 

and the user has selected parts: 1, 5, 7 and 23

 

i want to be able to return the results that have the closest match to the least closest. So the above column entries would return in this order:

"5|1|23|7"

"9|1|7|5|23|21"

"1|5|6|3|9"

"18|4|23|22"

 

If it would make it easier, i could enter the id groups in order but im not sure if that would be needed

 

I hope i explained this good enough and if i need to explain more i would be glad to.

 

Any help is greatly appreciated =-)

 

Thanks,

 

Matt

 

 

[Barand]

Read up on data normalization, re-design the tables and try again.

[mungyun]

that is exactly what the direction i needed to go. thanks!  =-)