Как эффективно и эффективно сохранить несколько записей в mysql с помощью PHP?
У меня проблема со сценарием. Иногда он работает просто отлично, но иногда он плохо себя ведет и смешивает записи. Скрипт написан на php и является самой важной частью системы, используемой для записи оценок студентов преподавателями.
Записи сохраняются в базе данных MySql, и скрипт предназначен для отправки нескольких записей в базу данных одновременно. Эта часть хороша, так как записи подаются все время, но иногда сценарий меняет местами или смешивает оценки студентов.
Это моя проблема, и мне нужна помощь, чтобы ее решить.
Ниже приведен пример кода.
foreach($ExamMarks as $Key=> $Value)
{
$Student = array(explode(" ",$Value));
if(trim($_POST[$Student[0][1]]) == "Null" || trim($_POST[$Student[0][1]]) == " ")
{
$examGrade = "";
$comments = "";
$points = "";
}
else if(trim($_POST[$Student[0][1]]) >= 80)
{
$examGrade = "A";
$comments = "Outstanding performance. Keep it up!";
$points = 9;
}
else if (trim($_POST[$Student[0][1]]) >= 70)
{
$examGrade = "B";
$comments = "Good performance. Aim higher.";
$points = 7;
}
else if (trim($_POST[$Student[0][1]]) >= 60)
{
$examGrade = "C";
$comments = "Fairly good performance. Work hard.";
$points = 5;
}
else if (trim($_POST[$Student[0][1]]) >= 40)
{
$examGrade = "D";
$comments = "Average performance. Work harder.";
$points = 3;
}
else if (trim($_POST[$Student[0][1]]) >= 20)
{
$examGrade = "E";
$comments = "Below Average. Put more effort.";
$points = 1;
}
else
{
$examGrade = "U";
$comments = "Ungraded";
$points = 0;
}
mysqli_autocommit($con, FALSE);
if(preg_replace('#[^0-9]#i', '',trim($_POST[$Student[0][3]])) == "")
{
if(preg_replace('#[^0-9]#i', '',trim($_POST[$Student[0][1]])) != "")
{
$updateSQL = "UPDATE student_exam_marks SET
ExamMark = " . preg_replace('#[^0-9]#i', '',trim($_POST[$Student[0][1]])) . ",
ExamGrade = '" . $examGrade . "',
Comments = '" . $comments . "',
Points = " . $points . " ,
TeacherID = '" . $row_Select_Teacher["TeacherID"] . "'
WHERE ExamID = " . $ExamID . " AND StudentID = " . trim($Student[0][0]) . "
AND SubjectID = " . $_GET["SubjectID"] . " AND AcademicYear = " . date("Y") ;
}
}
else if(preg_replace('#[^0-9]#i', '',trim($_POST[$Student[0][3]])) != "" && preg_replace('#[^0-9]#i', '',trim($_POST[$Student[0][1]])) != "")
{
$updateSQL = "UPDATE student_exam_marks SET
ExamMark = " . preg_replace('#[^0-9]#i', '',trim($_POST[$Student[0][1]])) . ",
ExamGrade = '" . $examGrade . "',
TermMark = " . preg_replace('#[^0-9]#i', '',trim($_POST[$Student[0][3]])) . ",
Comments = '" . $comments . "',
Points = " . $points . " ,
TeacherID = '" . $row_Select_Teacher["TeacherID"] . "'
WHERE ExamID = " . $ExamID . " AND StudentID = " . trim($Student[0][0]) . "
AND SubjectID = " . $_GET["SubjectID"] . " AND AcademicYear = " . date("Y") ;
}
}//End foreach
Приведенный выше код производит эти SQL-операторы, которые должны быть немедленно отправлены в MySQL.
UPDATE student_exam_marks SET ExamMark = 49, ExamGrade = 'D', Comments = 'Average performance. Work harder.', Points = 3 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 31 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 56, ExamGrade = 'D', Comments = 'Average performance. Work harder.', Points = 3 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 32 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 58, ExamGrade = 'D', Comments = 'Average performance. Work harder.', Points = 3 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 47 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 60, ExamGrade = 'C', Comments = 'Fairly good performance. Work hard.', Points = 5 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 30 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 60, ExamGrade = 'C', Comments = 'Fairly good performance. Work hard.', Points = 5 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 48 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 66, ExamGrade = 'C', Comments = 'Fairly good performance. Work hard.', Points = 5 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 34 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 66, ExamGrade = 'C', Comments = 'Fairly good performance. Work hard.', Points = 5 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 44 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 70, ExamGrade = 'B', Comments = 'Good performance. Aim higher.', Points = 7 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 35 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 70, ExamGrade = 'B', Comments = 'Good performance. Aim higher.', Points = 7 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 49 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 76, ExamGrade = 'B', Comments = 'Good performance. Aim higher.', Points = 7 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 36 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 77, ExamGrade = 'B', Comments = 'Good performance. Aim higher.', Points = 7 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 42 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 77, ExamGrade = 'B', Comments = 'Good performance. Aim higher.', Points = 7 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 45 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 78, ExamGrade = 'B', Comments = 'Good performance. Aim higher.', Points = 7 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 29 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 80, ExamGrade = 'A', Comments = 'Outstanding performance. Keep it up!', Points = 9 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 50 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 87, ExamGrade = 'A', Comments = 'Outstanding performance. Keep it up!', Points = 9 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 33 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 88, ExamGrade = 'A', Comments = 'Outstanding performance. Keep it up!', Points = 9 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 37 AND SubjectID = 8 AND AcademicYear = 2016 UPDATE student_exam_marks SET ExamMark = 90, ExamGrade = 'A', Comments = 'Outstanding performance. Keep it up!', Points = 9 , TeacherID = '899622611' WHERE ExamID = 3 AND StudentID = 26 AND SubjectID = 8 AND AcademicYear = 2016
Что я уже пробовал:
Изначально система была разработана таким образом, что она представляла оценки одного студента за раз, но я хотел сделать вещи более легкими для учителей, чтобы они представляли оценки всех студентов сразу. Этот вариант работал и до сих пор работает нормально, но меня интересует улучшенная версия.
Mohibur Rashid
вы когда-нибудь пробовали такое?
Вставка в ценности students_record(значение1...), (значение2...), (значение3...)....(valuen...);
Кстати, MySQL-запрос имеет ограниченную длину до 80 000 (??) .. проверьте это тоже
Richard Deeming
Ваш код уязвим для SQL-инъекция[^]. НИКОГДА используйте конкатенацию строк для построения SQL-запроса. ВСЕГДА используйте параметризованный запрос.
Все, что вы хотели знать о SQL-инъекции (но боялись спросить) | Трой Хант[^]
Как я могу объяснить SQL-инъекцию без технического жаргона? | Обмен Стеками Информационной Безопасности[^]
Шпаргалка по параметризации запросов / OWASP[^]