Bulk MySQL update instead of PHP While loop

Question

I am using PHP code to update week ending date which is on a different day for each subdomain. Each subdomain date range varies.

My current code uses a while loop to update the week end date. This is very slow and I am looking for ideas to optimise it.

Here is my current code:


// Loop through and assign week end date to each user activity.
$nextWeek = $startDate;  // initialise while loop condition
while ($nextWeek <= $endDate) {
    $lastWeek = $nextWeek;
    $nextWeek = Carbon::parse($lastWeek)->addWeek(1)->format('Y-m-d');

    // update activity date to week end date
    $query = 'UPDATE r_active_user_activities 
                SET week_end_date = "' . $nextWeek . '"
                WHERE (activity_date > "' . $lastWeek . '" 
                    AND activity_date <= "' . $nextWeek . '"  
                    AND subdomain="' . $subdomain->subdomain . '" );';
    $db->statement($query);
}

Save all data array into temporary table by one query, then update the whole table in interest by second query. — Akina, Jan 30 '20 at 06:09
I think this needs a much more clear description of your data model, before this becomes actually answerable … — 04FS, Jan 30 '20 at 08:49
You can also move the date calculation logic inside the query and run it for all sub-domains (if possible). As @04FS mentioned, the question needs more details to suggest a better alternate. — Shahzad Malik, Jan 30 '20 at 09:01

score -1 · Answer 1 · answered Jan 30 '20 at 09:21

Just append all those update queries in a php variable ($query) and execute that outside the while loop. something like this.

$nextWeek = $startDate;  // initialise while loop condition
$query = '';
while ($nextWeek <= $endDate) {
$lastWeek = $nextWeek;
$nextWeek = Carbon::parse($lastWeek)->addWeek(1)->format('Y-m-d');

// update activity date to week end date
$query .= 'UPDATE r_active_user_activities 
            SET week_end_date = "' . $nextWeek . '"
            WHERE (activity_date > "' . $lastWeek . '" 
                AND activity_date <= "' . $nextWeek . '"  
                AND subdomain="' . $subdomain->subdomain . '" );';
 }
$db->statement($query);

Bulk MySQL update instead of PHP While loop

1 Answers1