任何替代解决方案，使这项工作？ Php代码不工作，但mysql代码是工作 [英] Any alternative solution for getting this work? Php code is not working right but mysql code is working
本文介绍了任何替代解决方案，使这项工作？ Php代码不工作，但mysql代码是工作的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我一直在努力与以下代码很长时间。我是PHP的新人。这个mysql代码在phpmyadmin中是100％工作。我使用<？php echo $ row ['Rank'];？> 但我只有1为所有学生。但是sql代码工作正常。我已经使sql在这里fiddle http://sqlfiddle.com/#!2/24855/1 请帮助我。
I have been struggling with the following codes for long time now. I am new in PHP. This code of mysql is 100% working in phpmyadmin. I am using <?php echo $row['Rank'];?> but I only get 1 for all students. But the sql code is working fine. I have made the sql fiddle here http://sqlfiddle.com/#!2/24855/1 Please help me.
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部分。它似乎确定。它也正确地回应一切，除了它不正确地输出秩。在排名行中，它只显示所有学生的排名 1 。你可以从下面的代码中理解我想实现的。我真的需要你的帮助。我知道你们很熟悉这样的问题。
Here is the PHP part. It seems OK. It also echo everything correctly except that it does not output the Rank correctly. In the rank row it only displays 1 as rank for all students. You can understand from the below code what I am trying to achieve. I really need your help. I know you guys are well familiar with such problem.
<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 。
The parts which are not working: rank and result. Any help is appreciated.
推荐答案
您的sql代码有几个问题（例如never mix DISTINCT 和 GROUP BY ，在内部选择等中使用 WHERE ）。也就是说，你的查询应该看起来像
There are several problems with your sql code (e.g. never mix DISTINCT and GROUP BY, use WHERE in inner select etc). That being said your query should look something like
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 a> 演示
Here is SQLFiddle demo
现在是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>";
}
输出（如预期）：
40 - 1
2 - 2
3 - 2
20 - 3
这篇关于任何替代解决方案，使这项工作？ Php代码不工作，但mysql代码是工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
