Bulk UPDATE

[mshs013]

Please add a function for bulk update.

[mshs013]

I've created bellow function for bulk data update. Please check and let me know about this function. Function example is:

$tableName = 'users';
$multiUpdateData = [
     [
         'id' => 1,
         'name' => 'Sazzad',
         'updatedAt' => $db->now()
     ] ,
     [
         'id' => 5,
         'name' => 'Kanon',
         'updatedAt' => $db->now()
     ] ,
];

$index = 'id';
$db->updateBatch($tableName, $multiUpdateData, $index);

/**
 * Update Multi fields.
 *
 * @param string $tableName The name of the database table to work with.
 * @param array $multiUpdateData Two-dimensinal Data-array containing information for updating data into the DB.
 * @param string $index The index column the database table.
 *
 * @return bool
 */
public function updateBatch($tableName, $multiUpdateData, $index = 'id')
{
	$final  = array();
	$ids    = array();
	if(!count($multiUpdateData))
		return false;
	foreach ($multiUpdateData as $k => $val)
	{
		$ids[] = $val[$index];
		foreach (array_keys($val) as $field)
		{
			if ($field !== $index)
			{
				$value = $val[$field];
				// Function value
				if (is_array($value)) {
					$key = key($value);
					$v = $value[$key];
					switch ($key) {
						case '[I]':
							$value = $field . $v . " ";
							break;
						case '[F]':
							$value = $v[0] . " ";
							break;
						case '[N]':
							if ($v == null) {
							$value = "!" . $field . " ";
							} else {
							$value = "!" . $v . " ";
							}
							break;
						default:
							throw new Exception("Wrong operation");
					}
				} else {
					$value = '"' . $value . '" ';
				}
				$final[$field][] = 'WHEN `' . $index . '` = "' . $val[$index] . '" THEN ' . $value;
			}
		}
	}

	$cases = '';
	foreach ($final as $k => $v)
	{
		$cases .= $k.' = (CASE '. implode("\n", $v) . "\n"
		. 'ELSE '.$k.' END), ';
	}
	$query = 'UPDATE ' . $tableName . ' SET '. substr($cases, 0, -2) . ' WHERE ' . $index . ' IN('.implode(',', $ids).')';

	$this->_query = $query;
	$stmt = $this->_prepareQuery();

	$status = $stmt->execute();
	$this->reset();
	$this->_stmtError = $stmt->error;
	$this->_stmtErrno = $stmt->errno;         
        $this->_lastQuery = $query;
	$this->count = $stmt->affected_rows;

	return $status;
}	

[harryqt]

I've tested this with a basic data and it works fine. But $db->getLastQuery() does not work.

Update: As it seems that you're using CASE operator which very slow compared to INSERT ON DUPLICATE KEY UPDATE - Read here.

[mshs013]

@Dibbyo456 now $db->getLastQuery() works.

[harryqt]

Thanks for this. But I’ve built my own. 😅

[mshs013]

@Dibbyo456 can you share your code?

[harryqt]

can you share your code?

https://github.com/Dibbyo456/mysqli-database-class#bulk-update