PHP Maths on SQL Data

Question

Hello all, im not sure if this is php or mysql maths question.

Ive got a table full of data, and although i can echo the individual data to the screen or echo and individual piece of data using php. I've no idea how to perform maths on the data itself.

For simplicity, say my table has 4 columns, an id column and three with my data i want to multiply in them, data1, data2 and data 3,

and is layed out something like this

id  data1  data2   data3
1    2.5     2.6     2.7
2    2.6     7.0     8.2
3    3.0     1.8     6.0

what i want to be able to do maths like the following....data1(row1) x data(row2) x data3(row) or data1(column2) x data(column 3) x data3(column3).

Im able to use a while loop to loop thru the data, but i can only echo each row with the various field names and its values to the screen. ive no idea how to multiply fields in different rows together. Actually i would also like to be able to now only multiple the rows together, but also add them, divide them etc.

The final table of data im likely to need to do this on is likely to include many columns and have multiple rows. yet im basically stuck as to how to get all the data, one from each column and get it to do maths on it one at a time, being putting it in a loop? any ideas

cheers

Answer 1

So you're saying you have

id data1 data2 data3
1  2.5   2.6   2.7
2  2.6   7.0   8.2
3  3.0   1.8   6.0

and you want to compute the product of the main-diagonal entries (disregarding the id column)?

It's not clear from your question whether this is what you want to do, or whether you just want to compute data1 * data2 * data3 for each row.

If it's the latter: You should do this in MySQL. Add an extra column to the column list in your select statement:

SELECT
    id,
    ...,
    data1 * data2 * data3 AS product
FROM
    ...

If it's the former: You should do this in PHP rather than MySQL. You can do this by setting up a multidimensional array.

You probably have code that looks something like this:

while ( $row = mysqli_fetch_assoc($query_result) ) {
    echo $row['id'].' '.$row['data1'].' '.$row['data2'].' '.$row['data3'];
}

We will change it to the following:

$myarray = array();
while ( $row = mysqli_fetch_assoc($query_result) ) {
    $myarray[] = array($row['data1'], $row['data2'], ['data3']);
}

Now if you add the following:

echo '<pre>';
var_dump($myarray);
echo '</pre>';

you will see we have a two-dimensional array.

Now if we want to find the product of the diagonal entries in that array we can use this:

$product = $myarray[0][0] * $myarray[1][1] * $myarray[2][2];