How do I make mysql_num_rows of various results

Question

Well, what I want is this:

I have 1 table designated by items, in this table I have 4 columns:

• nomeitem
• iditem (Single id)
• numerobot

What I intend to do is: I want to do a mysql_num_rows of all results, but for each different item name.

That is to say I have 10 results with the name "Shadow Case", 15 results with the name "Gamma Case".

What I intend to do is to do this, so that you know the amount of each item I have.

To finish I intend to take these values, and update another table that is called infoitems. This table only has 2 columns that are item and quantity, where in the quantity I intend to put the respective previous result, for each item.

I hope you understand me.

Answer 1

Can be done directly in a query in Mysql:

SELECT nomeitem, count(nomeitem) as quantidade FROM tabela GROUP BY nomeitem;

• https://dev.mysql.com/doc/refman/5.7/en/counting-rows.html

With this you have the amount of each item.

You can take the result in a Mysqli_result, or inside you can update directly to another table:

INSERT INTO outra_tabela (nomeitem, quantidade) 
SELECT nomeitem, count(nomeitem) as quantidade FROM tabela GROUP BY nomeitem;

• https://dev.mysql.com/doc/refman/5.7/en/insert-select.html

Answer 2

The mysql_num_rows will not solve easily.

You can do a normal query that returns the total of each item:

SELECT nomeitem, COUNT(iditem) as total FROM tabela GROUP BY nomeitem;

This query will return something like this:

nomeitem       |total
----------------------
Shadow Case    |10 
Gamma Case     |15

Another thing, the functions mysql_* are depreciated, preferably the mysqli_* or the class PDO.

http://php.net/manual/en/mysqli-result.num-rows.php http://php.net/manual/en/pdostatement.rowcount.php