Is there a way to avoid updating all data when using update_batch()?

Question

My current problem is that all data are being updated all at once when I use update_batch(), is there a way to update only the last data?

I am updating grades every semester that is why I need to update the latest grades in this semester. However whenever I update grades, the latest update will also update the previous grades in my semester that is why it is being overwritten. Hence, I only want to update the latest grades in this semester. I also used update_batch to update multiple records, am I wrong doing so?

Here is my controller:

        $schoolYear = $csvData[0]['schoolYear'];
        $semester = $csvData[0]['semester'];

        $studentIDFromDB = $this->importSISAcademicInformationGetStudentIDFromDB($schoolYear, $semester);   

            foreach ($csvData as $key => $value) {                      
                if(isset($studentIDFromDB[$value['studentID']])){
                    $updateFields[$key] = array(                                
                        'studentID'             => $value['studentID'],
                        'programType'           => $value['programType'],
                        'level'                 => $value['level'],
                        'sectionDescription'    => $value['sectionDescription'],
                        'schoolYear'            => $value['schoolYear'],
                        'semester'              => $value['semester']                               
                    );
                }
            }

            if(isset($updateFields))
            $this->DBLogic->updateBatchRecordMultipleCondition('tbl_tt_academicinfo', $updateFields, 'studentID', array('schoolYear' => $schoolYear, 'semester' => $semester));

Here is my Model:

public function updateBatchRecordMultipleCondition($table, $fields, $criteria1, $criteriaN){        
    $this->db->where($criteriaN);
    $this->db->update_batch($table, $fields, $criteria1);       
}

maybe this can help https://stackoverflow.com/questions/31939336/how-to-only-update-last-inserted-record-in-php-codeigniter — Rao DYC, Jan 24 '22 at 14:43
@RaoDYC I've tried the `inser_id()` before and still it does produce the same output — Uzumaki Naruto, Jan 24 '22 at 14:46
*last data that I will need to update*: what is this the last data? the last set in the array? The last value inserted in the database? the highest id in the database? Please edit your question with this "detail". AND: Why use update_batch, if you only want to update 1 record set? — Vickel, Jan 24 '22 at 15:43
@Vickel - Hello, good day, I have updated my question kindly take a look and I hope it answers your questions. Please let me know if you still have questions to my answer and thank you for having your time. — Uzumaki Naruto, Jan 24 '22 at 15:56
You can't use update_batch, as it won't allow for multiple where criteria. It's designed to update by single attribute, which would typically be a key. Why are "updating" from a spreadsheet, and thus overwriting existing grades, when you should be inserting the new grades? What does the spreadsheet contain? — gview, Jan 25 '22 at 05:56

Vickel · Answer 1 · 2022-01-24T16:30:30.770

0

In Codeigniter 3.x, the function batch_update(), as by documentation has its where clause set in the 3rd parameter of the function and not by a preceding where() clause.

without knowing anything else about your variables and data, most likely this will do it:

$this->db->update_batch($table, $fields, $criteriaN);

Anyway, make sure you follow these indications from doc:

update_batch($table[, $set = NULL[, $value = NULL[, $batch_size = 100]]])
Parameters:

$table (string) – Table name

$set (array) – Field name, or an associative array of field/value pairs

$value (string) – Field value, if $set is a single field

$batch_size (int) – Count of conditions to group in a single query

Returns:

Number of rows updated or FALSE on failure

hint: In case you cannot set the where clause in the update_batch() function, use the update() function - which allows you to set a preceding where() - within you foreach loop.

edited Jan 24 '22 at 16:30

answered Jan 24 '22 at 16:15

Vickel

7,879
6
35
56

hello, it says `One or more rows submitted for batch updating is missing the specified index.` when I remove the `where` clause, can you help me further, please? – Uzumaki Naruto Jan 24 '22 at 16:40
check the *hint* of my updated answer, I was several times not able to create a simple batch_update and reverted to the "normal" update, which you can control much better individually – Vickel Jan 24 '22 at 16:50
so far I have done this, `$this->db->set($fields); $this->db->where($criteriaN); $this->db->update($table);` but it did give me an error of `Message: Array to string conversion` – Uzumaki Naruto Jan 24 '22 at 16:54
*without knowing anything else about your variables and data* it is difficult to help any further, the error says you are using an array instead of a string, you can print_r($your_variable) to check data structure and then take it from there – Vickel Jan 24 '22 at 17:04
@UzumakiNaruto update can't handle records set (multidimensional associative arrays). You should use a loop (poor perfomance) or update_batch adn, if you do so with update_batch, can't use set. (not sure about where). – Manuel Guzman Jan 24 '22 at 17:47
@Manuel Guzman, can you help me on this, please? – Uzumaki Naruto Jan 24 '22 at 23:36
1

I think your only solution is updating it 1 by 1. Your where clausule needs more than one key, and CI3 doesn't allow it. Not sure about CI4 – Manuel Guzman Jan 24 '22 at 23:48
Take a look to this question https://stackoverflow.com/questions/7426094/codeigniter-batch-update-with-multiple-where-conditions – Manuel Guzman Jan 24 '22 at 23:49

Is there a way to avoid updating all data when using update_batch()?

1 Answers1