[SOLVED] What is method for math in php or mysql?

[sevun]

I currently have a database setup with a working $result=mysql_query($query).  The query returns sub-set of the database like:

 

col1,col2,col3

5,4,aa

8,5,bb

3,4,cc

8,4,dd

 

I would like add a col4 which is the sum of col1 and col2.  The final result should look like:

 

col1,col2,col3,col4

5,4,aa,9

8,5,bb,13

3,4,cc,7

8,4,dd,12

 

Is this best to to in mysql as part of a query or php after the query is returned?  What is the best method for this to be done?  Thanks (this is my first post anywhere so noob questions may apply).

[Stooney]

This would be one way, I'm not sure if you can add the 2 colums via the query.  This is probably what I would do though.

edit: that was wrong, lemme remake

[cooldude832]

there is string based connotation and integer based math in mysql queries however as this is a mysql issue look it up in a mysql forum or in the mysql website.

 

The type of field must match for addition I believe

[Stooney]

May not be the best solution, but it's just a simple multi-dimensional array.  Didn't test, but it should work.

 

<?php
$result=mysql_query($query);

$count=0;
while($row=mysql_fetch_array($result)){
    $data[$count][0]=$row[0];
    $data[$count][1]=$row[1];
    $data[$count][2]=$row[2];
    $data[$count][3]=$row[0]+$row[1];
    $count++;
}
?>

[revraz]

You should be able to do a SUM in mysql and use a AS clause and give it it's own display name, like col4.

[sevun]

So to clarify, your just create a new variable '$data' which stores the original '$result' but adds a new line in the array.  My actual code is:

 

$query = "SELECT `eventRecord`.* FROM eventRecord 
    WHERE ((`eventRecord`.`eventDate` >= DATE('".$alpha."'))
    AND (`eventRecord`.`eventDate` <= DATE('".$beta."'))
    AND (`eventRecord`.`eventLat` <= ".$north.")
    AND (`eventRecord`.`eventLat` >= ".$south.")
    AND (`eventRecord`.`eventLng` <= ".$east.")
    AND (`eventRecord`.`eventLng` >= ".$west."));

 

The resultant query returns:

 

[pre]eventID eventLat eventLng eventDate

15069 2.942702 -1.951465 10/19/1982 7:55

21098 8.538873 2.011558 7/23/1982 4:47

22724 6.359921 3.520448 1/7/1982 1:10

24787 -6.956536 7.482803 1/6/1982 8:08

30543 6.680874 0.598643 3/26/1982 8:52

34364 -8.376106 1.125904 4/20/1982 6:43

36698 9.858398 2.425011 5/8/1982 3:49

36713 -0.334226 -0.766929 6/15/1982 13:54

39288 3.081715 7.089587 4/11/1982 12:06

41366 9.691384 -3.348416 9/2/1982 8:20

48670 -7.189769 9.11866 4/23/1982 21:55

49612 -3.387992 -6.690833 6/25/1982 10:47

62885 -1.199778 9.939783 7/13/1982 8:31[/pre]

 

I'd then like to take the 2nd and 3rd columns which are latitude and longitude, do a functional call for a mathematical operation, then add the result as a another row.

 

$count=0;
while($row=mysql_fetch_array($result)){
    $data[$count][0]=$row[0];
    $data[$count][1]=$row[1];
    $data[$count][2]=$row[2];
    $data[$count][3]=mathoperation($row[1],$row[2]);
    $count++;
}

 

Then I could use the $data now in place of $result?

 

 

May not be the best solution, but it's just a simple multi-dimensional array.  Didn't test, but it should work.

 

<?php
$result=mysql_query($query);

$count=0;
while($row=mysql_fetch_array($result)){
    $data[$count][0]=$row[0];
    $data[$count][1]=$row[1];
    $data[$count][2]=$row[2];
    $data[$count][3]=$row[0]+$row[1];
    $count++;
}
?>

[sevun]

I thought SUM was for adding columns.  I need to a mathematical operation using values from different columns on the same row, then join the result with the same row.

 

You should be able to do a SUM in mysql and use a AS clause and give it it's own display name, like col4.

[revraz]

That is what you asked for

 

I would like add a col4 which is the sum of col1 and col2.

 

I thought SUM was for adding columns.  I need to a mathematical operation using values from different columns on the same row, then join the result with the same row.

 

You should be able to do a SUM in mysql and use a AS clause and give it it's own display name, like col4.

[roopurt18]

SELECT 
  `table`.*, 
  `table`.`col1` + `table`.`col2` AS `the_sum` 
FROM `table` 
WHERE ...

 

Try and apply that to your situation.

[sevun]

Worked liked a champ!

Thanks

 

SELECT 
  `table`.*, 
  `table`.`col1` + `table`.`col2` AS `the_sum` 
FROM `table` 
WHERE ...

 

Try and apply that to your situation.