vb.org Archive - Help needed on first try at Database Query!

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Help needed on first try at Database Query! (https://vborg.vbsupport.ru/showthread.php?t=72983)

EvilCrow

12-16-2004 09:50 PM

Help needed on first try at Database Query!

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

Code:

        $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

12-17-2004 12:34 AM

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

12-17-2004 08:56 AM

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

Code:

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	12-17-2004 09:45 AM

What are you using for the $searchclause var?

EvilCrow

12-17-2004 09:55 AM

Quote:

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

12-17-2004 01:59 PM

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

That should sort it out

EvilCrow

12-17-2004 03:03 PM

Quote:

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

12-19-2004 11:23 AM

Can someone help plz??? :confused:

Paul M

12-19-2004 01:15 PM

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

EvilCrow

12-20-2004 11:29 AM

1 Attachment(s)

Quote:

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)

Code:

$result_allweight = $DB_site->query_first(" 

   SELECT COUNT(field7) AS total 

   FROM " . TABLE_PREFIX . "userfield

"); 

/* 

$result_allweight['total'] - should return the total value. 

*/

EvilCrow

12-21-2004 12:20 AM

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

Link14716

12-21-2004 02:18 AM

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

Code:

   SELECT SUM(field7) AS total

   FROM " . TABLE_PREFIX . "userfield

EvilCrow

12-21-2004 11:33 AM

Quote:

Originally Posted by Link14716

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

Code:

   SELECT SUM(field7) AS total

   FROM " . TABLE_PREFIX . "userfield

You mean like this?

Code:

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

12-22-2004 11:25 AM

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

12-22-2004 11:46 AM

Quote:

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

Code:

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

12-22-2004 11:53 AM

In my FORUMHOME Template i have :

Code:

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

Code:

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

12-24-2004 01:46 PM

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

Davey

12-25-2004 11:17 AM

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:

Code:

^[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:

Code:

$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

12-25-2004 04:28 PM

1 Attachment(s)

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

12-28-2004 10:03 PM

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

Dean C

12-28-2004 10:24 PM

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

PHP Code:


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

EvilCrow

12-28-2004 11:04 PM

Quote:

Originally Posted by Dean C

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

PHP Code:


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

Little confused what you mean. :ermm:

Dean C

12-29-2004 08:48 AM

Change this:

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

To this:

PHP Code:


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

12-30-2004 05:14 AM

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

Dean C

12-30-2004 11:34 AM

You're welcome :)

Davey

01-17-2005 03:09 PM

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.

All times are GMT. The time now is 05:06 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01215 seconds Memory Usage 1,814KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (11)bbcode_code_printable (10)bbcode_php_printable (6)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (26)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: