close ad
 
Important WebAssist Announcement
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

How to add up a column of numbers on a search query

Thread began 12/24/2009 3:01 pm by mel321998 | Last modified 1/03/2010 10:06 pm by Dani Chankhour | 3405 views | 11 replies |

mel321998

How to add up a column of numbers on a search query

Hi Everyone,

Happy Xmas & New Year

Using the search wizard I have set up a simple search to show records from a to and from date and this is working fine

The recordset includes two columns of figures the id's being ' fee ' & ' comm '

I want the search result page to show somewhere at the bottom of the page the totals for
' fee ' & ' comm '

Can this be done on a search results page and if so what code would I need to add. I have a very restricted knowledge of
php and mysql although I suspect that the the SUM statement comes into play some how and an echo statement for the result?

Thanks for your help
Mel

Sign in to reply to this post

Pro Covers FX

adding columns

Hi Mel,

With out seeing the code of the page it is a little hard, but I will give it a try. There should be a for loop that is reading the records from your database in this for loop you would want to use two php variables say $fee and $comm add the column value to these variables each time trhought the for loop then after the for loop you should be able to use php to echo them out on the screen.

Enjoy
Pro Covers FX

P.S. if this does not help post you code and I should beable to get it working for you.

Sign in to reply to this post

mel321998

Roy Neuf

Not sure what to do, here is page code have put the code in a zip file as submission error coming up

"The text that you have entered is too long (14939 characters). Please shorten it to 10000 characters long"

Here is zip

Attached Files
melsMonths.zip
Sign in to reply to this post

Dani Chankhour

To get the total for those two columns, update your query from:

SELECT recordNumber, date, fName, lName, telNo, apptTime, lenghtAppt, fee, comm, comments FROM thenumbers ORDER BY date DESC

to

SELECT recordNumber, date, fName, lName, telNo, apptTime, lenghtAppt, fee, comm, comments, SUM(fee) as Tfee, SUM(comm) as Tcomm, FROM thenumbers ORDER BY date DESC

This should add two columns to your record set. You could then drag the binding of those tow columns to any where on the page.

Sign in to reply to this post

mel321998

Hi Dani,

Thanks for your reply,

Now getting this error with and without your additional code for totalling columns

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM thenumbers WHERE (date >= "2009-12-01 00:00:00") AND (date <= "2009-12-28 2' at line 1

I am running this offline on WAMPServer with MYSQL Ver 5.1.32 for testing berfore going live

The odd things is that 3 days ago the search form was working but not adding up the totals now I am getting this error

But nothing has been changed or updated on my laptop or WampServer ?

Mel

Sign in to reply to this post

Dani Chankhour

Sorry, it looks like i have a syntax error in my sql statement, please change it to:

SELECT recordNumber, date, fName, lName, telNo, apptTime, lenghtAppt, fee, comm, comments, SUM(fee) as Tfee, SUM(comm) as Tcomm FROM thenumbers ORDER BY date DESC

You could see that i removed a comma before "FROM"

Sign in to reply to this post

mel321998

Hi Dani,

The records are now showing again but when I add the additional code you supplied, instead of displaying 7 records that fall into the date search range it is only displaying 1

I tried taking out the ORDER BY date DESC just as a test but it still is only returning one record

Is it because the the two new fields Tfee & Tcomm are not being echoed anywhere by me on the results page as yet?

If so should they take the form of something like this

<?php echo($row_WADAthenumbers['Tfee']); ?>
<?php echo($row_WADAthenumbers['Tcomm']); ?>


Thanks Mel

Sign in to reply to this post

Dani Chankhour

It looks like because it is a search record set, it is adding a Group By clause which could cause this issue. We can try this differently. Please change the query back to the way it was, and instead add this block of code:

<?php
$fee = 0;
$comm = 0;
$fee += $row_WADAthenumbers['fee'];
$comm += $row_WADAthenumbers['comm'];
?>

under this line:

<?php do { ?>

and then under this line:

<?php } while ($row_WADAthenumbers = mysql_fetch_assoc($WADAthenumbers)); ?>

add:

<?php
echo "".$fee."";
echo "".$comm."";
?>

Sign in to reply to this post

mel321998

Hi Dani,

It is now displaying the correct amount of records but not adding up the runing totals for the fee & comm it is just displaying the first fee and comm total

in the

<?php
echo "".$fee."";
echo "".$comm."";
?>

Fields

Mel

Sign in to reply to this post

Dani Chankhour

I have opened a support ticket to further assist you:

supporthistory.php

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...