Category, Subcategory?

alex1985

Jun 11 2008, 12:36 PM

Listen, I would like to create some menu which list categories describes the news that category contains as well as sub-category below it. So, the questions is how I can count the number of news or items in the category or sub-category?

This is example:

Books (100)

-Finance (25)
-Biology (75)

Any ideas?

Galahad

Jun 11 2008, 12:49 PM

I suppose you are using MySQL to store data, so you could simply use COUNT(*) in SQL query, to count categories and subcategories...

Something like

CODE

SELECT COUNT(*) FROM `news` WHERE `category`=1;

And that would return only one row and one field, containing number of news in the table, that correspond to the query you used...

alex1985

Jun 11 2008, 01:56 PM

QUOTE

And that would return only one row and one field, containing number of news in the table, that correspond to the query you used...

Just, show me the sample code if you can.

alex1985

Jun 12 2008, 08:11 AM

You, guys, to give up against my questions?!

galexcd

Jun 12 2008, 03:54 PM

I think the best way of doing this is to have two tables. One called Category the other called subcategory.
Under the category table you would have ID and NAME.
Under the subcategory table you would have ID, SUPER, and NAME.

The super value under the subcategory would point to the ID of its supercategory.

alex1985

Jun 12 2008, 06:27 PM

I'm a novice, just when you finish write your code for it as well.

galexcd

Jun 12 2008, 09:44 PM

You haven't even said what you want to do with the database yet, how are we supposed to know what kind of code to give you when you haven't even fully explained what you want? Do you want to know just how to set up the database? Well you can do that much easier with phpmyadmin, but i suppose I could whip out something for you really fast.

CODE

CREATE TABLE "category"(`ID` INT(3) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(35) NOT NULL, PRIMARY KEY (`ID`))
CREATE TABLE "subcategory"(`ID` INT(3) NOT NULL AUTO_INCREMENT,`SUPER` INT(3) NOT NULL,`NAME` VARCHAR(35) NOT NULL, PRIMARY KEY (`ID`))

Framp

Jun 13 2008, 08:22 PM

To write the code with these tables you simply have to retrieve data with a couple of mysql queries.

With a query select all the categories and then loop through their values.
In the loop you've to print the menu category and then to query all the subcategories with the SUPER id corrispondent to the category id. With this second loop you can simply print all the subcatergories

The code isn't so difficult

try to write it yourself.

alex1985

Jun 13 2008, 09:39 PM

The code itself just creates database values, I need the one that counts, the code that links those two databases.

alex1985

Jul 2 2008, 04:24 AM

Anymore replies, still waiting?!

Latest Entries

alex1985

Aug 10 2008, 03:28 AM

Thanks. I was waiting for this answer for several months!

salamangkero

Jul 31 2008, 04:44 AM

QUOTE(alex1985 @ Jul 31 2008, 06:03 AM)

Just show me the instance of it!

Ok, assuming that your news articles are represented as filenames, suppose that you have the following table:

CODE

CREATE TABLE news (
  `ID` INT(3) NOT NULL AUTO_INCREMENT,
  `FILENAME` VARCHAR(128) NOT NULL,
  `CATEGORY` VARCHAR(32) NOT NULL DEFAULT 'misc',
  `SUBCATEGORY` VARCHAR(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY INDEX_CATEGORY (CATEGORY),
  KEY INDEX_SUBCATEGORY (SUBCATEGORY)
);

This will give you the following table:

CODE

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| ID          | int(3)       |      | PRI | NULL    | auto_increment |
| FILENAME    | varchar(128) |      |     |         |                |
| CATEGORY    | varchar(32)  |      | MUL | misc    |                |
| SUBCATEGORY | varchar(32)  |      | MUL |         |                |
+-------------+--------------+------+-----+---------+----------------+

Now, let's insert some dummy records:

CODE

INSERT INTO news (FILENAME, CATEGORY, SUBCATEGORY) VALUES
("hp01.php", "Books", "Harry Potter"),
("hp02.php", "Books", "Harry Potter"),
("hp03.php", "Books", "Harry Potter"),
("kl01.php", "Books", "tlhIngan Hol"),
("nz01.php", "Politics", "Adolf Hitler"),
("nz02.php", "Politics", "Adolf Hitler"),
("as01.php", "Politics", "Arnold Schwarzenegger"),
("as02.php", "Politics", "Arnold Schwarzenegger"),
("es01.php", "Genius", "Albert Einstein"),
("ph01.php", "Genius", "Paris Hilton");

As you can see, we have three categories: Books (4),Politics (4) and Genius (4)
Books has two subcategories: Harry Potter (3) and tlhIngan Hol (1)
Politics has two subcategories: Adolf Hitler (2) and Arnold Schwarzenegger (2)
Genius, too, has two subcategories: Albert Einstein (1) and Paris Hilton (1)

Now, to get how many news articles you have under the category Books, use the following statement:

CODE

mysql> SELECT count(1) FROM news WHERE CATEGORY = "Books";
+----------+
| count(1) |
+----------+
|        4 |
+----------+

On the other hand, to see how many articles you have under the subcategory Paris Hilton, use the following statement:

CODE

SELECT count(1) FROM news WHERE SUBCATEGORY = "Paris Hilton";
+----------+
| count(1) |
+----------+
|        1 |
+----------+

Well, there you go. If you need any more information, it certainly wouldn't hurt to ask in a polite manner, be patient and non-aggressive. It definitely pays to be nice to people you're asking help from. Most of all, a lot of us developers here took the time to learn PHP and MySQL, which is why we know these things. You certainly cannot learn simply by bullying other people to give you the information that you want, when you can just as easily search online resources.

Ah, but I'm going off-topic. In any case, have a nice day

alex1985

Jul 30 2008, 10:03 PM

Just show me the instance of it!

magiccode9

Jul 2 2008, 01:51 PM

You may try this

Create a table like this

CODE

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| cat_id    | int(11)     | NO   | PRI |         | auto_increment |
| parent_id | int(11)     | YES  |     | 0       |                |
| name      | varchar(50) | NO   |     |         |                |
+-----------+-------------+------+-----+---------+----------------+

then, use this select query

CODE

SELECT parent_id, COUNT(parent_id) FROM testcat group by parent_id

Eric,

ADD REPLY / Got an Opinion!	a humble request :-)	RAPID SEARCH!	Free Hosting	[X]
Express your Opinions, Thoughts or Contribute more info. to help others. Ask your Doubts & Queries to get answers, So that "Together We can help others!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE

Category, Subcategory? - !

Category, Subcategory? - !

Latest Entries