Forums

SQL SUM Query String

L
lavelle99 posted Sep 7, 2010 at 09:56
Good Morning...I'm a bit of a newbie to Chrono but from what I've discovered so far I think its an excellent product.

I am trying to obtain the sum a numerical column from the database and display this on the webpage in a nice neat table format. I would like to display the sum of more than one column on the same page also, somthing like the below. It would be much appreciated if someone can point me in the right direction of what query string to use and where this should be located.

Number of Lights(database column)
10 (sum of numerical values from database)

Number of Tables
25
G
GreyHead posted Sep 7, 2010 at 10:39
Hi lavelle99,

Sorry, I don't understand the question well enough to give you an answer. Please can you give a bit more info.

Bob
L
lavelle99 posted Sep 7, 2010 at 10:56
Apologies for the confusion..

The form requests that you enter the number of chairs required for that particular form. I wish then to count all the number of chairs entered in all records using chronoconnectivity?
G
GreyHead posted Sep 7, 2010 at 12:55
Hi Lavelle99,

OK - you can do this two ways.

One is to add some code snippets to add up the chairs for each entry in the listing. It needs three parts. In the Header box
<?php
global $chairs;
$chairs = 0;
?>
In the Body box
<?php
global $chairs;
$chairs += $MyRow>chairs;
?>
and in the Footer Box
<?php
global $chairs;
echo "<div>Total chairs: $chairs</div>"; 
?>

The other is to add a separate MySQL query in the Header or Footer section to get the total chairs.

Bob
L
lavelle99 posted Sep 8, 2010 at 08:30
Hi,

Thanks for that information, it's probably the second method that I'd like to use as I don't want to display the rows on the page, I'm after a summary style page.

Can you tell me how I can do a SQL query in the Header or Footer section?

Cheers
G
GreyHead posted Sep 8, 2010 at 10:01
Hi lavelle99,

If you don't' want the listing then you probably don't need ChronoConnectivity. Use a ChronoForm and put the MySQL query in the Form HTML instead.
<?php
if ( !$mainframe->isSite() ) { return; }
$db =& JFactory::getDBO();
$query = "
    SELECT SUM(`chairs`) as `chair_count`, SUM(`tables`) as `table_count`
        FROM `#__some_table`;
";
$db->setQuery($query);
$data = $db->loadObject();
echo "<div>Number of chairs: ".$data->chair_count."</div>";
echo "<div>Number of tables: ".$data->table_count."</div>";
?>

Bob
This topic is locked and no more replies can be posted.

 2Checkout.com

2CheckOut.com Inc. (Ohio, USA) is an authorized retailer for
goods and services provided by ChronoEngine.com