Learn WordPress CRUD Tutorial with wpdb Object

In this article we will see about insert, update & delete data into wordpress database table. In the previous articles we discussed about global wordpress database object, wordpress methods like get_var, get_row, get_col & get_results to select data from table into different different formats.

WordPress manages it’s database very well. It takes care about each IN and OUT data behaviour. Everything in wordpress is managed by database either it will be post, pages, medias, users, categories etc.

We are going to use the concept of WordPress Global Database Object.

Any user who has the knowledge of programming or not they can operate/manage wordpress application with it’s database.

Learn More –

• How to use WordPress Global Database Object ?
• Basics Overview of MySQL Stored Procedures
• Complete Basics Overview of WordPress Widget
• Create a Basic Widget Plugin in WordPress Tutorial

Let’s get started about WordPress CRUD Tutorial with wpdb Object.

How to Connect with WordPress Database ?

To make database connection with the wordpress we need to use wpdb class. WordPress database class file stored in /wp-includes/wp-db.php

wpdb is a core class file which we need to load via creating an instance. Either we can create a custom instance of wpdb class file. The best way to connect with the database is to use wordpress available global object $wpdb.

The class “wpdb” abstracts the wordpress database functions. Most of the WordPress functions directly or indirectly use wpdb class. WordPress class wpdb based on the concept ezSQL class.

To connect with the database, let’s use the global object. Syntax to use – global $wpdb;

// Basic Find/Search Methods
function myFunction() {
 
  global $wpdb;
 
  $wpdb->get_var("Write Query");
  $wpdb->get_row("Write Query", Format_parameter);
  $wpdb->get_col("Write Query");
  $wpdb->get_results("Write Query", Format_parameter);
 
}

Here, we called some of the methods of wordpress by the help of $wpdb object. So, this same object will be used to do all operations like insert, update & delete methods.

Insert data into WordPress Database Table – $wpdb->insert()

Performing an insert operation, we can use insert method of wpdb class. Inserting data into wordpress is not as simple as insert query of others like “INSERT INTO table_name (cols) VALUES (vals)“. We use insert method.

global $wpdb;

$wpdb->insert( $table, $data, $format );

$wpdb->insert() method accepts three arguments. First value will be the name of the table in which we are inserting the data. Second argument is an array which contains pair of columns and their respective values. The third value is optional which specifies the type of data of values.

global $wpdb;

$wpdb->insert($wpdb->prefix."posts", array(
   	"post_title" => "Sample Post", 
    "post_content" => "This is sample content of this post",
    "post_name" => "sample-post",
    "post_status" => "publish"
),array(
   "%s",
   "%s",
   "%s",
   "%s"
));

// EQUIVALENT GENERATED QUERY
// INSERT INTO wp_posts ("post_title", "post_content", "post_name", "post_status") 
// VALUES ("Sample Post", "This is sample content of this post", "sample-post", "publish");

Here, $wpdb->prefix returns wp_. So the tables used as wp_posts ast the first parameter, second value is key values pairs of columns and values. Third is optional all about the value we passed as it’s data type.

Data Types Placeholders

1. %d – Integer Value
2. %s – String Value
3. %f – Float Value etc.

Modified Query when no optional values

global $wpdb;

// We can use this insert method too to insert data into table
$wpdb->insert($wpdb->prefix."posts", array(
   	"post_title" => "Sample Post", 
    "post_content" => "This is sample content of this post",
    "post_name" => "sample-post",
    "post_status" => "publish"
));

INSERT_ID()

Whenever we insert any row into a table, we will have an auto-incrementing ID in return of the result. To get the auto incremented ID value of the most recent inserted row, simply we can use $wpdb->insert_id

Let’s write query with return ID

global $wpdb;

$row_id = $wpdb->insert($wpdb->prefix."posts", array(
   	"post_title" => "Sample Post", 
    "post_content" => "This is sample content of this post",
    "post_name" => "sample-post",
    "post_status" => "publish"
));

echo $wpdb->insert_id; // It prints the return row ID after insertion.

Alternative method to insert data

The method what we have discussed above is safe, best and easy to implement. But if we are looking for any alternative method to insert data into table we have a prepare() method which we can call by $wpdb object.

Using prepare method we can also protect our query from SQL injection based queries. This is also safe but not be the best in comparison of this first.

global $wpdb;

$wpdb->prepare("INSERT INTO table_name (cols) VALUES (values)");

global $wpdb;

$query = $wpdb->prepare('INSERT INTO wp_posts ("post_title", "post_content", "post_name", "post_status") VALUES ("Sample Post", "This is sample content of this post", "sample-post", "publish")');
$data = $wpdb->query($query);

echo $wpdb->insert_id;

So, basically we have two ways to insert data into wordpress. If we search any other options apart from these hopefully we can search and implement.

Update Method of global $wpdb Object – $wpdb->update()

Update method is one of the available methods by wordpress global object $wpdb. Rest other methods I have mentioned at the top of this article.

Syntax to use

global $wpdb;

$wpdb->update($table, $data, $where, $format = null, $where_format = null);

Syntax is very much similar to insert method of $wpdb which already been discussed. Let’s understand about the parameters of update() method.

Parameters Explanation

• $table – This specifies the table name, table where we want to execute update query.
• $data – Updated data we want to be reflected after running query.
• $where – We need to pass conditional parameters On the basis of this we will update data into table.
• $format – All about placeholders like what we had already discussed in previous article. It simply indicates about value types. %s for string, %d for integer, %f for float etc. It should be column value pairs.
• $where_format – Same as $format parameter, additionally we need to pass the where conditions.

global $wpdb;

$wpdb->update( 
  $wpdb->prefix."posts", 
  array(
    "post_title" => "Updated Post Title", 
    "post_content" => "This is sample content update of this post", 
    "post_name" => "my-updated-post", 
    "post_excerpt" => "Sample content update"
  ), 
  array("ID" => 4), 
  array("%s", "%s", "%s","%s"), 
  array("%d") 
);

Here, we have used posts table. $wpdb->prefix returns wp_.

$table – wp_posts, $data – we have the set of updated values with column names and it’s values. $where – ID = 4, $data_format – placeholder formats we have passed, $where_format – where condition is an integer value so %d is the format for that.

Alternative Method to Update Data

Yes, we can use prepare method to update data in wordpress database table. But I recommend to use the first method.

global $wpdb;

$wpdb->prepare("UPDATE table_name SET COLS = VALS WHERE CONDITIONS");

global $wpdb;

$sql = 'UPDATE wp_posts SET post_title = "Updated Post title", post_content = "Updated Sample content", post_name = "updated-post-name" WHERE ID = 4';

$prepared_query = $wpdb->preapre($query);

$data = $wpdb->query($prepared_query);

Using prepared statement, it provided query to run with sql injection security. It is also safe to run and execute. But using $wpdb->update() is quite simple, easy and handy in comparison of using by $wpdb->prepare().

Update Method of global $wpdb Object – $wpdb->delete()

Delete method is used to delete data from table. It also has the simple syntax like as we have for insert and update.

Syntax to use

global $wpdb;

$wpdb->delete(string $table, array $where, array|string $where_format = null)

Parameters Explanation

• $table – This specifies the table name, table where we want to execute delete query.
• $where – We need to pass conditional parameters On the basis of this we will delete data from table.
• $where_format – Same as $format parameter, additionally we need to pass the where conditions. It simply indicates about value types. %s for string, %d for integer, %f for float etc.

global $wpdb;

$wpdb->delete(
  $wpdb->prefix."posts",
  array("ID" => 4),
  array("%d")
);

Inside above code, ID = 4 will be deleted from wp_posts table.

Alternate way to Delete Data

We can use the prepare method to delete in alternative way. You can see this is simple delete command we have written and trying to delete.

global $wpdb
  
$query = "DELETE FROM wp_posts WHERE ID = 4";

$prepared_query = $wpdb->preapre($query);

$data = $wpdb->query($prepared_query);

We hope this article helped you to learn about WordPress CRUD Tutorial using $wpdb object in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.