How you can execute this query in pure SQL, no PHP?

Now there is this code:
$categoryList = "SELECT `id` FROM `category`;";
$categoryList = mysqli_query($_MS_ID, $categoryList);
while($category = mysqli_fetch_array($categoryList)) {
 mysqli_query($_MS_ID, "UPDATE `category` SET `count` = (SELECT COUNT(`id`) FROM `product` WHERE clause 
 `category` = {$category['id']} AND `status` = '0') WHERE `id` = {$category['id']};");
}

He gets all the categories and counts the number of free goods.

Is it possible to get rid of the loop PHP and implement the conversion in all categories with a single SQL query? If so, how. Thank you :-)
June 27th 19 at 15:02
1 answer
June 27th 19 at 15:04
Solution
You have it right.)))) the current bit to think out - UPDATE can Lope anything to do subqueries
UPDATE `category` SET `category`.`count` = (
 SELECT COUNT(`product`.`id`) 
 FROM `product` 
 WHERE 
 `product`.`category` = `category` .`id` AND `product`.`status` = '0'
)
Thanks for the solution :) - quinton_Daugherty83 commented on June 27th 19 at 15:07
: To thanks there is a button "Like" and "Noteworthy decision"))) - cleora commented on June 27th 19 at 15:10

Find more questions by tags MySQLPHPSQL