How to count the number of materials in the catalog?

Question

PHP

Catalogs are stored in one table, materials - in another (ID connection via Foreign key). When you go to the main site module, directories should be displayed indicating the number of materials in each.
What is the best way to do this:
a) add a field to the catalog table, upon request of which MYSQL will count the number of materials with the current catalog_id in the materials table (is it possible to do this for one cell? Or will it be necessary to create a view?)
b) when adding material to the database trigger an increment of the number of materials in the directory table
c) use the DomainObjectAssembler interface in PHP and call the count query for each directory
d) increment the directory field when adding material via DomainObjectAssembler in PHP

Answer 1

You can make a request like:

SELECT categories.*, (
    SELECT COUNT(id) 
    FROM products  
    WHERE products.category_id = categories.id
) AS products_count 
FROM categories;

But it is better to add a column for the amount of materials to the catalog table, to add an INSERT/DELETE trigger in the material table, which would increase/decrease the counter of the amount of materials in the catalog table.

Answer 2

As an option: you get an array from the database anyway, it turns out count($my_array) should solve your problem

Answer 3

There need a nested query no more