Jump to content
xisto Community
alex1985

Category, Subcategory?

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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

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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


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`))

Share this post


Link to post
Share on other sites

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 subcatergoriesThe code isn't so difficult :D try to write it yourself.

Share this post


Link to post
Share on other sites

Look into using the MySQL COUNT function. This allows you to count the number of rows returned by a query. For example:

SELECT COUNT(*) FROM `books` WHERE `category` = 1

 

Not having your database structure available, I can't really be any more precise than that.

 

For more information, consult the MySQL manual: http://dev.mysql.com/doc/refman/5.7/en/counting-rows.html

Share this post


Link to post
Share on other sites

You may try this

Create a table like this

+-----------+-------------+------+-----+---------+----------------+| 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

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



Eric,

Share this post


Link to post
Share on other sites

Just show me the instance of it!

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

  `SUBCATEGORY` VARCHAR(32) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`),
KEY INDEX_CATEGORY (CATEGORY),
KEY INDEX_SUBCATEGORY (SUBCATEGORY)
); linenums:0'>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:

+-------------+--------------+------+-----+---------+----------------+| 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:
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:
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:
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 :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.