Average age in statistics

[Lea Verou]

I would like to include an average age in statistics.
I want to make it as a mod and release it here (i love seeing people liking my "hacks" and installing them!!) but I have a very little php knowledge and none for SQL. I can learn easily though!
I just want somebody who knows to point me to the right direction and give me some guidelines, especially on the SQL part.
I read about a SQL function AVG (http://www.w3schools.com/sql/func_avg.asp), will it help?
Can I make this as a plugin?
I have never made a code mod before, only template mods, but I really like giving to the community. I just need some help with this as it looks quite difficult to me...
I took a look at my database and as I expected there is no "age" field, just birthday. This means my script should calculate the age itself or vb provides a way to do so?
Any help is really appreciated!! :)

Thanks in advance!

[-=Sniper=-]

this is from my site www.hardwareforums.com

import the attached plugin (enable it is well) and in your forumhome template search for

$vbphrase[welcome_to_our_newest_member_x]</phrase></div>

and add this after it

Average Age: $avg_age

I'm sure it can be done without any template edits, so if someone wants to, feel free to turn it into that...

[Lea Verou]

Thanks so much! Why don't you release this as a plugin? It's a nice idea I think and it would get many installs! :)

PS: I guess now I must find something else to make as my first php-hack :p Maybe a male to female ratio I guess :p

It produced a database error cause it tried to SELECT from a table that didn't exist. For some reason it treated the database name as the prefix as well.
I'll look into the code and try to see if I can fix it although I consider it quite unlikely :p

[Andreas]

I'd use another query:

SELECT ROUND(AVG(YEAR(NOW())-YEAR(birthday_search) - (MONTH(NOW()) < MONTH(birthday_search)) - (MONTH(NOW()) = MONTH(birthday_search) AND DAY(NOW()) < DAY(birthday_search)))) AS avgage FROM user WHERE YEAR(birthday_search)>0

as your Version only looks for the year, which produces incorrect results.

[-=Sniper=-]

thanks KirbyDE I'll update it now

edit:I get this msg

#1064 - 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 '( NOW( ) ) < DAY( birthday_search ) ) ) ) AS avgage FROM user W

[Lea Verou]

1. Both of your queries don't consider the prefix I think (correct me if I'm mistaken, I don't know SQL)

2. I would prefer the average to have 1-2 decimal digits, not as an integer like it does now

(I hope my english makes sense :p)

[Andreas]

@Sniper
This doesn't seem to be a complete mySQL erorr message

@Loukrhtia
I just posted the query, no PHP Code. And pure queries can't contain the TABLE_PREFIX constant :)
But you're right, in PHP one must add it.

If you want decimals, leave out the ROUND() and run the result through vb_number_format(), with decimals set to 2.

[Lea Verou]

I made the query like that so that it has the prefix:

SELECT ROUND(AVG(YEAR(NOW())-YEAR(birthday_search) - (MONTH(NOW()) < MONTH(birthday_search)) - (MONTH(NOW()) = MONTH(birthday_search) AND DAY(NOW()) < DAY(birthday_search)))) AS avgage FROM " . TABLE_PREFIX . " user WHERE YEAR(birthday_search)>0


but I get another database error now:

Invalid SQL:
SELECT ROUND(AVG(YEAR(NOW())-YEAR(birthday_search) - (MONTH(NOW()) < MONTH(birthday_search)) - (MONTH(NOW()) = MONTH(birthday_search) AND DAY(NOW()) < DAY(birthday_search)))) AS avgage FROM vb3_ user WHERE YEAR(birthday_search)>0;;

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 '(NOW()) < DAY(birthday_search)))) AS avgage FROM vb3_ user WHER
Error Number : 1064

at least now the prefix is ok :surprised:

[-=Sniper=-]

as strange as it might sound thats whole error message I get, I just tested it a couple of times.

[Lea Verou]

as strange as it might sound thats whole error message I get, I just tested it a couple of times.

Looks like we are getting the same error message Sniper...
KirbyDE, any ideas??