CODEWITHSUNDEEP
phpmysql
meandme
meandme

Reputation: 2477

Multiplication of data from MySQL row

I would like to be able to multiply all the prices for example from my database then multiply the total by 10.

The code below do the addition not multiplication

<?php
$d = $_GET['d'];
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("databasename", $con);//

$sql= "SELECT SUM(prices) FROM tablename WHERE Date = '$d' AND Prices >0.20 AND Type= 'sold'";
$results = mysql_query($sql);
$row = mysql_fetch_array($results);
echo $row[0]*10;


mysql_close($con);
?>

example

id prices
1   25
2   36
3   45

That is i want 25*36*45*10 and not 25+36+45*10

Hope anyone can help

Upvotes: 3

Views: 4251

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

This would work, with restrictions (numbers should be only positive):

SELECT 10 * EXP(SUM(LOG(prices))) AS result
FROM tablename 
WHERE ...

The thnx should go to Napier and his wonderful invention, logarithms

Upvotes: 6

Marc B
Marc B

Reputation: 360702

How does it not do the multiplication? You get 0? You get an error?

You could do it within the database as is, remember that

10 x (a + b + c + ...)

is exactly the same as

10a + 10b + 10c + ...

So you could simply do

SELECT 10*SUM(prices) from ...

or

SELECT SUM(10*prices) from ...

However, I notice that you have NO error handling whatsoever. If your query fails, mysql_query() will return a boolean false, from which you then try to fetch a row, which would also return a boolean false.

that boolean false would then be in $row, and you use it as an array, meaning that $row[0] will be a php NULL. NULL * 10 would be integer 0.

Change your query call to be the following:

$results = mysql_query($sql) or die(mysql_error());

and see what pops up.

Upvotes: -1

Related Questions