PHP 用 mysql 列的总和填充数组

Question

我是编程新手。我正在尝试从 MySQL 表创建一个数组，稍后我将使用它在 PHP/Javascript 中创建图形。

整个想法是，我想制作一个数组，其中填充代表一年中每个星期的数据。因此，一个包含 52 个条目的数组(让我们忘记有时会出现的第 53 周)。

我的数据库:

我的数据库中有 1 个表用于此:

+-------------------------+I       production        I+-------------------------+I  Shift     I  (number)  I <-ranging from 1 to 3 (different shifts people make)I  Line      I  (number)  I <-ranging from 1-8 (different 'conveyor belts')I  Products  I  (number)  I <-ranging from 0- a lot! (To be entered in a form)I  Week      I  (number)  I <-ranging from 1 to 52+-------------------------+

Now, the idea is, that I want an array, filled with the SUM of all the products.The SUM(Products) must exist of the following:

Sum of all products per shift + line.

Shift 1, Line 1, made 10,000 products
Shift 1, Line 2, made 15,000 products
Shift 1, Line 3, made 20,000 products (etc etc)

SUM(Products) will be all products from shift 1 to 3, and all lines 1 to 8.
So: 10,000 + 15,000 + 20,000 etc etc.

I want this total to be put inside an array, having 'week 1' as my array keys.

So for the first week you get:

$array (
    "1" => // SUM(products) of week 1 (which was the 10,000 + 15,000 + 20,000 etc)
    "2" => // SUM(products) of week 2
    "3" => // SUM(products) of week 3
// etc.

我可以通过添加 52 个不同的 MySQL 查询来做到这一点，唯一的区别是 WHERE week='x' 吗？

到目前为止，我一直在试验在 interwebz 上找到的代码。

任何帮助将不胜感激! :D

来自荷兰的问候

Answer 1

只需删除 WHERE 子句，您就会获得每周的结果。

$result = mysql_query("SELECT week, SUM(products) AS total 
  FROM production GROUP BY week");

结果将作为结果集中的连续行返回，您可以将它们放入一个数组中:

$sum_by_week = array();
while ($row = mysql_fetch_assoc($result)) {
  $sum_by_week[$row["week"]] = $row["total"];
}

PS:你没有要求这个，但你应该知道 mysql_* 函数正在被弃用。对于新的 PHP 应用程序，您应该开始使用 mysqli 函数或 PDO。仅当您只是维护现有应用程序时，才值得继续使用旧的 mysql_* 函数。