WHERE子句没有过滤结果

Question

我在我的模型中有这个工作函数，我试图根据他们的学科分数获得学生在班级中的位置。

这是工作的，但是where子句没有过滤结果以根据主题对它们进行分类。

模型:

public function GetSubjectPosScores($exam_group_class_batch_exam_subject_id) 
{
$sql = "SELECT `get_tot_score` FROM `exam_group_exam_results` 
WHERE `exam_group_class_batch_exam_subject_id`= `exam_group_class_batch_exam_subject_id` ORDER BY `get_tot_score` DESC";
$query = $this->db->query($sql);
return $query->result();
}

控制器:

public function GetSubjectPosScores($exam_group_class_batch_exam_subject_id)
{
$data = $this->examresult_model->GetSubjectPosScores($exam_group_class_batch_exam_subject_id); 
return $data;
}

视图:

<?php
$scores2 = $CI->GetSubjectPosScores($exam_group_class_batch_exam_subject_id); //echo $value->pos;
$scores = array_column($scores2, 'get_tot_score');
$pos = array_search($exam_result_value->get_tot_score, $scores);
$number = $pos + 1;
echo $CI->ordinal($number);?>

这是我用这个查询得到的结果:

student_id | subject_id | get_tot_score | Position
---------------------------------------------------
11        |   1        |   76          |  3rd
12        |   1        |   90          |  1st
28        |   6        |   89          |  2nd
30        |   6        |   70          |  4th

我想要的是:

student_id | subject_id | get_tot_score | Position
---------------------------------------------------
11        |   1        |   76          |  2nd
12        |   1        |   90          |  1st
28        |   6        |   89          |  1st
30        |   6        |   70          |  2nd

Answer 1

问题出在你的查询中

SELECT `get_tot_score` FROM `exam_group_exam_results` 
WHERE `exam_group_class_batch_exam_subject_id`= `exam_group_class_batch_exam_subject_id` ORDER BY `get_tot_score` DESC

基本上你是在检查列exam_group_class_batch_exam_subject_id中的值等于exam_group_class_batch_exam_subject_id

列的值这始终是真实的

你必须改变它传递你的值$exam_group_class_batch_exam_subject_id使用某种类型的语句(我不知道你正在使用什么库)

编辑我看了一下codignirer文档

just try this

public function GetSubjectPosScores($exam_group_class_batch_exam_subject_id) 
{
$sql = "SELECT `get_tot_score` FROM `exam_group_exam_results` 
WHERE `exam_group_class_batch_exam_subject_id`= ? ORDER BY `get_tot_score` DESC";
$query = $this->db->query($sql, [$exam_group_class_batch_exam_subject_id]);
return $query->result();
}

Answer 2

您在where条件中指定了字符串值，而不是在getSubjectPosScores函数中作为参数传递的变量值

在查询中使用字符串插值

代替

public function GetSubjectPosScores($exam_group_class_batch_exam_subject_id) { $sql = "SELECTget_tot_scoreFROMexam_group_exam_resultsWHEREexam_group_class_batch_exam_subject_id=exam_group_class_batch_exam_subject_idORDER BYget_tot_scoreDESC"; $query = $this->db->query($sql); return $query->result(); }

与

public function GetSubjectPosScores($exam_group_class_batch_exam_subject_id) { $sql = "SELECT get_tot_score FROM exam_group_exam_results WHERE exam_group_class_batch_exam_subject_id= $exam_group_class_batch_exam_subject_id ORDER BY get_tot_score DESC"; $query = $this->db->query($sql); return $query->result(); }

或

public function GetSubjectPosScores($exam_group_class_batch_exam_subject_id) { $sql = "SELECTget_tot_scoreFROMexam_group_exam_resultsWHEREexam_group_class_batch_exam_subject_id= '".$exam_group_class_batch_exam_subject_id."' ORDER BYget_tot_scoreDESC"; $query = $this->db->query($sql); return $query->result(); }