how to sum all values in mysql using php

My table value is:

id stock
1   7|8
2   80|50|30

and my query is

I used PHP function

function numofQuantity() {

    $sql = "SELECT sum(stock) as quantity FROM products";
    $result = dbQuery($sql);
    $data=mysql_fetch_assoc($result);
    return $data['quantity'];

}

Here it shows the result is 87.

How to sum all the values? Thank you

Answers


Now it is difficult to do in Mysql. You can do it php like below:

function numofQuantity() {

    $sql = "SELECT stock FROM products";
    $result = dbQuery($sql);
    $data=mysql_fetch_assoc($result);
    $arrayVal = explode("|", $data['stock']);
    return array_sum($arrayVal)

}

For more info :http://us3.php.net/array_sum


Never store multiple values in one column!

As you see now that will only lead to problems. You should rather change your DB design. Use another table to store the stock:

stock table
-----------
id
product_id
stock

The you could sum the stock like this

SELECT p.id, sum(s.stock) as quantity 
FROM products p
inner join stock s on s.product_id = p.id
group by p.id

Need Your Help

How to create an Integer spinner with FXML

javafx spinner fxml

I am trying to create an Integer Spinner, but instead, a Double one is created instead.

How do you normally make a program look beautiful?

layout wiki

How can I make an Application look nice and not like an amateur pulled it together?