SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Jan 2001
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to create a range of age years, based on born years

    Hi everyone,

    I need a way to create a range of users age from a database. I need to display it in this format:

    Code:
    16    |    3% |    0-17 Years
    80    |    15% |    18-24 Years
    127    |    24% |    25-34 Years
    53    |    10% |    35-49 Years
    5    |    1% |    50+ Years
    The first column is the amount of people, the second column is the percent and the third is the age range.

    All I have is their born year in a column named 'year_born'. I have created this MySQL query to get the amount of users per born year:
    Code SQL:
    SELECT COUNT(id) AS total, year_born FROM jos_users WHERE ((year_born > DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -90 YEAR), '%Y')) AND (year_born < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -10 YEAR), '%Y'))) GROUP BY year_born ORDER BY total DESC;
    This returns me something like this:

    Code:
    total    year_born
    1368    1970
    1288    1975
    1263    1974
    1240    1973
    1219    1971
    1183    1976
    1182    1972
    1176    1977
    1165    1965
    1160    1968
    1115    1969
    1112    1980
    1091    1967
    1058    1966
    1051    1978
    1023    1979
    1022    1964
    994    1962
    947    1960
    933    1963
    862    1961
    835    1981
    806    1982
    Now that I have that data, how can I create the report that I actually need to display? Can it be done using sub-selects or maybe using some MySQL functions or any suggestion on the PHP code needed to create the report using the available data?

    Any help will be greatly appreciated...

    Thanks!
    Guillermo

  2. #2
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Pseudo code (as you haven't posted any of your PHP code), adjust it to fit your needs

    PHP Code:
    $sumOfTotal null;
    foreach (
    $result as $r) {
        
    $sumOfTotal += $r->total;
    }
    foreach (
    $result as $r) {
        echo 
    $r->total' | '$r->total $sumOfTotal 100'% | '$r->year;



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
  •