php - 如何在php中使用mysql_fetch_assoc处理记录？

Question

我正在尝试检索主题名称，有两种类型的主题可选和主要。每个学生都包含选修课和主修课。通过使用这些主题 ID，我可以从主题表中检索名称。但在这个结果中只出现了一个可选的主题名称，我的代码有什么问题吗？请帮助我

  $subject_names = array();
for($i=0;$i<count($student_num_data);$i++)
{
    $optional_id_list = mysql_query("SELECT optional_subject_id FROM ms_student WHERE student_id = ".$student_num_data[$i]['student_id']);
    while($row = mysql_fetch_assoc($optional_id_list))
    {
        foreach ($row as $key) 
        {
            $optional_subject = mysql_query("SELECT subject_name FROM ms_subject WHERE subject_id = ".$key['optional_subject_id']); 
            $optional_subject_name = array();
            while($row1 = mysql_fetch_assoc($optional_subject))
            {
                $optional_subject_name[] = $row1;  
            }
        }
    }
    $subject_id_list = mysql_query("SELECT subject_ids FROM subject_config WHERE stream_index =".$stu_stream);  
    while($row = mysql_fetch_assoc($subject_id_list))
    {
        foreach ($row as $key) 
        {
            $main_subject = mysql_query("SELECT subject_name FROM ms_subject WHERE subject_id = ".$key['subject_ids']); 
            $main_subject_name = array();
            while($row3 = mysql_fetch_assoc($main_subject))
            {
                $main_subject_name[] = $row3;  
            }
        }
    } 
    $subject_names = $main_subject_name[0]['subject_name'].','.$optional_subject_name[0]['subject_name'];

    $small_subject_name=trim($subject_names);//remove whitespace at end of string         
    $small_subject_name = str_replace(" ", "-", $small_subject_name); // Replaces all spaces with hyphens.     
    $small_subject_name = preg_replace('/[^A-Za-z0-9\-]/', '', $small_subject_name); // Removes special chars.     
    $small_subject_name =  preg_replace('/-+/', '-', $small_subject_name); // Removes multiple hyphens.            
    $small_subject_name = str_replace("-", " ", $small_subject_name); // Replaces all hyphens with spaces.            
    $small_subject_name=strtolower(str_replace(" ","_",$small_subject_name));  

    if($stu_class == 11 || $stu_class == 12)
    {  
        $subject_statement = $subject_statement."IF(".$small_subject_name."='ab','ab',round(".$small_subject_name.",2)) AS ".$small_subject_name.",";   
        $sum_subject_statement = $sum_subject_statement.'IFNULL(`'.$small_subject_name.'`,0) + ';           
        /* start by 1022 02-09-2014 */
        $subject_pass_statement .= $small_subject_name." >= 33 AND ";       
        $subject_pass_statement = substr($subject_pass_statement,0,strlen($subject_pass_statement)-5);
        $update_pass_statement = "UPDATE ".$table_name." SET class_rank = 'P' WHERE 1=1 ".$track_div_stmnt.$id_stmnt." AND student_id = ".$student_num_data[$i]['student_id']." AND ".$subject_pass_statement;
        $update_pass_query=mysql_query($update_pass_statement);
    }
}

Answer 1

这与mysql_fetch_assoc函数无关，而是关于代码的逻辑。您遇到了困难，主要是因为您的代码的外观。您同时面临太多问题，最合理的解决方案是:

分解问题

1. 思考这段代码将执行的主要任务/操作是什么。
   比方说:
   1. 对于每个学生，我们都会检索他们注册的选修科目。
   2. 然后我们检索他们正在学习的主要科目。
   3. 然后我们检索他们的成绩，以确定他们通过了哪些科目，哪些科目未通过。

2. 从表达您意图的代码开始，使用尚不存在的函数:

    $passStatementForYear = new PassStatement($year);
    $students = getStudentsForSchoolYear($year);
    foreach ($students as $student) {
        $optionalSubjects = loadOptionalSubjectsForStudent($student);
        $mainSubjects = loadMainSubjectsForStudent($student);
        $passReport = prepareReportForStudent(
            $student, $optionalSubjects, $mainSubjects
        );
        updatePassStatementUsingStudentsReport(
            $passStatementForYear, $passReport
        );
    }
   

3. 构建缺失的部分(函数、类等)

现在您只需处理这些独立步骤即可。每个功能都会引入新的小问题，您可以以孤立且简单的方式处理这些问题。只有这样，如果您发现自己遇到 mysql_fetch_assoc 问题，引用官方文档和他们展示的示例:

$sql = "SELECT id as userid, fullname, userstatus
        FROM   sometable
        WHERE  userstatus = 1";

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
}

mysql_free_result($result);