Meyka Jograt Meyka Jograt - 7 months ago 32
SQL Question

Update Query base on HTML Select Option Array PHP

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.
enter image description here

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

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++;
}
Comments