Help needed on first try at Database Query!

[EvilCrow]

No hope in getting this to work eh??? :ermm:

[Link14716]

field7 contains numbers that you want to add up? Then here is the query that would work.

   SELECT SUM(field7) AS total
   FROM " . TABLE_PREFIX . "userfield

[EvilCrow]

Originally Posted by Link14716

field7 contains numbers that you want to add up? Then here is the query that would work.

   SELECT SUM(field7) AS total
   FROM " . TABLE_PREFIX . "userfield

You mean like this?

// Weight Count
$result_allweight = $DB_site->query_first(" 
   SELECT SUM(field7) AS total
   FROM " . TABLE_PREFIX . "userfield 
"); 
/* 
$result_allweight['total'] 
*/

Still getting the "Array"

[Davey]

Using that corrected code (in your last post), you must not output the variable $result_allweight, but you must output the variable

PHP Code:

$result_allweight['total'] 


see if that works outputting that variable in the template instead.
You can also remove those comments;

PHP Code:

/* 
$result_allweight['total']
*/ 


They were just an instruction to tell you what I said above in this post.

[EvilCrow]

Originally Posted by Davey

Using that corrected code (in your last post), you must not output the variable $result_allweight, but you must output the variable

PHP Code:

$result_allweight['total'] 


see if that works outputting that variable in the template instead.
You can also remove those comments;

PHP Code:

/* 
$result_allweight['total']
*/ 


They were just an instruction to tell you what I said above in this post.

When I try to add

$result_allweight['total']

to my template I get

The following error occurred when attempting to evaluate this template:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

[EvilCrow]

In my FORUMHOME Template i have :

$header
<center><b>Total Member Weight Loss is :</b> <b><font color="red">$result_allweight</font></b></center>
$navbar

In my Index.php of the forums I have :

// ### START TEST WEIGHT COUNT ##########################################
$result_allweight = $DB_site->query_first(" 
   SELECT SUM(field7) AS total
   FROM " . TABLE_PREFIX . "userfield 
");
// ### END TEST WEIGHT COUNT#############################################

Hope thats better so you guys understand whats I have going on.. and BTW I can't thank you guys enough for the help...

[EvilCrow]

I guess I should give up on trying to get this working? :disappointed:

[Davey]

OK well since nobody else seems to know I'll do what I can to help.
One thing I might suggest also, is that you edit the custom profile field 'weight' and add this regular expression in the regular expression field:

^[0-9]*$

This way, if anyone tries to be smart and input characters [a-z] instead of integers [0-9] into your field, the query wont go crazy trying to SUM() a non-integer, and it will give them a stern warning saying that the format they entered is not acceptable.
Still it is up to you.
Now,
Lets see if we can't get this query to run successful. I ran apache on my computer to test the query myself, so I should eventually come up with something which I am positive works...
I am testing it now while I am typing this message.

--- Success so far!

< I am now just doing a few quick last minute troubleshooting for any possible errors you may encounter, so I know in advance what to expect... >

OK no errors encountered on my part. If any of your users have entered non-integer values for weight, it will omit them from the SUM(). I assumed it would error.
Please tell me if this works for you.

PHP Code:

// ### START TEST WEIGHT COUNT ##########################################
$result_allweight = $DB_site->query_first("
    SELECT COUNT(*) AS users,
    SUM(field7) AS weight
    FROM " . TABLE_PREFIX . "userfield
");
// ### END TEST WEIGHT COUNT############################################# 


In your template, to call the total weight, you need to use this variable:

$result_allweight[weight]

It's advisable not to use apostrophes in the variable, eg try to avoid: $result_allweight['weight'] as this will cause a strange Parse Error message in your ACP which you will have to [Continue], although it wont really affect anything. It will just spew some strange eval()'d code error. o.o;

Well, I hope this helps you, if you have any more troubles, I will see what I can do.
Sorry for lengthy delay, it being Xmas and all.

PS: Merry Xmas all.

[EvilCrow]

Davey,

I can't thank you enough for not giving up on my problem and making it work.. I just used your code on the site and its working GREAT!

I have one last request if its not to hard to do..

The total weight loss is 2,221 Pounds (See attached image)
The total comes up as "2221" is it hard to have it put in the " , " for the 1,000 mark so that it will show "2,221"?
If its alot more work then I can deal with it staying the way it is but if its easy to place the " , " It would be great.. Thank for everyone's time that has worked on getting this to work for me.. I can't thank you all enough.. and To Davey please check your PM.. Thanks again!!

Crow

[EvilCrow]

I guess no one wants to give this one a try! :ermm: