Любое альтернативное решение для получения этой работы? Php-код работает неправильно, но работает код mysql

Я долгое время боролся со следующими кодами. Я новичок в PHP. Этот код mysql работает на phpmyadmin на 100%. Я использую <?php echo $row['Rank'];?> Но я получаю только 1 для всех учеников. Но SQL-код работает нормально. Я сделал скрипку sql здесь http://sqlfiddle.com/#!2/24855/1 Пожалуйста, помогите мне.

 Select Distinct regd, Roll_no, Name_of_Student, Test_date, English, f_eng, Mizo, f_mz, Hindi, f_hn, Mathematics, f_maths, SS, f_ss, Science, f_sc, score, fmscore, perc, Rank FROM (SELECT *, IF(@marks = (@marks := score), @auto, @auto := @auto + 1) AS Rank FROM (SELECT regd, Roll_no, Name_of_Student, Test_date, SUM( IF( `Subject` = 'English', Mark_score, 0 ) ) AS English, SUM( IF( `Subject` = 'English', Full_mark, 0 ) ) AS f_eng, SUM( IF( `Subject` = 'Mizo', Mark_score, 0 ) ) AS Mizo, SUM( IF( `Subject` = 'Mizo', Full_mark, 0 ) ) AS f_mz, SUM( IF( `Subject` = 'Hindi', Mark_score, 0 ) ) AS Hindi, SUM( IF( `Subject` = 'Hindi', Full_mark, 0 ) ) AS f_hn, SUM( IF( `Subject` = 'Mathematics', Mark_score, 0 ) ) AS Mathematics, SUM( IF( `Subject` = 'Mathematics', Full_mark, 0 ) ) AS f_maths, SUM( IF( `Subject` = 'SS', Mark_score, 0 ) ) AS SS, SUM( IF( `Subject` = 'SS', Full_mark, 0 ) ) AS f_ss, SUM( IF( `Subject` = 'Science', Mark_score, 0 ) ) AS Science, SUM( IF( `Subject` = 'Science', Full_mark, 0 ) ) AS f_sc, SUM(Full_mark) AS fmscore, SUM(Mark_score) AS score, SUM(Mark_score)/SUM(Full_mark)*100 as perc FROM cxexam, (SELECT @auto := 0, @marks := 0) AS init GROUP BY regd ORDER BY score DESC) t) AS result where Test_date between '2013-07-01' and '2013-07-31'

Вот часть PHP. Кажется, все в порядке. Он также эхо все правильно, за исключением того, что он не выводит Rank правильно. В ранговой строке он отображает только 1 как ранг для всех учеников. Вы можете понять из приведенного ниже кода, чего я пытаюсь достичь. Мне действительно нужна твоя помощь. Я знаю, что вы, ребята, хорошо знакомы с такой проблемой.

 <table width="800" border="1" class="tablestyle" cellpadding="8" cellspacing="6"> <tr> <th align="center" width="80">Roll No</th> <th align="center" width="100">Name_of_Student</th> <th align="center" width="40">English</th> <th align="center" width="55">Mizo</th> <th align="center" width="55">Hindi</th> <th align="center" width="55">Math</th> <th align="center" width="70">SS</th> <th align="center" width="40">Science</th> <th align="center" width="70">FM</th> <th align="center" width="70">MO</th> <th align="center" width="40">Percentage</th> <th align="center" width="40">Rank</th> <th align="center" width="40">Result</th> </tr> <?php while($row=mysql_fetch_array($res)) { ?> <tr> <td align="center"><?php echo $row['Roll_no'];?></td> <td align="left"><?php echo $row['Name_of_Student'];?></td> <td align="center"><?php echo $row['English'];?></td> <td align="center"><?php echo $row['Mizo'];?></td> <td align="center"><?php echo $row['Hindi'];?></td> <td align="center"><?php echo $row['Mathematics'];?></td> <td align="center"><?php echo $row['SS'];?></td> <td align="center"><?php echo $row['Science'];?></td> <td align="center"><?php echo $row['fmscore'];?></td> <td align="center"><?php echo $row['score'];?></td> <td align="center"><?php echo number_format($row['perc'],0);?>%</td> <td><?php echo $row['Rank']; ?></td> <td><?php if ($row['English']/$row['f_eng']*100>=40 && $row['Mizo']/$row['f_mz']*100>=40 && $row['Hindi']/$row['f_hn']*100>=40 && $row['Mathematics']/$row['f_math']*100>=40 && $row['SS']/$row['f_ss']*100>=40 && $row['Science']/$row['f_sc']*100>=40) { echo "<font color=green>Passed</font>"; } else { echo "<font color=red>Failed</font>"; } ?></td> </tr> <?php } ?> </table> в <table width="800" border="1" class="tablestyle" cellpadding="8" cellspacing="6"> <tr> <th align="center" width="80">Roll No</th> <th align="center" width="100">Name_of_Student</th> <th align="center" width="40">English</th> <th align="center" width="55">Mizo</th> <th align="center" width="55">Hindi</th> <th align="center" width="55">Math</th> <th align="center" width="70">SS</th> <th align="center" width="40">Science</th> <th align="center" width="70">FM</th> <th align="center" width="70">MO</th> <th align="center" width="40">Percentage</th> <th align="center" width="40">Rank</th> <th align="center" width="40">Result</th> </tr> <?php while($row=mysql_fetch_array($res)) { ?> <tr> <td align="center"><?php echo $row['Roll_no'];?></td> <td align="left"><?php echo $row['Name_of_Student'];?></td> <td align="center"><?php echo $row['English'];?></td> <td align="center"><?php echo $row['Mizo'];?></td> <td align="center"><?php echo $row['Hindi'];?></td> <td align="center"><?php echo $row['Mathematics'];?></td> <td align="center"><?php echo $row['SS'];?></td> <td align="center"><?php echo $row['Science'];?></td> <td align="center"><?php echo $row['fmscore'];?></td> <td align="center"><?php echo $row['score'];?></td> <td align="center"><?php echo number_format($row['perc'],0);?>%</td> <td><?php echo $row['Rank']; ?></td> <td><?php if ($row['English']/$row['f_eng']*100>=40 && $row['Mizo']/$row['f_mz']*100>=40 && $row['Hindi']/$row['f_hn']*100>=40 && $row['Mathematics']/$row['f_math']*100>=40 && $row['SS']/$row['f_ss']*100>=40 && $row['Science']/$row['f_sc']*100>=40) { echo "<font color=green>Passed</font>"; } else { echo "<font color=red>Failed</font>"; } ?></td> </tr> <?php } ?> </table>

Детали, которые не работают: rank и result . Любая помощь приветствуется.

Related of "Любое альтернативное решение для получения этой работы? Php-код работает неправильно, но работает код mysql"

Есть несколько проблем с вашим кодом sql (например, никогда не смешивайте DISTINCT и GROUP BY , используйте WHERE во внутреннем выборе и т. Д.). При этом ваш запрос должен выглядеть примерно так:

 SELECT regd, Roll_no, Name_of_Student, Test_date, English, f_eng, Mizo, f_mz, Hindi, f_hn, Mathematics, f_maths, SS, f_ss, Science, f_sc, score, fmscore, perc, Rank FROM ( SELECT t.*, IF(@p = score, @n, @n := @n + 1) AS Rank, @p := score FROM ( SELECT regd, Roll_no, Name_of_Student, Test_date, SUM(IF(Subject = 'English' , Mark_score, 0)) English, SUM(IF(Subject = 'English' , Full_mark, 0)) f_eng, SUM(IF(Subject = 'Mizo' , Mark_score, 0)) Mizo, SUM(IF(Subject = 'Mizo' , Full_mark, 0)) f_mz, SUM(IF(Subject = 'Hindi' , Mark_score, 0)) Hindi, SUM(IF(Subject = 'Hindi' , Full_mark, 0)) f_hn, SUM(IF(Subject = 'Mathematics', Mark_score, 0)) Mathematics, SUM(IF(Subject = 'Mathematics', Full_mark, 0)) f_maths, SUM(IF(Subject = 'SS' , Mark_score, 0)) SS, SUM(IF(Subject = 'SS' , Full_mark, 0)) f_ss, SUM(IF(Subject = 'Science' , Mark_score, 0)) Science, SUM(IF(Subject = 'Science' , Full_mark, 0)) f_sc, SUM(Full_mark) fmscore, SUM(Mark_score) score, SUM(Mark_score) / SUM(Full_mark) * 100 perc FROM cxexam, (SELECT @n := 0, @p := 0) n WHERE Test_date BETWEEN '2013-07-01' AND '2013-07-31' GROUP BY regd ORDER BY score DESC ) t ) r

Вот демоверсия SQLFiddle

Теперь PHP-код

 $link = mysql_connect('localhost', 'user', 'password'); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db('dbname', $link); if (!$db_selected) { die ('Can\'t use db : ' . mysql_error()); } $sql = " SELECT regd, Roll_no, Name_of_Student, Test_date, English, f_eng, Mizo, f_mz, Hindi, f_hn, Mathematics, f_maths, SS, f_ss, Science, f_sc, score, fmscore, perc, Rank FROM ( SELECT t.*, IF(@p = score, @n, @n := @n + 1) AS Rank, @p := score FROM ( SELECT regd, Roll_no, Name_of_Student, Test_date, SUM(IF(Subject = 'English' , Mark_score, 0)) English, SUM(IF(Subject = 'English' , Full_mark, 0)) f_eng, SUM(IF(Subject = 'Mizo' , Mark_score, 0)) Mizo, SUM(IF(Subject = 'Mizo' , Full_mark, 0)) f_mz, SUM(IF(Subject = 'Hindi' , Mark_score, 0)) Hindi, SUM(IF(Subject = 'Hindi' , Full_mark, 0)) f_hn, SUM(IF(Subject = 'Mathematics', Mark_score, 0)) Mathematics, SUM(IF(Subject = 'Mathematics', Full_mark, 0)) f_maths, SUM(IF(Subject = 'SS' , Mark_score, 0)) SS, SUM(IF(Subject = 'SS' , Full_mark, 0)) f_ss, SUM(IF(Subject = 'Science' , Mark_score, 0)) Science, SUM(IF(Subject = 'Science' , Full_mark, 0)) f_sc, SUM(Full_mark) fmscore, SUM(Mark_score) score, SUM(Mark_score) / SUM(Full_mark) * 100 perc FROM cxexam, (SELECT @n := 0, @p := 0) n WHERE Test_date BETWEEN '2013-07-01' AND '2013-07-31' GROUP BY regd ORDER BY score DESC ) t ) r"; $result = mysql_query($sql); if(!$result) { die(mysql_error()); // TODO: better error handling } while($row = mysql_fetch_assoc($result)) { echo "{$row['regd']} - {$row['Rank']}<br>"; }

Выход (как ожидалось):

Для части рейтинга это 100% работает. Кажется, вы не очень хорошо знаете php.

 <?php $rank = $prevScore = 0; { $count++; // always increment if ($row_dep['perc'] > $prevScore) { //whenever a non-tie occurs the my rank catches up $my_rank = $count; $rank = $count; } else { //whenever a tie occurs, just use the my rank $rank = $my_rank; } $prevScore = $row_dep['perc']; echo $rank; } ?>

Проблема заключается в этой части кода: – SELECT *, IF (@marks = (@marks: = score), @auto, @auto: = @auto + 1). Вы должны убедиться, что логика правильная.