Category, Subcategory?

[alex1985 0]

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 0]

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

[alex1985 0]

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 0]

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

[galexcd 0]

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 0]

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

[galexcd 0]

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

[Framp 0]

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 try to write it yourself.

[alex1985 0]

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

[alex1985 0]

Anymore replies, still waiting?!

[rvalkass 5]

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

[magiccode9 0]

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,

[alex1985 0]

Just show me the instance of it!

[salamangkero 0]

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

[alex1985 0]

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