**SOLVED** ranking table...

[brown2005]

Right, what I want to do is create three lists..... in ranking order... BASED ON THE NUMBER OF THEM, I.E. IF THERE IS 5 RICHARDS AND 3 EMILYS....

1. FIRST NAMES - BOTH MALES AND FEMALES

1 - RICHARD - 5
2 - EMILY - 3

2. FIRST NAMES - MALES ONLY

1 - RICHARD - 5

3. FIRST NAMES - FEMALES ONLY

1 - EMILY - 3

the table is members

and has the following fields

members_firstname
members_sex

[brown2005]

what i forgot to put is that i want them in the same table though.

[Barand]

Something like this maybe

[code]function rankNames ($sex='') {
    $sql = "SELECT firstname, COUNT(*) as total
            FROM members
            WHERE sex LIKE '$sex%'
            GROUP BY firstname
            ORDER BY total DESC";
    $res = mysql_query($sql) or die(mysql_error());

    switch ($sex) {
        case 'M':
            echo '<br>MALES ONLY<br>';
            break;
        case 'F':
            echo '<br>FEMALES ONLY<br>';
            break;
        default:            
            echo '<br>MALE and FEMALE<br>';
            break;
    }
    $rank=1;
    while (list($name, $tot) = mysql_fetch_row($res)) {
        echo "$rank $name $tot<br>";
        ++$rank;
    }
}

rankNames();
rankNames('M');
rankNames('F');[/code]

[brown2005]

Yeah thats cool i can use that....

now wat if i wanted to put it into a table...


RANK - ALL - MALES - FEMALE

like that? any ideas please?

[Barand]

I can't think of many things more annoying than people saying

"I know I said I wanted that but what I really want is this."

[brown2005]

well if you see i put "what i forgot to put is that i want them in the same table though" before wat u wrote

[Barand]

So you think I set up a test database, wrote the code and tested it in 3 minutes?

[brown2005]

no i dont..... ill make sure i put it all in the first bit next time.. sorry mate

[Barand]

Try

[code]$ranks = array();

$sql = "SELECT sex, firstname, COUNT(*) as total
        FROM members
        GROUP BY sex,firstname
        ORDER BY total DESC";
$res = mysql_query($sql) or die(mysql_error());

$rank = 0;
while (list($sex, $name, $tot) = mysql_fetch_row($res)) {
    $ranks['BOTH'][] = array($name,$tot);
    $ranks[$sex][] = array($name,$tot);
    $rank++;
}

echo "<table border='1'>";
echo "<tr>
    <td>RANK</td>
    <td>ALL</td>
    <td>MALE</td>
    <td>FEMALE</td>
    </tr>";

for ($r=1; $r<=$rank; $r++) {
     echo "<tr>
         <td>$r</td>
         <td>{$ranks['BOTH'][$r-1][0]} - {$ranks['BOTH'][$r-1][1]}</td>
         <td>{$ranks['M'][$r-1][0]} - {$ranks['M'][$r-1][1]}</td>
         <td>{$ranks['F'][$r-1][0]} - {$ranks['F'][$r-1][1]}</td>
         </tr>";
}
echo "</table>";[/code]

[brown2005]

Thanks Barand. Exactly what I wanted.. Im sorry for the messing around in the first place.

[Barand]

Just to make sure the data lists under the right heading, change first line to
[code]
$ranks = array(
     'BOTH' => array(),
     'M'    => array(),
     'F'     => array()
);[/code]

[brown2005]

Hi,

I don't know if its possib;e but with the colum under All, is there anyway to make the males one color and the females another color.

And is it possible to change the 1000 to 1,000

[Barand]

Define styles for M/F cells. Save sex in the array as well as name and count and use to define the TD.class.

For the count, store number_format($tot,0);

[code]<STYLE type="text/css">
    TD.M {color: #8CAAE1}
    TD.F {color: #FFC0C0}
</STYLE>

<?php
include 'db.php';

$ranks = array(
    'BOTH' => array(),
    'M'    => array(),
    'F'    => array()
);

$sql = "SELECT sex, firstname, COUNT(*) as total
        FROM members
        GROUP BY sex,firstname
        ORDER BY total DESC";
$res = mysql_query($sql) or die(mysql_error());

$rank = 0;
while (list($sex, $name, $tot) = mysql_fetch_row($res)) {
    $ranks['BOTH'][] = array($name, number_format($tot, 0), $sex);
    $ranks[$sex][] = array($name, number_format($tot, 0), $sex);
    $rank++;
}

echo "<table border='1'>";
echo "<tr>
    <td>RANK</td>
    <td>ALL</td>
    <td>MALE</td>
    <td>FEMALE</td>
    </tr>";

for ($r=1; $r<=$rank; $r++) {
     echo "<tr>
         <td>$r</td>
         <td class='{$ranks['BOTH'][$r-1][2]}'>{$ranks['BOTH'][$r-1][0]} - {$ranks['BOTH'][$r-1][1]}</td>
         <td class='{$ranks['M'][$r-1][2]}'>{$ranks['M'][$r-1][0]} - {$ranks['M'][$r-1][1]}</td>
         <td class='{$ranks['F'][$r-1][2]}'>{$ranks['F'][$r-1][0]} - {$ranks['F'][$r-1][1]}</td>
         </tr>";
}
echo "</table>";
?>[/code]

[brown2005]

Thanks mate... Thanks for all your help......