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:

PHP Code:

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

Originally Posted by Brad.loo

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]

Originally Posted by dimopoulos

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???

[Paul M]

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

[EvilCrow]

Originally Posted by Paul M

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. 
*/