基于HTML的更新查询选择选项阵列PHP

Question

HTML

            <input type="hidden" name="coreid[]" value="<?php echo $coreid; ?>">
            <input type="hidden" name="student_no" value="<?php echo $student_no; ?>">

            <div class="col col-lg-2 col-md-2 col-xs-2">
                <div class="form-group">
                    <select name="corevalues[]" class="form-control">
                        <option value="">Select</option>
                        <?php 

                $result11 = mysqli_query($con, "SELECT * FROM core_values");

                while ($row11 = mysqli_fetch_array($result11)){
                    $idcore = $row11['id'];
                    $levelid = $row11['marking'];
                    $level_name = $row11['non_marking'];
                    if ($first == $level_name){
                        echo '<option value="'.$level_name.'" selected="selected">'.$level_name.'</option>';
                    } else{
                        echo '<option value="'.$level_name.'">'.$level_name.'</option>';
                    }
                }
                        ?>
                    </select>
                </div>
            </div>

PHP

$coreid = $_POST['coreid'];
$grade = $_POST['corevalues'];
$student_no = $_POST['student_no'];
$quarter = $_POST['quarter'];
$marking = $_POST['marking'];
$quarter2 = $_POST['quarter2'];

$xx = 0;



foreach ($coreid as $sid) {
    foreach ($grade as $sid2) {
       $checkrecords = mysqli_query($con, "SELECT * FROM teacher_cv WHERE coreid='".$sid."' AND student_no='".$student_no."'");

           if (mysqli_num_rows($checkrecords) > 0){

               $sql = "UPDATE teacher_cv
                        SET $quarter='".$sid2."'
                                WHERE coreid='".$sid."' AND
                                    student_no='".$student_no."'";

            if (!mysqli_query($con,$sql))
            {
                die('Error: 1 ' . mysqli_error($con));
            }
               $xx++;
           } else{

               $sql = "INSERT INTO teacher_cv
               (         coreid,
                            $quarter,
                                student_no)
            VALUES (      '".$sid."',
                            '".$sid2."',
                                '".$student_no."')";

               if (!mysqli_query($con,$sql))
               {
                   die('Error: 1 ' . mysqli_error($con));
               }
               $xx++;
       }
    }
}

Im Updating my table teacher_cv with multiple data base on HTML Select Option. To show what standard of the core value in teachers student.

But it seems Update and Insert Query is always getting the last part when it comes to first_q which a column in my table. Here is the sample that ive made so far.

It seems the core id is working but the standard that selected in HTML Select is always getting the last part of the array. How can I Insert/Update all the array not the last part of array?

UPDATE

I tried printing it before the foreach to see if its really passing the array or not and here is the outcome.

    Array ( [0] => 1 [1] => 2 [2] => 3 [3] => 4 [4] => 5 ) 
    Array ( [0] => Sometimes Observed [1] => Rarely Observed [2] => Rarely Observed [3] => Not Observed [4] => Sometimes Observed )

Thanks to Kunal now I know that the array is really passing. Why only the last of the Array is the inputted on the database? Whats Wrong with mo PHP Code?

Answer 1

You shouldn't be using nested loops with $coreid and $grade, because the corresponding elements of each array go together. You were creating a cross product between all of them. So it should be:

foreach ($coreid as $i => $sid) {
    $sid2 = $grade[$i];
    ...
}

Also, since you're using MySQLI, you should use prepared queries instead of concatenating variables to the queries. And if there's a unique index on (coreid, student_no), you can do everything in a single INSERT query with the ON DUPLICATE KEY UPDATE option.

$stmt = mysqli_prepare($con, "INSERT INTO teacher_cv (coreid, quarter, student_no)
    VALUES(?, ?, ?)
    ON DUPLICATE KEY UPDATE quarter = VALUES(quarter)" or die(mysqli_error($con));;
mysqli_stmt_bind_param($stmt, "sss", $sid, $sid2, $student_no);

foreach ($coreid as $i => $sid) {
    $sid2 = $grade[$i];
    mysqli_stmt_execute($stmt);
    $xx++;
}