[SOLVED] Calculating the sum of an HTML column

[Maracles]

Hi,

 

On my page I have a table, one column in which contains $ prices. What is the best method for calculating the sum of these prices and displaying the total in the bottom row? Is it quicker for it to be calculated at my database or on the clients computer?

 

I have thought of a couple of ideas:

 

1. Each price value is stored as variable so I could add all the variables i.e.

$totalprice = $price1 + $price2

 

...and so forth; this however seems long-winded.

 

2. All of the individual price variables are stored in an array

$price = array($day1, $day2... etc)

 

Is it possible to calculate the value of the array?

 

Or alternatively are there better ways of doing this, i.e. at the database?

 

[Maracles]

Just wanted to point out in my database has:

 

Rows = Film Title

Columns = Daily Gross i.e. column 1 = day1 gross, col 2 - day 2 gross and so on.

 

Is it possible to add up the rows at the database end?

[rajivgonsalves]

 

Is it possible to calculate the value of the array?

 

you can use array_sum

[Maracles]

Thank you for the reply.

 

I have tried using array sum but it doesn't actually calculate the sum, instead it simply print out 'Array'. Does this mean it is not calculating them properly? 

[rajivgonsalves]

if you can post some of your code it will be helpful its hard to know what is going wrong without the code bang on the screen

[Maracles]

This is the code that creates the array:

 

               $day1 = stripslashes($row['day1']);
		$day2 = stripslashes($row['day2']);
		$day3 = stripslashes($row['day3']);
		$day4 = stripslashes($row['day4']);
		$day5 = stripslashes($row['day5']);
		$day6 = stripslashes($row['day6']);
		$day7 = stripslashes($row['day7']);
		$day8 = stripslashes($row['day8']);
		$day9 = stripslashes($row['day9']);
		$day10 = stripslashes($row['day10']);
		$day11 = stripslashes($row['day11']);
		$day12 = stripslashes($row['day12']);
		$day13 = stripslashes($row['day13']);
		$day14 = stripslashes($row['day14']);
		$day15 = stripslashes($row['day15']);
		$day16 = stripslashes($row['day16']);
		$day17 = stripslashes($row['day17']);
		$day18 = stripslashes($row['day18']);
		$day19 = stripslashes($row['day19']);
		$day20 = stripslashes($row['day20']);
		$day21 = stripslashes($row['day21']);
		$day22 = stripslashes($row['day22']);
		$day23 = stripslashes($row['day23']);
		$day24 = stripslashes($row['day24']);
		$day25 = stripslashes($row['day25']);
		$day26 = stripslashes($row['day26']);
		$day27 = stripslashes($row['day27']);
		$day28 = stripslashes($row['day28']);

	$daygross =array(1=> $day1, $day2, $day3, $day4, $day5, $day6, $day7, $day8, $day9, $day10,$day11, $day12, $day13, $day14, $day15,$day16, $day17, $day18, $day19, $day20, $day21, $day22, $day23, $day24, $day25, $day26, $day27, $day28);

 

This is the code I'm using to try and echo the total:

 

 

 $grosstotal = array_sum($daygross);

	 echo '<tr class="tablefoot" >';
	 echo '<td></td>';
	 echo '<td></td>';
	 echo '<td><b>Total</b></td>';
	 echo '<td>$ '.$daygross.'</td>';
	 echo '<td></td>';
	 echo '<td></td>';
		 echo '<td></td>';
	 echo '</tr>';

 

 

[Mchl]

Where do you get those $day variables from?

[rajivgonsalves]

here is the problem why sum is not working

 

 

change this part

 

$daygross =array(1=> $day1,... 

 

to

 

$daygross =array($day1,... 

[Mchl]

here is the problem why sum is not working

...

 

I'll be surprised. array_sum doesn't care about keys.

 

[edit]

 

Answer's here

echo '<td>$ '.$daygross.'</td>';

 

should be

echo '<td>$ '.$grosstotal.'</td>';

[rajivgonsalves]

here is the problem why sum is not working

...

 

I'll be surprised. array_sum doesn't care about keys.

 

hehehe yeah I am wrong here kinda forgot that one

[Maracles]

Yeah, I just tried removing it anyway and it did not work.

 

Do either of you have any other ideas?

[Mchl]

See my edited post

[Orionsbelter]

Where do you get those $day variables from?

 

I think it might be just numbers in a table form the database, such as day1 earnings for his website.

[Mchl]

I think it might be just numbers in a table form the database, such as day1 earnings for his website.

 

Seems so. I smell bad design.

[Maracles]

Sorry... just saw your response above Mchl, make me look like a complete idiot!!!!!

 

Although I probably shouldn't deserve any help for not checking my code properly in the first place, can you advise whether this kind of thing should be done on the server or client side? Which is generally better.

 

Thanks a lot.

[Maracles]

As for your question about the numbers...

 

1. I have the amounts stored in my database

2. When the page is loaded I'm calling the amounts and loading them into variables - $day1, $day2 and so forth.

3. Then i'm loading them into an array and using a 'for' loop to echo my table.

 

I'm a complete beginner at the moment so I'm certain the design is bad, I would however take onboard any advice. At the moment I simply have an idea which I'm trying to make any way I can, not the best method I know!

[Mchl]

I might be wrong, but it seems your database is poorly designed. Had you all this data in rows and not in columns as it seems to be, you could just run query that would sum all these rows. Alternatively, you could calculate the sum, while fetching the data form the query that selects the rows.

 

Do you know database normalisation?

[Maracles]

I heard of database normalisation about 48 hours ago, one of my friends sent me this link. This is however the only thing I have read so far...

 

http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/

 

With regards to rows and columns where you talking about those in my DB or those on my webpage?

 

Currently my database has the following columns:

 

  grossID  FilmID  day1  day2  day3 ....

 

I then link the FilmID to the actual film whose gross it is the details of which are stored in another table. Is this wrong or would you need way more info to give advice on database structure

 

 

 

[Daniel0]

               $day1 = stripslashes($row['day1']);
		$day2 = stripslashes($row['day2']);
		$day3 = stripslashes($row['day3']);
		$day4 = stripslashes($row['day4']);
		$day5 = stripslashes($row['day5']);
		$day6 = stripslashes($row['day6']);
		$day7 = stripslashes($row['day7']);
		$day8 = stripslashes($row['day8']);
		$day9 = stripslashes($row['day9']);
		$day10 = stripslashes($row['day10']);
		$day11 = stripslashes($row['day11']);
		$day12 = stripslashes($row['day12']);
		$day13 = stripslashes($row['day13']);
		$day14 = stripslashes($row['day14']);
		$day15 = stripslashes($row['day15']);
		$day16 = stripslashes($row['day16']);
		$day17 = stripslashes($row['day17']);
		$day18 = stripslashes($row['day18']);
		$day19 = stripslashes($row['day19']);
		$day20 = stripslashes($row['day20']);
		$day21 = stripslashes($row['day21']);
		$day22 = stripslashes($row['day22']);
		$day23 = stripslashes($row['day23']);
		$day24 = stripslashes($row['day24']);
		$day25 = stripslashes($row['day25']);
		$day26 = stripslashes($row['day26']);
		$day27 = stripslashes($row['day27']);
		$day28 = stripslashes($row['day28']);

	$daygross =array(1=> $day1, $day2, $day3, $day4, $day5, $day6, $day7, $day8, $day9, $day10,$day11, $day12, $day13, $day14, $day15,$day16, $day17, $day18, $day19, $day20, $day21, $day22, $day23, $day24, $day25, $day26, $day27, $day28);

 

That entire monster block of code can be summarized into this:

 

$daygross = array_values(array_map('stripslashes', $row));

 

And if it's important to you that your array indices are 1-indexed (which is odd, by the way) you could do this:

$daygross = array_combine(range(1, count($row)), array_values(array_map('stripslashes', $row)));

 

You should turn off magic quotes though. Then you won't need to stripslash() everything.

[Mchl]

If he gets these from MySQL query, he doesn't need them anyway. And you're assuming there are only 'dayn' keys in the $row

 

Maracles: We've solved the problem you've had with summing. If you would like to get advice on how to improve toyr database design, it would be best if you started new topic in appropriate section of the forum (most likely MySQL ).

 

[Maracles]

OK, thanks very much for the help.

 

DanielO that code was a little bit of magic, my code looks so much better now!! Didn't quite understand what was going on though so just on PHP manual trying to figure out array_map.

[Daniel0]

array_map() just applies a function to every element in an array and returns the result.

[Daniel0]

If he gets these from MySQL query, he doesn't need them anyway. And you're assuming there are only 'dayn' keys in the $row

 

True, but I just wanted to point out that there were smarter ways of doing the same thing to all elements in an array than writing a million lines of repetitive code.

[Maracles]

Ah I see.. one last question.

 

I need to exclude the first value in the $row array from the array_values because it is the 'filmID'  and now a gross value. How could I do this?

 

I guess all these improvements to code are just learned through practice and experience? Thanks for the help much much appreciated.