hi guys i am trying to populate how many products are available within a certain category in Mysql database but i am unsure of the sql query to run. the 2 table examples are below.
Table 1 with fields id/category/product. field "id" is an auto increment field and can be ignored.
the layout is referenced below
i need to somehow get a total amount of products that are within each category i.e in the example above there would be a total products of 4 and they are within category 1 and 1 item within category 4
Table 2 has fields named id/category/product. the category names only appear once so go from 1 to 20 for example but i must update the field within this table named "product" and put the amount of products found within table 1 into each category
So if the query ran i would expect an update of field "product" in table 2 to then change to the below
hope this is easy enough to follow and thank you in advance for your help