DISTINCT

[mcmuney]

I'm using the codes below to display the ages of members on the site and show a count per age. It's working, except for the fact that the ages are repeating, and I tried placing DISTINCT on every place imaginable, but no luck, I'm stil seeing repeats. HELP. What do I need to do, I just need to see total count by age.

[code]
<?php
// grab the total count
$total_query = "SELECT COUNT(scm_dob) FROM sc_member";
$res = mysql_query($total_query) or die(mysql_error());
$total_count = mysql_result($res, 0, 0);

// start the table
echo '<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor=orange><tr bgcolor=orange><td><b><font face=Verdana size=1>Age</td><td width=50 align=right><b><font face=Verdana size=1>Count</td><td width=50 align=right><b><font face=Verdana size=1>P%</td></tr>';

// go through each age (lets hope you only have two)
$dob_query = "SELECT scm_dob, COUNT(scm_dob) AS dob_count FROM sc_member GROUP BY scm_dob ORDER BY dob_count DESC";
$res2 = mysql_query($dob_query) or die(mysql_error());
while ($info = mysql_fetch_assoc($res2))
{
  echo '<tr><td><font face=Verdana size=1>'.$db->ageFromDOB($info['scm_dob']).'</td><td align=right><font face=Verdana size=1>'.$info['dob_count'].'</td><td align=right><font face=Verdana size=1>'.round(($info['dob_count'] / $total_count)*100).' %</td></tr>';
}

// finish the table with the total
echo '<tr><td colspan=3 height=2></td></tr><tr><tr align=right bgcolor=orange><td align=left><b><font face=Verdana size=1>Total Members</td><td><b><font face=Verdana size=1>'.$total_count.'</td><td><b><font face=Verdana size=1>100 %</td></tr></table>';
?>
[/code]

The ageFromDOB function looks like this (this is the whole section, not sure exactly what it's calling):

[code]
function get_mem_age_old($sc_mem_id){
   
    $sql = "Select DISTINCT scm_dob  from sc_member where scm_mem_id='$sc_mem_id'";
$result = $this->select($sql);
$time = time();
//echo $result[0][0];
$age_stmp =  $time - $result[0][scm_dob];
$age = round($age_stmp/31536000);

return $age;
}

function get_age($dob){
   
    $time = time();
//echo $result[0][0];
$age_stmp =  $time - $dob;
$age = round($age_stmp/31536000);
return $age;
}

function ageFromDOB_old($dob)
{
$age=$this->select_data("select TRUNCATE((TO_DAYS(now()) - TO_DAYS('".$dob."'))/365,0)");
    return $age[0][0];

}//function

function ageFromDOB($dob)
{
$dob_arr=array();
$dob_arr=explode("-",$dob);

$curr_year=date(Y);
$curr_month=date(m);
$curr_day=date(d);

$dob_year=$dob_arr[0];
$mem_age=$curr_year-$dob_year;


if($curr_month<$dob_arr[1])
$mem_age=$mem_age-1;

return $mem_age;

}//function

function get_mem_age($sc_mem_id){
   
    $sql = "Select DISTINCT scm_dob  from sc_member where scm_mem_id='$sc_mem_id'";
$result = $this->select($sql);
$dob=$result[0][0];
$time1=time();
$age=($time1-$dob)/31536000;
$age=floor($age);
return $age;
}
[/code]

[mcmuney]

Can anybody help me with this?

[ryanlwh]

you see repeats because you group by dob. if you think about it, there could be multiple dob's evaluating to the same "age" (see example).. what you should do is calculate the age in the sql statement and group by the age, or go through the records first, counting each age in php, then output the result in another loop.

[code]
1981-10-01    2
1981-10-02    3
[/code]
these two dob's have the same age (24)

[mcmuney]

Good point, I didn't realize that it was looking at the date and not the calculated age. How can I easily group by the age? This is an existing code that I used and just slightly modified, so not sure how to proceed next. Thanks.

[ryanlwh]

Just use whatever method you like to calculate the age from mysql, create an alias for it, then group by it.
[code]SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age,
  COUNT(*) AS dob_count
FROM sc_member GROUP BY age ORDER BY dob_count DESC[/code]