Jump to content

DISTINCT


mcmuney

Recommended Posts

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]

Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.