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
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 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 :https://www.php.net/array_sum
First of all, you should normalize your database if you wish to do anything with the individual values inside a column.
Without normalization you will need the support of PHP (PDO example):
$sum = 0; $res = $db->query('SELECT stock FROM products'); foreach ($res->fetchAll(PDO::FETCH_COLUMN) as $stocks) { $sum += array_sum(explode('|', $stocks)); } echo $sum;