building ranking system with data from the database

Question

I am building a ranking system that takes data(totals) from the database and ranks them from the highest to the lowest.Here is the code:

$data = array(
  'A'=>19,'B'=>18,'C'=>17,'D'=>17,'E'=>16,'F'=>15
);
//Populate the arrays with data from mysql
/*
--
-- Table structure for table `data`
--

CREATE TABLE IF NOT EXISTS `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `totals` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=501 ;

--
-- Dumping data for table `data`
--

INSERT INTO `data` (`id`, `totals`) VALUES
(1, 468),
(2, 450),
(3, 400),
(4, 419),
(5, 400),
(6, 400),
(7, 500),
(8, 489),
(9, 412),
(10, 385);

*/
$rank = 0;
$lastScore = PHP_INT_MAX;
foreach( $data as $name=>$score ) {
  if ( $lastScore !== $score ) {
    $lastScore = $score;
    $rank += 1;
  }
  printf("%s %d (%d)\n", $name, $score, $rank);
}

I want a way to populate the variable($data) with the values stored in the database,how can i do that?.

Answer 1

Undgerman -- you will have to connect to a MySQL database:

http://us2.php.net/manual/en/function.mysql-connect.php

Then select your database:

http://us2.php.net/manual/en/function.mysql-select-db.php

Then do a MySQL Query:

http://us2.php.net/manual/en/function.mysql-query.php

Then fetch the result:

http://us2.php.net/manual/en/function.mysql-fetch-assoc.php

---

EXAMPLE

<?php
mysql_connect( 'host', 'username', 'password' ); 
mysql_select_db( 'database' );

$result = mysql_query( 'SELECT * FROM `data`' );
$data = mysql_fetch_assoc( $result );
?>

Realize you can sort this data in the query itself:

$result = mysql_query( 'SELECT * FROM `data` ORDER BY `totals`' );

Answer 2

I have now finished building the simple ranking system and i thought i should share my code.Grab the db package from pear,the project needs it.

<?php
require_once 'DB.php';

$dsn = array(
    'phptype'  => 'mysql',
    'username' => 'root',
    'password' => '',
    'hostspec' => 'localhost',
    'database' => 'rm-db',
);

$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$data =& $db->getCol('SELECT  totals FROM data order by totals desc');

if (PEAR::isError($data)) {
    die($data->getMessage());
}

//print_r($data);

//Populate the arrays with data from mysql and arrange in descending order.
//SELECT * FROM data order by totals desc
/*
--
-- Table structure for table `data`
--

CREATE TABLE IF NOT EXISTS `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `totals` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=501 ;

--
-- Dumping data for table `data`
--

INSERT INTO `data` (`id`, `totals`) VALUES
(1, 468),
(2, 450),
(3, 400),
(4, 419),
(5, 400),
(6, 400),
(7, 500),
(8, 489),
(9, 412),
(10, 385);

*/
$rank = 0;
$lastScore = PHP_INT_MAX;
foreach( $data as $name=>$score ) {
  if ( $lastScore !== $score ) {
    $lastScore = $score;
    $rank += 1;
  }
  printf("%s %d (%d)\n", $name, $score, $rank);
}