The question:
I’m not crazy familiar with WPDB or SQL in general but I have a custom table for my project and I’m trying to assign some metadata to it. What I’d “like” to happen is if a row exists, update it and if not insert it. I’ve read both Insert and Update in the WPDB Codex but neither really went into an “either or” situation. I thought i could work with update, so my code so far looks like this:
$wpdb->update(
$wpdb->prepare(
$wpdb->prefix.'item_info',
array(
'post_id' => $post_id,
'item_stock' => $item_stock
),
array('post_id' => $post_id)
)
);
Does WordPress have anything like an “IF exists Update, ELSE Insert”, or do I need to run custom SQL to achieve this, or do I need to query the database first to see if an ID exists in my table THEN decide whether to update it or insert it?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
First, you are using prepare
incorrectly. You seem to have $wpdb->update
‘s arguments wrapped in $wpdb->prepare
like that. That won’t work. In effect, you are passing update
a single argument– the output of prepare
. Try something simple like the following and you will see why that won’t work:
$post_id = 123;
$item_stock = 567;
var_dump(
$wpdb->prepare(
$wpdb->prefix.'item_info',
array(
'post_id' => $post_id,
'item_stock' => $item_stock
),
array('post_id' => $post_id)
)
);
And $wpdb->update()
runs prepare
for you.
Second, if this were me, I skip the helper function bloat and write a proper ON DUPLICATE KEY UPDATE
query:
$sql = "INSERT INTO {$wpdb->prefix}item_info (post_id,item_stock) VALUES (%d,%s) ON DUPLICATE KEY UPDATE item_stock = %s";
// var_dump($sql); // debug
$sql = $wpdb->prepare($sql,$post_id,$item_stock,$item_stock);
// var_dump($sql); // debug
$wpdb->query($sql);
This assumes that post_id
is a UNIQUE
index or PRIMARY KEY
. If your table structure is what I think it is, let the database handle it.
Method 2
Have you tried $wpdb->replace
. According to WP Codex:
Replace a row in a table if it exists or insert a new row in a table if the row did not already exist.
I have tried myself in some plugins and it does the work when trying to avoid unique IDs duplication errors, etc.
Method 3
You can try and update the table using $wpdb->update
, I am using the id in the following code, but you can use any criteria.
$result = $wpdb->update($tableName, $info, array('id' => $info["id"]));
//If nothing found to update, it will try and create the record.
if ($result === FALSE || $result < 1) {
$wpdb->insert($tableName, $info);
}
Update() result output
$result === FALSE
: Fail$result === 0
: Success, but nothing updated$result > 1
: Success and updated
Method 4
You should check if the row exists first.
Most likely you’ll want to try to get the ID or primary key for the row you’re trying to update, then $wpdb->update
if it does or $wpdb->insert
of it doesn’t
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0