multiple update in php with one button click

[aoi.serizawa.9]

hey guys im trying to update my database here, the code is running fine

session_start();

        $school_id = $_SESSION['school_id'];
        $faculty_id = $_SESSION['user_id_fac'];
        $subject_id = $_POST['subject_id'];
        $year_grade_level = $_POST['year_level'];
        $subject_handeler_id = $_POST['subject_handler_id'];
        $student_grades_boy = $_POST['student_grades_boy'];
        $student_grades_girl = $_POST['student_grades_girl'];


        $values_boy = array();


         foreach ($student_grades_boy as $key=>$data) {
                                    $student_id_B= $data['studnt_B_id'];
                                    $grade_B = $data['studnt_grade_B'];

            $values_boy[$key] = "UPDATE registrar_grade_archive SET grade = ".$grade_B." WHERE student_id = ".$student_id_B." AND subject_id = ".$subject_id." AND school_id = ".$school_id." AND advisor_faculty_id = ".$faculty_id." AND subject_handler_id = ".$subject_handeler_id." ";



            $save_grades_boy = mysql_query($values_boy[$key]) or die(mysql_error());
                                }

but it keeps bugging me, cuz every loop the php will make a request query wich may slowdown my site. Is there a way to improve the codes on multiple updating my table in database?

[diafol]

This is a common issue. Update queries (as in your example) tend to be unique for each record as they often have to specify an 'id' field in the WHERE clause. This usually means that they cannot be used for multiple records. Difficult to see how your data is being passed from the form. Can you show the form?

[broj1]

I think a little bit of optimization can be done using prepared statements:

...
// define the query with ? in places of variables
$query = "UPDATE registrar_grade_archive 
SET grade = ? 
WHERE student_id = ? 
AND subject_id = ? 
AND school_id = ? 
AND advisor_faculty_id = ? 
AND subject_handler_id = ?";

// prepare the query
$stmt = mysqli->prepare($query);

// loop through array of variables
foreach ($student_grades_boy as $key=>$data) {

    $student_id_B= $data['studnt_B_id'];
    $grade_B = $data['studnt_grade_B'];

     // bind parameters to the query for each loop
     // I assume here that all parameters are integers
     $stmt -> bind_param(
         "iiiiii",    // set parameter types (all integers here)
         $grade_B, 
         $student_id_B,
         $subject_id,
         $school_id,
         $faculty_id,
         $subject_handeler_id
     );

    // execute the query for this loop
    $result = $stmt -> execute();

    if(!$result) {

        die ('Error updating the database.');
    }
}

You get slighly better performance this way and quite a lot of security since prepared statements are great way of preventing injections.

Please note I have used the newer mysqli extension since the older mysql extension (which you are using) does not support prepared statements to my knowledge.

I haven't actually tested above code (since I am using PEAR MDB2 for cases like this) so please somebody have a look at it too and correct me if I am wrong.

[ajbest]

I do not think there is a way to handle multiple row updates in a database from a single query. I understand that you are concerned about the speed of the loop but this is not where the bottle neck would happen.

Lets say you had 2000 records to loop through which is about the limit where you might see an effect of the time. In order to even have the data to update you would have had to send a form to your user to fill out that had at least 1 input per record to be updated. This would be a huge file and would take the user at least a few seconds to download. Furthermore, PHP by default wont even accept more than 200 keys in the $_POST array.

Assuming you are not reconfiguring PHP to accept more post values, your current code should be more than fast enough with regard to mysql. However there are some memory issues that could be improved upon. You could remove the $values_boy array which seems to just store the queries that were executed and instead go straight to the query. There are also other variables that could be bypassed.

replace your lines 16-23 with

       mysql_query("UPDATE registrar_grade_archive 
                   SET grade = ".$data['studnt_grade_B']." 
                   WHERE student_id = ".$data['studnt_B_id']." 
                       AND subject_id = ".$subject_id." 
                       AND school_id = ".$school_id." 
                       AND advisor_faculty_id = ".$faculty_id." 
                       AND subject_handler_id = ".$subject_handeler_id." ") or die(mysql_error());