时间戳

首页 > 解决方案 > PHP从explode/foreach循环中获取id并计算价格

php - PHP从explode/foreach循环中获取id并计算价格

问题描述

我正在尝试将 MySQL 表中的所有 product_price 求和product_id。

当我运行时$result = "SELECT SUM(product_price) AS product_price FROM products";，它总结了所有正常工作的 product_price。

当我运行时$result = "SELECT SUM(product_price) AS product_price FROM products WHERE product_id = $school_product_id";，它给了我一个数组错误。

我希望查询从explode/获取所有 4 个 IDforeach并显示总价。

正在展开的数据库字段看起来像“1,2,3,4”，product_id在下products表中匹配。

    $row = $result->fetch_assoc();
    $school_subject_products = $row["school_subject_products"];  // (output is 1,2,3,4)
    $school_product_id = explode(',', $school_subject_products); // (explode 1,2,3,4 to use in query below to calculate all 4 product_id's product_price)


    $result = "SELECT SUM(product_price) AS product_price FROM products WHERE product_id = ??????????"; //<------ how do I get it to search for all 4 product_ids and total the price of all the product_ids
    $price_result = $mysqli->query($result);
    $row = $price_result->fetch_assoc();
    $sum = $row['product_price'];
    echo $sum;

    ?>

标签： phpmysqli

解决方案

理想的解决方案是规范化您的数据，这样您就没有逗号分隔的列表。

如果你不能这样做，你可以使用JOINwithFIND_IN_SET()来匹配一个逗号分隔的列表，而不是执行多个查询并使用explode().

SELECT SUM(product_price) AS product_price
FROM products AS p
JOIN select_subject AS s ON FIND_IN_SET(p.product_id, s.school_subject_products)
WHERE school_id = $selected_school_id AND school_year_id = $selected_year_id AND  school_subject_id = $school_subject_selected

推荐阅读