Automattic\WooCommerce\Internal\Utilities

DatabaseUtil::insert_or_update │ public │ WC 1.0

Hybrid of $wpdb->update and $wpdb->insert. It will try to update a row, and if it doesn't exist, it will insert it. Unlike insert_on_duplicate_key_update it does not require a unique constraint, but also does not guarantee uniqueness on its own.

When a unique constraint is present, it will perform better than the insert_on_duplicate_key_update since it needs fewer locks.

Note that it will only update at max just 1 database row, unlike wpdb->update which updates everything that matches the $where criteria. This is also why it needs a primary_key_column.

Method of the class: DatabaseUtil{}

No Hooks.

Returns

true|false|Int. Number of rows affected. Boolean false on error.

Usage

$DatabaseUtil = new DatabaseUtil();
$DatabaseUtil->insert_or_update( $table_name, $data, $where, $format, $where_format, $primary_key_column, $primary_key_format );

$table_name(string) (required)

Table Name.

$data(array) (required)

Data to insert update in array($column_name => $value) format.

$where(array) (required)

Update conditions in array($column_name => $value) format. Conditions will be joined by AND.

$format(array) (required)

Format strings for data. Unlike $wpdb->update/insert, this method won't guess the format, and has to be provided explicitly.

$where_format(array) (required)

Format strings for where conditions. Unlike $wpdb->update/insert, this method won't guess the format, and has to be provided explicitly.

$primary_key_column(string)

Name of the Primary key column.
Default: 'id'

$primary_key_format(string)

Format for primary key.
Default: '%d'

DatabaseUtil::insert_or_update() DatabaseUtil::insert or update code ^{WC 10.7.0}

public function insert_or_update( $table_name, $data, $where, $format, $where_format, $primary_key_column = 'id', $primary_key_format = '%d' ) {
	global $wpdb;
	if ( empty( $data ) || empty( $where ) ) {
		return 0;
	}

	// Build select query.
	$values     = array();
	$index      = 0;
	$conditions = array();
	foreach ( $where as $column => $value ) {
		if ( is_null( $value ) ) {
			$conditions[] = "`$column` IS NULL";
			continue;
		}
		$conditions[] = "`$column` = " . $where_format[ $index ];
		$values[]     = $value;
		++$index;
	}

	$conditions = implode( ' AND ', $conditions );
	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $primary_key_column and $table_name are hardcoded. $conditions is being prepared.
	$query = $wpdb->prepare( "SELECT `$primary_key_column` FROM `$table_name` WHERE $conditions LIMIT 1", $values );

	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is prepared above.
	$row_id = $wpdb->get_var( $query );

	if ( $row_id ) {
		// Update the row.
		$result = $wpdb->update( $table_name, $data, array( $primary_key_column => $row_id ), $format, array( $primary_key_format ) );
	} else {
		// Insert the row.
		$result = $wpdb->insert( $table_name, $data, $format );
	}

	return $result;
}