Reading results with a comma.

gat

I have a field in my DB that holds values that are imploded like

1,2,4,5

1,5,6

I have an SQL statement that searches for all images with these values.

however at the moment, its only reading the 1st value. ie. '1' in this case.

this is my SQL (its a bit long, joining tables).
can this be made to read in the commas?

"SELECT * from artwork 
LEFT JOIN art_cat 
ON artwork.art_id = art_cat.art_id
LEFT JOIN sub_cat
ON art_cat.sub_cat_id = sub_cat.sub_cat_id
WHERE sub_cat_name = '$cont'"

it is the 'sub_cat_id' that i need to read with the commas?

can this be done?

AstroTeg

You appear to have a database design problem. The array elements need to be represented in their own field or row (depending on what they are to represent). By doing so, it'll be a ton easier to query and maintain the data.

gat

I did not explain the problem very well. here we go again

Ok i have this SQL that inserts two vars into an art_cat table..

PHP Code:
$sql2 = mysql_query("INSERT INTO art_cat (sub_cat_id, art_id) VALUES ('$var', '$art_id')");

however, the var '$var' holds the imploded data, e.g

1_2_3_4_5

so when the data is entered into the DB, i get the following

art_id sub_cat_id

69 1_2_3_4

I would like the infor to be added like this though

art_id sub_cat_id

69 1
69 2
69 3
69 4

could someone show me how i would need to alter my SQL for this to work.

would i need a 'for each' loop according to the $var variable, then for each number, insert the art_id and number.

this would make all my other code work correctly.
thanks.

laserlight

AstroTeg's point is that the way you store data is wrong to begin with.
If you take his advice, you wouldnt have such a mess to work with.

gat

ok i will explain my structure (as im sure its ok)

This is my table structure
http://art.uk-graffiti.co.uk/erd_artwork2.gif

so the point of the art_cat table is as there is a many to many relationship between artwork and sub_categorys.

When i submit an image to the gallery i select one of many sub_cats via a range of check box's.

each check box holds a value which is the same value of the sub_cat_id in the sub_cat table.

Is this the wrong thing to do?

AstroTeg

Looks like you have an art ID and a subcategory ID. You're on the right track by saying you'll take the art ID and associate it to 1 subcategory per row (this is a one-to-many join).

You are right about looping (or using foreach()), but you do that when inserting. For querying, you can inner join the subcategory table based on the join you have there.

This probably isn't overly clear and its because I don't have enough info to how your database is layed out to give you specifics...

AstroTeg

Oops - your post went up before my reply.

Looking at your table structure, it looks good.

I would expect table "Artwork Category" to contain two columns at least. One being for the art work ID and the other being for the subcategory ID. You might have a third which keeps track of the order of the subcategories, but that's up to you. That should be about it for that table. Each row in that table would represent one art ID and one subcategory ID. Each combination should be unique.

Using joins, you can fish out the artwork and the subcategories as needed.