Jump to content


Photo

Mathematics w/pagination problems


  • Please log in to reply
2 replies to this topic

#1 lansing

lansing
  • Members
  • PipPip
  • Member
  • 15 posts
  • LocationClinton, TN USA

Posted 07 September 2006 - 03:51 PM

I am using a very simple pagination script. I am trying to get use Mathematics with the records displayed on the page. Like if I limit it to 5 records per page & need to be able to use Mathematics on those 5 records.

Scenario:
Limit 5 records per page.
Each record has a membership fee
Need to add the total membership fees for those 5 records.


I am picturing having the table rows stop & then having 2 rows after the pagination rows. With the 5 table rows created on my page by the pagination script I am placing making the next row show totals for the selected records, but the last row will show totals for the entire database. I have that last row working, but can't seem to get the query code to total up just those 5 records that the pagination selected from the db.

I hope I haven't lost you.

Here is what I have now & it isn't working. The LIMIT $skip,$show is the same code that the pagination script uses in its processings.
$total_paid ="SELECT SUM(membership_fees) as membership_fees_paid
FROM $membership_fees_table
LIMIT $skip,$show";


#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 07 September 2006 - 04:28 PM

limit won't work here because you're only selecting ONE ROW of results (SUM()). your best bet is to possibly join the membership_fees_table to the initial query and use PHP to add them up as you loop through the records, for instance (and the names may be off since i don't know your schema):
<?php
$sql = mysql_query("SELECT * FROM users LEFT JOIN membership_fees WHERE users.user_id = membership_fees.user_id LIMIT $skip, $show");
$total = 0;
while ($x = mysql_fetch_array($sql)) {
  $fee = $x['membership_fee'];
  $total += $fee;
}
?>

hope that makes sense. good luck!
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 lansing

lansing
  • Members
  • PipPip
  • Member
  • 15 posts
  • LocationClinton, TN USA

Posted 08 September 2006 - 12:21 AM

<?php
$sql = mysql_query("SELECT * FROM users LEFT JOIN membership_fees WHERE users.user_id = membership_fees.user_id LIMIT $skip, $show");
$total = 0;
while ($x = mysql_fetch_array($sql)) {
  $fee = $x['membership_fee'];
  $total += $fee;
}
?>

What is this part about: LEFT JOIN membership_fees WHERE users.user_id = membership_fees.user_id? I didn't say anything about user or user_id. I tried to change the line to match my variables, but couldn't get anything to work.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users