Explode comma delimited field for tagging system

[Catherine85]

Hey!

 

I have a table and in that table I have a "name" field, and a "description" field. I'm trying to make a tag system for these rows.

 

I'd like to use a denormalized approach because I can't wrap my head around a normalized approach.

 

I don't have a problem adding, for instance, catid_1, catid_2, catid_3 columns and do a WHERE statement to fetch only rows with that particular column IE: $sql = mysql_query("SELECT * FROM tablename WHERE catid_1='categorynameortag' ORDER BY DESC")

 

How would I make it so rather than having individual columns, I can put all that data into one field with comma's. Then also, how would I fetch and display  only rows with one of the words in the field?

 

Thank you for your help.

 

 

 

[QuickOldCar]

To insert into array use implode.

http://php.net/manual/en/function.implode.php

 

To fetch can explode

http://www.php.net/manual/en/function.explode.php

 

Prob better off using a pipe though...  |

 

I would think having each category as it's own table and associate to the id be best approach.

 

Look into wordpress database structure using terms as an example

 

[johnny86]

I think that it's even better to have them in seperate columns than in one column. That might not be normalized but the whole point of a database is that you can grab just the data you need and not having to parse through your grabbed data for more data. You should have your categorys defined in one table and link their ID to other tables. And if you need to link many categorys to one record, you could have a table that has:

 

RECORD_ID  |  CATEGORY

        1                  3

        1                  6

        1                  7

        3                  2

        3                  6

 

Now you could do something like: SELECT category FROM relations WHERE record_id = 1;

 

That would return three rows so you know it's linked to three categorys and you know the ID's of those categorys.

 

You can put that all together in one statement using JOIN to join those categorys to the result according to the result row in that. But for starters you can just grab that info and make another query according to that.

 

I hope I made some sense

[Catherine85]

Okay, thanks.. Your method is normalized. I guess I'll have to learn how to join tables etc. Thanks very much.

 

I think that it's even better to have them in seperate columns than in one column. That might not be normalized but the whole point of a database is that you can grab just the data you need and not having to parse through your grabbed data for more data. You should have your categorys defined in one table and link their ID to other tables. And if you need to link many categorys to one record, you could have a table that has:

 

RECORD_ID  |  CATEGORY

        1                  3

        1                  6

        1                  7

        3                  2

        3                  6

 

Now you could do something like: SELECT category FROM relations WHERE record_id = 1;

 

That would return three rows so you know it's linked to three categorys and you know the ID's of those categorys.

 

You can put that all together in one statement using JOIN to join those categorys to the result according to the result row in that. But for starters you can just grab that info and make another query according to that.

 

I hope I made some sense

[johnny86]

Well a quick peak for joining information like that, imagine a situation:

 

TABLE videos ( id , name , description )

TABLE categorys ( id , name , description )

TABLE cat_vid ( vid_id , cat_id )

 

Now I want to grab all categorys that are linked to video with an id of 10:

 

SELECT * FROM cat_vid JOIN categorys ON cat_vid.vid_id = 10 AND categorys.id = cat_vid.cat_id

 

A little explaining:

 

SELECT * FROM cat_vid // Self explanatory

JOIN categorys ON // This is like saying JOIN info from categorys table when following conditions happen

cat_vid.vid_id = 10 // Our relation table cat_vid ---> select all videos with an id of 10

AND categorys.id = cat_vid.cat_id // Also check that the row we are about to join(from categorys) has the same id as cat_vids row cat_id

 

Now I would have a table consisting of:

cat_id  vid_id  id  name    description

|__________|  |_________________|

        |                            |

From cat_vid          From categories

 

So there is a double category id now which can be resolved not selecting it to the results. I hope this helps a bit forward.