php sql help. adding rows together???

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jcrensha627
Forum Newbie
Posts: 15
Joined: Fri Feb 27, 2009 11:34 pm

php sql help. adding rows together???

Post by jcrensha627 »

Here is my situation:

Im trying to figure out how to add rows together to get "volume"

here an example of my table

id symbol shares volume price
13 JAME 100 100 50.00
14 JAME 50 "X" 50.00

what SQL query do i use to make the "x" in id 14 equal to the volume in 13 + shares in 14.

basically how do i have "X" replaced with 150
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: php sql help. adding rows together???

Post by josh »

You probably wouldn't since it violates normal form. You'd do the calculation with code, as a derived property on an object. I guess you could use stored procedures if you really wanted to store the redundant data
jcrensha627
Forum Newbie
Posts: 15
Joined: Fri Feb 27, 2009 11:34 pm

Re: php sql help. adding rows together???

Post by jcrensha627 »

josh wrote:You probably wouldn't since it violates normal form. You'd do the calculation with code, as a derived property on an object. I guess you could use stored procedures if you really wanted to store the redundant data
Thanks - but i do want the number to add up so i can have an idea of the volume of the shares. this needs to be updated so the price will fluctuate with it. Also im trying to figure how to make Y fluctuate based on "X"


id symbol shares volume price
13 JAME 100 100 50.00
14 JAME 50 "X" "Y"
15
16...
User avatar
php_east
Forum Contributor
Posts: 453
Joined: Sun Feb 22, 2009 1:31 pm
Location: Far Far East.

Re: php sql help. adding rows together???

Post by php_east »

you didn't get the point josh was trying to make.
you dont 'make' the values from mysql, you only fetch them.

you could if you wish make sql do the maths, but that isn't what is normally done,
and is what josh meant as "violation of normal form". it would be loading the sql unnecessarily, when such tasks are more suited to programs in PHP and such.

so the idea is to fetch the data, and do the maths in your programs. and then make them appear as if it was done from the db itself.

the fluctuations are reflected in your data if your data itself is updated, i.e. you need a
routine to continuously update your db. somehwere in there is a requirement for ajax.
it is not done from within mysql.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: php sql help. adding rows together???

Post by josh »

To elaborate on what was just said, the thing you're overlooking is now you need to do more work everytime you update the table, when your system grows its easy to forget these things. The better solution is to write a class to access that table from and do the math inside of a method. To the code using that class an array or object is returned with all 3 fields including your "magic" field which will always be equal to the sum of the other 2 fields. In the database you'd only store the bare minimum data, the 2 fields. Storing the 3rd field allows things to accidentally get out of sync.
jcrensha627
Forum Newbie
Posts: 15
Joined: Fri Feb 27, 2009 11:34 pm

Re: php sql help. adding rows together???

Post by jcrensha627 »

Hey guys thanks for the responses. Im pretty new to this but have taught myself a few things. Since im attempting to make a sort of stock market simulation I have done some math of my own to calculate some ones total cost when buying a certain stock at a certain price * by how many they want. below is my code, should i be using math to get an idea of how to insert volume in a table? If so, I have no idea where to start - im assuming it goes in php and then i can just insert it like ive inserted things below. what math calc will create column?

Code: Select all

 
$get_my_basket = mysql_query("SELECT `symbol`, `price` FROM `JAME_PH` WHERE `symbol`='".JAME."'");
 
  $total_cost = 0;
  $JAME = 0;
  $quantity = $_POST['quantity'];
  
 
while($my_basket = mysql_fetch_array($get_my_basket))
{
  if($my_basket['symbol']=='JAME') $JAME++;
 
  $total_cost += $my_basket['price'] * $quantity; 
}
 
echo 'You have selected: ';
if($JAME > 0) 
{
   echo $quantity.' JAME shares, ';
}
else
{
echo 'Your Basket it Empty';
}
$total_cost = number_format($total_cost,2);
 
echo ' and it will cost you $'.$total_cost; 
 
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: php sql help. adding rows together???

Post by josh »

There wouldn't be a column if you wanted to do it right
Post Reply