Help needed on first try at Database Query!

[EvilCrow]

I wasn't sure if this is the right section to post but I'll try here...

I'm trying to make a Database query, This will be my first one, So please be nice..

ok heres what I'm trying to do.. I have a site I'm doing for a support group. I have a userfield that members can choose a number, What I need is a database query that will add-up all the numbers and give a total...

Heres my failed stab at it..

$result_allweight = $DB_site->query("
SELECT * FROM " . TABLE_PREFIX . "userfield $searchclause ORDER BY field7
");

$weightcount = $DB_site->num_rows($result_allweight);

I need the query to go inside the "Userfield" and count the numbers inside "field7" and give me a total for all..
btw, This code works but its only giving me the total of members not whats inside the "Field7" Table!

[Davey]

If I understand what I think you're trying to accomplish...

Try this, see if it does what you need:

$result_allweight = $DB_site->query_first("
SELECT COUNT(field7) AS total,
FROM " . TABLE_PREFIX . "userfield
$searchclause
");
/*
$result_allweight['total'] - should return the total value.
*/

This is an untested piece of code, I have no idea if it even works. If it doesn't, please post back stating any errors you get, I will do my best to help if I can.

If anyone notices any obvious flaws in that above code, feel free to point them out, thanks.

[EvilCrow]

I'm getting this error after using your code. :ermm:

Invalid SQL:
SELECT COUNT(field7) AS total,
FROM vb3_userfield


mysql error: 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 vb3_userfield' at line 2

mysql error number: 1064

Let me explain better of what I'm trying to do here..
The site is for a weightloss supprot group and during registering they have a userfield option (field7) where they can put in how much weight they lost. What I'd like is a total of all the members weight loss added up and shown on the forum home page. The code I posted in the first post works somewhat as its returning the amount of tables (members) and its not adding whats in the "Field7" table.. :disappointed:

[Brad]

What are you using for the $searchclause var?

[EvilCrow]

What are you using for the $searchclause var?

erm... I guess nothing. :disappointed:
Like I said this is my FIRST try at php/mysql coding!

[dimopoulos]

Get rid of the comma after the COUNT(field7) AS total, <-

That should sort it out

[EvilCrow]

Get rid of the comma after the COUNT(field7) AS total, <-

That should sort it out

Did that and now I don't get any errors but insted of posting a total its only posting "Array"?? :disappointed:

[EvilCrow]

Can someone help plz??? :confused:

[Paul M]

Please post the code you are using to display the result.

[EvilCrow]

Please post the code you are using to display the result.


Heres the code I'm using.. its give me a "Array" as the result. (see attached image)

$result_allweight = $DB_site->query_first("
SELECT COUNT(field7) AS total
FROM " . TABLE_PREFIX . "userfield
");
/*
$result_allweight['total'] - should return the total value.
*/

[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]

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" :rolleyes:

[Davey]

Using that corrected code (in your last post), you must not output the variable $result_allweight, but you must output the variable
$result_allweight['total']
see if that works outputting that variable in the template instead.
You can also remove those comments;
/*
$result_allweight['total']
*/
They were just an instruction to tell you what I said above in this post.

[EvilCrow]

Using that corrected code (in your last post), you must not output the variable $result_allweight, but you must output the variable
$result_allweight['total']
see if that works outputting that variable in the template instead.
You can also remove those comments;
/*
$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.

// ### 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:

[Dean C]

You'll need to put something like this in your PHP file:


$result_allweight['weight'] = vb_number_format($result_allweight['weight']);

[EvilCrow]

You'll need to put something like this in your PHP file:


$result_allweight['weight'] = vb_number_format($result_allweight['weight']);


Little confused what you mean. :ermm:

[Dean C]

Change this:


// ### 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#############################################


To this:


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

[EvilCrow]

WOOHOO... Thank you very much!!!! :up: :classic: ;) :)

[Dean C]

You're welcome :)

[Davey]

Sorry I killed my hard drive, I haven't checked back here for a long time.
No problem, I got your pm, check the reply.
Happy to help.