imploded a set of number and can't get them to pull from database

bkatke

ok I have been looking over the databases for a month now and i have gotten alot of good information but i just can't seem to get past this problem and although the searches i come out with come close, they just aren't solving my specific issue. so i come to you with this dilema today.

background: the code i am manipulating is for an ecommerce site. that sells printer cartridges. obviously one cartridge can work for multiple printers so i have set the system up (as per the consumers request) to have one category for each printer model they have listed so my database has multiple categories for 1 product. (many to many relationship) most of the code for this system is already written and so creating a linking table will be more effort than it's worth. so what I have done is this

the user adds the printer and ctrl selects a list of categories on the form. this works great. it implodes the information into the database cat_id field nicely. so for example the table looks like this

productID ProductDesc cat_id

obviously there are more fields but just to simplifly this because they other fields don't matter i just put those.

the cat_id field has a set of numbers that corresponds with the categories this product applies to so the actual field looks like this..

17,18,21,22,23,25,27,29

so on and so forth.

onto the real issue here

when i use the statement

SELECT * FROM store_inventory where 18 in (cat_id)

it doesn't come up with any results but change that 18 to a 17 and the results will come up with the product. so what it looks like its doing is returning results where the value of the number is equal to the first number in the cat_id comma seperated list and no other time.

there is alot more to the system than just this string but this is pretty much where all my troubles boil down to is that select line listed above. any advice on this issue would be greately apprieciated.

Brian

tekky

I would suggest using '18' or using WHERE id LIKE '%18%'

this LIKE method would work so long as you dont go into numbers like 180 and such...
as %18% would much 118 018 18 etc etc

try quotnig the #'s first though...

bkatke

i tried it that way a long time ago. unfortunately it is the case where there will be records like 180 118 and so on so i am in need of figuring out what is wrong with the "IN" statement i'm trying to use here.

I apprieciate the thought but can't work that way.
and actually i think someone summed up the problem i am having where the system is looking at the statement (littleraly) as

select * from store_inventory where 18 in ('17,18,22,23') and not the proper way of

select * from store_inventory where 18 in (17,18,22,23) because it is in a varchar field. so i need to figure out how to eliminate the quotes before and after the number set. so thats where i'm stuck.

i am not to sure if this is a symtax problem or what so if someone has another suggestion, please post it here

tekky

ok... here's a possibility....
(actually the method I think I would use)

rewire the storage line for the '17,18,19,23,24,25' field....
to include the comma at the beginning and end, so that it reads ',17,18,19,23,24,25,' or even ',17,,18,,19,,23,,24,,25,'
(adding ",#," each time...)

then you can just use LIKE '%,18,%'

bkatke

will that cause problems when i explode it because i need to be able to edit the fields in the future

tekky

shouldnt, I've used explode on items that end in the exploded char like x.x.x.x. and explode("."....);