How to edit mySQL wp_posts table from plugin php?

Question

I have a need to change a plugin that adds entries to the wp_posts table. Currently, it is lacking in that it cannot target some (other) plugin specific columns. I am endeavoring to edit the plugin so I can change these columns as well with the plugin.

How would I generally target this table then create/edit entries?

I have in mind something like this pseudo code:

function editTable() {
    $table = getTable(wp_posts);
    $tableRow = table.row(1234);
    $tableRow.column(1234) = "new data entry";
}

I assume this is possible because the plugin I'm hoping to change already makes changes to the wp_posts table. I realize, though, that my pseudo code may be way off. How do I do this?

Answer 1

There are two ways to edit the wp_posts table that I know of. The first is with the wp_update_post() and wp_insert_post() functions. These are typical WP functions that are used like any others. You just have to pass the correct parameters. For example:

$my_post = array(
      'ID'           => $updatePostID, // User defined variable; the post id you want to update.
      'post_title'   => 'Update Title',
      'post_content' => 'Update Content', 
  );
// Update the post into the database
$post_id = wp_update_post( $my_post, true ); // Returns 0 if no error; returns post id if there was an error.

// Check if table was updated without error. For debugging. Remove from your code if it all works, before publishing.
if (is_wp_error($post_id)) {
    $errors = $post_id->get_error_messages();
    foreach ($errors as $error) {
        echo $error;
    }
}

Simply pass an array with the predefined field names to the functions and they will then run correctly. The update function is for updates, naturally, and the insert function creates new table entries (rows). There is a limitation, however. These two functions will only operate on the WP predefined fields. So, if your table contains some custom fields, then you will have to use the $wpdb class.

The other way to update your mySQL tables from WordPress is to use the $wpdb class directly. This class is far more expansive than the wp_update_post() and wp_insert_post() functions. The first difference is that it can edit many other mySQL tables, not just the wp_posts table. It can also edit non-WP fields. For example:

global $wpdb; // This calls the use of the class. I actually do not think this is necessary, but I do it anyway.
$dbresult = $wpdb->update($wpdb->posts, // Returns 0 if no errors. Post ID if errors occurred. 
            // Notice posts instead of wp-posts. Prefixes are not necessary with this class.
     ['post_title' => 'Update Title', 
      'post_content' => 'Update Content', 
      'custom_field' => $customField, ], // User defined table column and variable.
     ['ID' => $updatePostID, ]); // User defined variable; the post id.
if (false === $dbresult) {
    echo 'An error occurred while updating...'; 
    $errors = $post_id->get_error_messages();
}

I'm not very familiar with either of these options, but there seems to a massive difference in the width of functionality, so there may be considerations you should take before using one over the other. I've asked a question to that end: What does wp_update_post() do that the $wpdb class does not? One reply says:

Downsides of $wpdb are that you do need to know SQL and you need to be conscious of normal data sanitization and when to use prepare() and what functions already prepare your statement which are hardly downsides at all. The $wpdb Class is by nature more powerful than any of the default WordPress functions because you have access to all the data, custom or not, assuming you know the proper SQL to get, modify, or remove it.

TL;DR $wpdb is more powerful but less friendly and forgiving.

That makes sense to me. Basically, don't fiddle with $wpdb unless you know what you are doing or absolutely have to.