lansing Posted September 7, 2006 Share Posted September 7, 2006 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.[u][b]Scenario:[/b][/u]Limit 5 records per page.Each record has a membership feeNeed 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.[code=php:0] $total_paid ="SELECT SUM(membership_fees) as membership_fees_paidFROM $membership_fees_tableLIMIT $skip,$show";[/code] Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 7, 2006 Share Posted September 7, 2006 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):[code]<?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;}?>[/code]hope that makes sense. good luck! Quote Link to comment Share on other sites More sharing options...
lansing Posted September 8, 2006 Author Share Posted September 8, 2006 [quote author=obsidian link=topic=107240.msg429978#msg429978 date=1157646497][code]<?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;}?>[/code][/quote]What is this part about: [i][b]LEFT JOIN membership_fees WHERE users.user_id = membership_fees.user_id[/b][/i]? 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.