[SOLVED] Multiple Items in Column

[Onloac]

I have another problem I need help with. I tried to search on google but I'm not exactly sure what I'm searching for. When I add an article to my site I can select categories to list it under via check boxes. I was wondering how I would go about adding all the categories selected (numbered 1-20) into one table column. Then I want to know how I go about sorting them after they are added. I just need to be pointed in the right direction... perhaps a tutorial or webpage out there that covers this. thanks in advance!

[trq]

You wouldn't store them in one field.

 

You would need to use several tables of normalised data. You then use joins, to join this data. There is a good tutorial on our main site. Here.

[Onloac]

I'm just starting out with PHP/MySQL and that seems to be a little more complicated then I was expecting. Currently I have a table within my database that holds my categories (Category ID, Category Name) and I'm just trying to find away to have it input all selected categories into a category column within my article table. So for example if I selected categories 1,3,4,5 it would add it into the article category column as "1,3,4,5" that way I can extract that information after and find out what categories it was filed under. I'm guessing there has to be a simple and effective way of doing this?

[haku]

You didn't read Thorpe's answer:

 

You wouldn't store them in one field.

 

He is spot on with that. You could do it, but you would be using the database the wrong way, and you would lose out on all sorts of database capabilities - being able to sort by category, or just pull out rows of a certain category etc.

[Onloac]

So your saying that having a column for each category in my article table is the better way to do it? then just have 1 for selected/0 for not selected and join the results from all those columns to sort my categories and such?

[trq]

No. At minimum you need three tables. One for articles, one for category types and one to hold the relationships. eg;

 

CREATE TABLE categories (
  id INT NOT NULL AUTO_INCREMENT,
  cname VARCHAR(80),   
  PRIMARY KEY (id)
);

INSERT INTO categories (cname) VALUES ('php');
INSERT INTO categories (cname) VALUES ('linux');
INSERT INTO categories (cname) VALUES ('foo');


CREATE TABLE articles (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(80),
  content TEXT,   
  PRIMARY KEY (id)
);

INSERT INTO articles (title, content) VALUES ('my first php article', 'an article about php');
INSERT INTO articles (title, content) VALUES ('installing php on linux', 'this is a howto');
INSERT INTO articles (title, content) VALUES ('another php article', 'blah blah blah');


CREATE TABLE article_to_category (
  id INT NOT NULL AUTO_INCREMENT,
  cat_id INT,
  art_id INT,   
  PRIMARY KEY (id)
);

INSERT INTO article_to_category (cat_id, art_id) VALUES (1,1);
INSERT INTO article_to_category (cat_id, art_id) VALUES (2,2);
INSERT INTO article_to_category (cat_id, art_id) VALUES (1,3);

 

See the relationship?

[Onloac]

I know what your suggesting, but let me see just in case.

 

Your trying to say that its better to store that information in a relationship table and use that to determine the category? I'm not sure if I'm understanding this corrently but wouldn't that mean that I'd need to have 4-5 rows within that table per article when an article is filed under that many categories?

 

For example. Lets say my article id is 55 and it has been filed under 5 different categories numbered 1-5. When adding it to the table it would be listed as:

 

55, 1

55, 2

55, 3

55, 4

55, 5

 

Right?

Meaning I would need to give 5 rows in the relations table to record that articles categories? I have about 20 articles posted daily, some of which belong to 5-6 categories. I'm sure you can imagine over time how many articles can gather in a database. Would it be wise for me to use this method for so many articles and am I right with the above example?

[Mark Baker]

Your trying to say that its better to store that information in a relationship table and use that to determine the category? I'm not sure if I'm understanding this corrently but wouldn't that mean that I'd need to have 4-5 rows within that table per article when an article is filed under that many categories?

That's right.

I have about 20 articles posted daily, some of which belong to 5-6 categories. I'm sure you can imagine over time how many articles can gather in a database. Would it be wise for me to use this method for so many articles and am I right with the above example?

The database software is designed to handle large volumes very efficiently in this manner: what you're talking about isn't even large volumes (although it may seem so to you).

I work with several million new records daily, each of which has many categories, and I use exactly these methods without overloading the database. The cross-reference tables are the largest in number of rows, but tiny in terms of actual data volumes.

Google will use similar techniques, as do many other organisations that store many gigabytes and even petabytes of data.

 

If the database tables, relationships and indexes aren't properly designed, then it can cause problems as data volumes grow; but if properly designed around the principles in the article that Thorpe referenced, then it's extremely efficient.

[Onloac]

Thanks guys. I'm really learning alot from this site.