Jump to content


Photo

**SOLVED** ranking table...


  • Please log in to reply
13 replies to this topic

#1 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 02 June 2006 - 08:21 PM

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

#2 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 02 June 2006 - 09:30 PM

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



#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 02 June 2006 - 09:34 PM

Something like this maybe

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');

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 02 June 2006 - 09:51 PM

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?

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 02 June 2006 - 10:00 PM

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."
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 02 June 2006 - 10:24 PM

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

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 02 June 2006 - 11:01 PM

So you think I set up a test database, wrote the code and tested it in 3 minutes?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 02 June 2006 - 11:09 PM

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

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 02 June 2006 - 11:51 PM

Try

$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>";

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 03 June 2006 - 08:10 AM

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

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 03 June 2006 - 08:50 AM

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 03 June 2006 - 09:01 AM

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

#13 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 03 June 2006 - 09:14 AM

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);

<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>";
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#14 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 03 June 2006 - 09:26 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users