SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calculate avg age of multiple users?!?!

    I'm trying to figure ot the avareage age of all users in a certain group.

    This is how I calculate 1 user:
    PHP Code:
    $sql="SELECT * FROM ".$prefix."_users WHERE group = 1";
    $result mysql_query($sql);
    while(
    $row mysql_fetch_array($result)){

       
    $birthday $row['birthday']; // 1970-05-08 //
       
    list($year,$month,$day) = explode("-",$birthday);
       
    $year_diff  date("Y") - $year;
       
    $month_diff date("m") - $month;
       
    $day_diff   date("d") - $day;

       echo 
    'Age: '.$year_diff;


    As you can see I calculate the age outside the query... I think I have to doit within the query...

    Any help is apreciated...

    Thanks in advance ;-)

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Code:
    $sql = "SELECT 
    AVG( YEAR(now()) - YEAR(birthday)) as avg_age  
    FROM ".$prefix."_users 
    WHERE group = 1" ;
    *Only very roughly tested*

    Need to use ceil() to round up decimal points.

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have tryied this, but with no luck:
    PHP Code:
    $sql="SELECT AVG(YEAR(NOW()) - YEAR(u.birthday)) AS avg_age FROM ".$prefix."_club_users cu
        INNER JOIN "
    .$prefix."_users u ON cu.new_userid = u.new_userid
        WHERE clubid='
    $umode' AND cu.active=0";
    $result mysql_query($sql) or die("Error: (" mysql_errno() . ") " mysql_error());
    $row mysql_fetch_array($result); 
    Any ideas anybody?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i am not familiar with the "with no luck" mysql error message

    didn't it say anything a bit more substantial?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm sorry, i am not familiar with the "with no luck" mysql error message

    didn't it say anything a bit more substantial?
    Sorry, but it didnt give any errors... Just no output

    PHP Code:
    $sql="SELECT AVG(YEAR(NOW()) - YEAR(u.birthday_new)) AS avg_age FROM ".$prefix."_club_users cu
              INNER JOIN "
    .$prefix."_users u ON cu.new_userid = u.new_userid
              WHERE clubid='
    $umode' AND cu.active=0";
    $result mysql_query($sql) or die("Error: (" mysql_errno() . ") " mysql_error());
    $row mysql_fetch_array($result);

    echo 
    'Average age: '.$row['avg_age']; 
    This just gives
    Average age:

    Any ideas?

  6. #6
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jmansa View Post
    Sorry, but it didnt give any errors... Just no output

    PHP Code:
    .......
    echo 
    'Average age: '.$row['avg_age']; 
    This just gives
    Average age:

    Any ideas?
    Sorry, my bad. It gives an output:
    Average age: 534.5000

    But thats totally wrong! It should give 41,5?

    Any ideas?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you dump a few rows of data from each table please, so we can test the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you dump a few rows of data from each table please, so we can test the query
    Sorry, one record was wrong (000-00-00). Everything works fine with query below... But is there a way to not count the ones with 0000-00-00?

    PHP Code:
    $sql="SELECT AVG(DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(u.birthday_new, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(u.birthday_new, '00-%m-%d'))) AS avg_age, COUNT(*) as count FROM fb_club_users cu
    INNER JOIN "
    .$prefix."_users u ON cu.new_userid = u.new_userid
    WHERE cu.clubid='
    $groupID' AND cu.active=0"

  9. #9
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    change 0000-00-00 to NULL and they won't be counted.

  10. #10
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is a link to calculate age in the SQL itself in MySQL's documentation.

    Just tweak it for your use.
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •