while 的困擾，如何改善成只要一個查詢？

Question

$product_data = $mysqli->query(
        "SELECT
          p.url as url,
          p.description as description,
          p.info as info,
          p.note as note,
          p.content as content,
          p.name as pname,
          p.icon as icon,
          p.price as price,
          p.original_price as original_price,
          p.prod_id as prod_id,
          p.stock as stock,
          p.views as views,
          s.name as sname,
          c.name as cname,
          p.add_time as add_time
        FROM `product` as p
        JOIN
          `store` as s ON p.store_id = s.store_id
        JOIN
          `category` as c ON c.category_id = p.category_id
        WHERE
          p.active = 1
        ORDER BY `prod_id` DESC

while($row = mysqli_fetch_array($product_data)){
$theme = $mysqli->query(
        "SELECT
        t.name as tname
        FROM `product` as p
        JOIN
          `product_theme` as pt ON p.prod_id = pt.prod_id
        JOIN
          `theme` as t ON t.theme_id = pt.theme_id
        WHERE
          p.prod_id = '{$row['prod_id']}' "
      );

      $buyTotal = mysqli_fetch_array($mysqli->query(
        "SELECT sum(quan) as quan FROM `stock`
        WHERE
          `prod_id` = '{$row['prod_id']}' "
      ));

      $viewsIp = mysqli_num_rows($mysqli->query(
        "SELECT * FROM `product_view`
        WHERE
          `prod_id` = '{$row['prod_id']}' "
      ));

      $pre = mysqli_num_rows($mysqli->query(
        "SELECT sum(quan) as quan FROM `user_pre`
        WHERE
          `prod_id` = '{$row['prod_id']}' "
      ));
}

直接不刪減，給大神們看看我的困擾
while 裡面的四個查詢都需要 $product_data 的 $row['prod_id']
然後這這四個查詢都會有其他不同的作用
例如顯示符合條件的有幾筆、印出某個欄位的加總

<?=$pre;?>
<?=$buyTotal['quan'];?>

都是透過這樣的方式。
當然希望大神可以教教我，幫我簡化簡化，感激不盡。

Answer 1

不要在循环里面放Sql语句,可以把$row['prod_id']单独拿出来,sql语句可以写成wherein