i have mysql table below:
create table if not exists `cxexam` ( `id` int(11) unsigned not null auto_increment, `regd` int(11) not null, `name_of_student` varchar(100) collate latin1_general_ci not null, `class` varchar(50) collate latin1_general_ci not null, `roll_no` int(11) not null, `section` varchar(50) collate latin1_general_ci not null, `name_of_exam` varchar(100) collate latin1_general_ci not null, `test_date` date not null, `subject` varchar(50) collate latin1_general_ci not null, `full_mark` int(11) not null, `mark_score` int(11) not null, `year` year(4) not null, primary key (`id`) ) engine=myisam default charset=latin1 collate=latin1_general_ci auto_increment=7 ; --
-- dumping data table cxexam
insert `cxexam` (`id`, `regd`, `name_of_student`, `class`, `roll_no`, `section`, `name_of_exam`, `test_date`, `subject`, `full_mark`, `mark_score`, `year`) values (6, 20, 'ramdina', 'x', 9, 'a', 'second term unit test', '2013-07-19', 'english', 20, 18, 2013), (3, 2, 'zonundanga', 'x', 5, 'a', 'second term unit test', '2013-07-19', 'english', 20, 12, 2013), (4, 40, 'lalnunkimi', 'x', 10, 'a', 'second term unit test', '2013-07-19', 'english', 20, 18, 2013); the mysql query produce:
regd totalscore rank 20 18 1 2 18 1 40 12 2 and want output rank of regd='2' using php. using following php code , mysql code need refresh page code working. query working fine in phpmyadmin, of times got 1 rank when change regd.
mysql_select_db($database_dbconnect, $dbconnect); $query_myrank = "select distinct regd, test_date, year, name_of_student, totalscore, rank (select *, if(@marks = (@marks := totalscore), @auto, @auto := @auto + 1) rank (select name_of_student, regd, test_date, year, sum(mark_score) totalscore cxexam, (select @auto := 0, @marks := 0) init group regd, year order totalscore desc) t) result having regd='2' , year='2013' , test_date between '2013-07-01' , '2013-07-30'"; $myrank = mysql_query($query_myrank, $dbconnect) or die(mysql_error()); $row_myrank = mysql_fetch_assoc($myrank); $rank= $row_myrank['rank']; i echo using <?php echo $row_myrank['rank'];?> happy if can me alternative solution using php because trying apply ranking in school marksheet management system.
this new code:
<?php mysql_select_db($database_dbconnect, $dbconnect); $query_myrank = "select distinct regd, test_date, year, name_of_student, totalscore, rank (select *, if(@marks = (@marks := totalscore), @auto, @auto := @auto + 1) rank (select name_of_student, regd, test_date, year, sum(mark_score) totalscore cxexam, (select @auto := 0, @marks := 0) init group regd, year order totalscore desc) t) result having year='$yr' , test_date between '$fdate' , '$tdate'"; $myrank = mysql_query($query_myrank, $dbconnect) or die(mysql_error()); $i = 0; $j = 1; $data = array(); while($row_myrank = mysql_fetch_assoc($myrank)) { $data[$i] = $row_myrank; if(isset($data[$i - 1]) && $data[$i - 1]['totalscore'] == $data[$i]['totalscore']) { $data[$i]['rank'] = $j; }else{ $data[$i]['rank'] = ++$j; } $i++; } foreach($data $key => $value) { if($value['regd'] == $regd) { echo $value['rank']; } } ?>
when loop add automatic increment this.
$i = 0; $j = 0; $data = array(); while($row_myrank = mysql_fetch_assoc($myrank)) { $data[$i] = $row_myrank; if(isset($data[$i - 1]) && $data[$i - 1]['mark_score'] == $data[$i]['mark_score']) { $data[$i]['rank'] = $j; }else{ $data[$i]['rank'] = ++$j; } $i++; } it bad answer if don't "don't use mysql_* function pdo". @ link please. http://www.php.net/manual/fr/class.pdo.php
edit: how output data
foreach($data $key => $value) { if($value['regd'] == 2) { echo $value['field']; } } just change field ones in query.
Comments
Post a Comment