Automattic\WooCommerce\Internal\Utilities
DatabaseUtil::insert_on_duplicate_key_update()
Hybrid of $wpdb->update and $wpdb->insert. It will try to update a row, and if it doesn't exist, it will insert it. This needs unique constraints to be set on the table on all ID columns.
You can use this function only when:
- There is only one unique constraint on the table. The constraint can contain multiple columns, but it must be the only one unique constraint.
- The complete unique constraint must be part of the $data array.
- You do not need the LAST_INSERT_ID() value.
Method of the class: DatabaseUtil{}
No Hooks.
Return
Int
. Returns the value of DB's ON DUPLICATE KEY UPDATE clause.
Usage
$DatabaseUtil = new DatabaseUtil(); $DatabaseUtil->insert_on_duplicate_key_update( $table_name, $data, $format ): int;
- $table_name(string) (required)
- Table name.
- $data(array) (required)
- Unescaped data to update (in column => value pairs).
- $format(array) (required)
- An array of formats to be mapped to each of the values in $data.
DatabaseUtil::insert_on_duplicate_key_update() DatabaseUtil::insert on duplicate key update code WC 9.6.1
public function insert_on_duplicate_key_update( $table_name, $data, $format ): int { global $wpdb; if ( empty( $data ) ) { return 0; } $columns = array_keys( $data ); $value_format = array(); $values = array(); $index = 0; // Directly use NULL for placeholder if the value is NULL, since otherwise $wpdb->prepare will convert it to empty string. foreach ( $data as $key => $value ) { if ( is_null( $value ) ) { $value_format[] = 'NULL'; } else { $values[] = $value; $value_format[] = $format[ $index ]; } ++$index; } $column_clause = '`' . implode( '`, `', $columns ) . '`'; $value_format_clause = implode( ', ', $value_format ); $on_duplicate_clause = $this->generate_on_duplicate_statement_clause( $columns ); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Values are escaped in $wpdb->prepare. $sql = $wpdb->prepare( " INSERT INTO $table_name ( $column_clause ) VALUES ( $value_format_clause ) $on_duplicate_clause ", $values ); // phpcs:enable // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared. return $wpdb->query( $sql ); }