building ranking system with data from the database
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 d开发者_如何学运维ata 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?.
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`' );
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);
}
精彩评论