一起添加行

我有一个表有一些行和很多列(大约50) - 我不想列出所有的列名,我使用while循环来遍历表和foreach命令来查找列名和值 . 现在我想将这两行添加到一行(其中id为1)并将值加在一起......

id | col 1 | col 2 | ...
 1    30      21
 1    11      16
 2    75      0

它看起来应该是这样的

id | col1 | col2 | ...
 1    41     37
 2    75      0

这就是我的......

$query = mysql_query("SELECT * FROM `table` WHERE `id`='1'");
while ($row = mysql_fetch_assoc($query)) {
    foreach($row as $key => $val) {
        if($key != 'id') {
            //the sum code...?
        }
    }
}

请你帮助我好吗?非常感谢...

回答(3)

2 years ago

基于您的代码,表明您将为您提供一个mysql解决方案(可以轻松移植到大多数RDBMS) . 您正在寻找的东西可以通过aggregate functions轻松完成 . 点击链接阅读mysql的所有集合函数 .

SELECT id, SUM(col1), SUM(col2) FROM table GROUP BY id;

2 years ago

只是将结果汇总到mysql中 . 数据库可以处理这样的事情,所以它只是比在php中执行它更快的解决方案 .

SELECT 
    id, 
    SUM(col1), 
    SUM(col2) 
FROM table
GROUP BY id;

2 years ago

试试这个:参见解释为评论:

<?php
$query = mysql_query("SELECT * FROM `table` WHERE `id`='1'");
//Initially Set all the columns as zero.
$col1 = 0; $col2 = 0; 
//Etc.
while ($row = mysql_fetch_assoc($query)) {
    foreach($row as $key => $val) {
        if($key != 'id') {
            $$key += $val;
            //Like: $col1 += 30;
        }
    }
}

//Now All the variables are ready added:
//Like:  $col1 = 41;
//Like:  $col2 = 37;

//Use them However you like:
//To update:

//1. First Delete both rows:

$query = mysql_query("DELETE FROM `table` WHERE `id`='1'");

//2. Insert


$query = mysql_query("INSERT INTO `table` (`id`,`col1`,`col2`) VALUES ('1','{$col1}','{$col2}') ");
//And so on



?>