php 姓名和年龄上传,PHP – 将学生姓名和成绩放入合适的技能名称

(1)class

(2)studentmark

(3)skill

PHP代码：

//DB CONNECTION

//---(1)Get skillname---

$q = "SELECT skillName FROM skill ORDER BY skillName asc";

$r = mysqli_query($dbc, $q);

$num_rows = mysqli_num_rows($r);

while($row = mysqli_fetch_array($r, MYSQLI_ASSOC))

{

$skills[] = $row['skillName'];

}

//---(2)Get classname---

$q1 = "SELECT className FROM class";

$r1 = mysqli_query($dbc, $q1);

$num_rows1 = mysqli_num_rows($r1);

while($row1 = mysqli_fetch_array($r1, MYSQLI_ASSOC))

{

$className[] = $row1['className'];

}

//---(3)Create table---

echo '

echo '

';

for($a = 0; $a < $num_rows; $a++)

{

echo '

'.$skillName[$a].'';

}

echo '

';

for($b = 0; $b < $num_rows; $b++)

{

echo '

Student Name';

echo '

Grade';

}

echo '';

//---(4)Get student name and grade---

for($s = 0; $c < $num_rows1; $c++)

{

$q2 = "SELECT GROUP_CONCAT(sm.studentName) as studentName,

GROUP_CONCAT(sm.studentGrade) as studentGrade,

s.skillName

FROM studentmark sm

LEFT JOIN skill s ON sm.skillID = s.skillID

WHERE sm.className = '".$className[$c]."'

GROUP BY s.skillID";

$r2 = mysqli_query($dbc, $q2);

$num_rows2 = mysqli_num_rows($r2);

$value = array();

while($row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC))

{

$value[] = $row2;

}

echo '

';

for($d = 0; $d < $num_rows2; $d++)

{

echo '

'.$value[$d]['studentName'].'';

echo '

'.$value[$d]['studentGrade'].'';

}

echo '

';

}

echo '

';

?>

从上面的代码,我的输出如下：

我差不多完成了.我可以在一行中显示学生姓名和成绩.

现在,我要做的最后一件事是将它们放入合适的技能名称,如下所示：

我想比较$q2上的$技巧和s.skillname.

以下是我的逻辑：

if($value[X]['skillName'] == $skills[X])

{

//put student name and grade inside

}

else

{

//empty cell

}

但我不知道我应该在哪里打开循环并将我的逻辑放入(4).有人能帮我吗？

最佳答案 因此,为了不多次循环数据,我肯定会弄乱你的干净代码.我还显示类名称原因,看起来像有用的信息.

我更改了一些变量名称,因为我发现更容易记住每个变量的用途.另外,请注意学生信息查询仅执行一次. Normaly(读：我想不出你为什么不会,但我是CMA),你想最小化你查询数据库的次数

下面的代码将替换您发布的整个脚本.

//DB CONNECTION

$dbc = // magic connection sauce you already have

// get skills and stash how many there are

$q_class = "SELECT skillName FROM skill ORDER BY skillName asc";

$r_class = mysqli_query($dbc, $q_class);

$num_skills = mysqli_num_rows($r_class);

// start table code so that we can echo the skillname headers

echo '

Classes';//header for class name column

$header = array();

while($row = mysqli_fetch_array($r_class, MYSQLI_ASSOC))

{

$skills[] = $row['skillName'];

// store both thead rows at the same time so that we can echo them out properly later

$header['first'][] = '

' . $row['skillName'] . '';

$header['second'][] = '

Student NameGrade';

}

echo '

' . implode($header['first']) . '

' . implode($header['second']) . '';

// clean-up

mysqli_free_result($r_class);

// get class names and stash how many there are

$classes = array();

$query_class = "SELECT className FROM class";

$r_class = mysqli_query($dbc, $query_class);

$num_classes = mysqli_num_rows($r_class);

while($row = mysqli_fetch_array($r_class, MYSQLI_ASSOC))

{

$classes[] = $row['className'];

}

// clean-up

mysqli_free_result($r_class);

echo '

';

// pull query out of loop so that you'll only have to execute it once.

$studentInfoQuery = "

SELECT

GROUP_CONCAT(sm.studentName) as studentName,

GROUP_CONCAT(sm.studentGrade) as studentGrade,

s.skillName,

sm.className

FROM studentmark sm

LEFT JOIN skill s ON sm.skillID = s.skillID

GROUP BY sm.className,s.skillID";

$r_students = mysqli_query($dbc,$studentInfoQuery);

$num_studentRows = mysqli_num_rows($r_students);

$studentRows = array();

while($row = mysqli_fetch_array($r_students, MYSQLI_ASSOC)) {

// with our query, we only find 1 cell-pair per skill per class

$studentRows[$row['skillName']][$row['className']] = '

' . $row['studentName'] . '' . $row['studentGrade'] . '';

}

// everybody do their share! // actually, more clean-up

mysqli_free_result($r_students);

for($j = 0; $j < $num_classes; $j++) {

echo "

" . $classes[$j] . "";

for($i = 0; $i < $num_skills; $i++) {

// always echo out a cell, even if we have student info for it

// example: if(isset($studentRows['Listening']['1A'])) echo it out else echo cell

if(isset($studentRows[$skills[$i]][$classes[$j]]))

echo $studentRows[$skills[$i]][$classes[$j]];

else

echo "

No skill-class-student value";

}

echo "

";

}

echo '

';

?>

结果：