CODEWITHSUNDEEP
phpmysqlsqlrows
mike621
mike621

Reputation: 93

Add rows together

I have one table with some rows and lot of columns (about 50) - I don't want to list all the column names, co I use a while loop to go through the table and a foreach command to find the column names and values. Now I would like to add these two rows into one row (where id is 1) and sum the values together...

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

It should look like this

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

This is what I have...

$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...?
        }
    }
}

Could you please help me? Thank you a lot...

Upvotes: 1

Views: 159

Answers (3)

X Pahadi
X Pahadi

Reputation: 7443

Try this: See the Explanation as comments:

<?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



?>

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25842

just sum the results in mysql.. databases are made to handle things like this so it'll be a faster solution than doing it just in php.

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

Upvotes: 1

Scott S
Scott S

Reputation: 2746

Based on your code which indicates you're using mysql I'm going to give you a mysql solution (which can easily be ported to most RDBMS). What you're looking for can easily be accomplished with aggregate functions. Follow the link to read about all the aggregate functions mysql has.

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

Upvotes: 1

Related Questions