How to insert data to WordPress database using wpdb

Introduction

WordPress allows us to easily insert data into the database using the built-in wpdb class.

This PHP class is used in WP to instantiate the global $wpdb object.

With the right PHP code we can insert the desired data to any database table.

wpdb insert to database

PHP code to insert data to database

In order to show you how we can easily add info to the database, I will use an example which you will be able to adapt to your needs.

This sample code will insert a transaction data into a transactions table which belongs to a WP Affiliate plugin.

Why is this needed or the info which it is inserted it is not important. What it matters is that you will be able to see how a date is inserted correctly but also a float number or a string.

First of all, whenever you use $wpdb, you need to add global $wpdb;.

Here's the PHP code which you can use:

global $wpdb;
$date= current_time('mysql');
$referenceId = uniqid();
$table = $wpdb->prefix.'wpam_transactions';
$data = array('dateCreated' => $date, 'affiliateId' => 10, 'amount' => 25, 'status' => 'confirmed', 'referenceId' => $referenceId, 'email' => $affiliate_email );
$format = array( '%s', '%d', '%f', '%s', '%s', '%s');
$wpdb->insert($table,$data,$format);

I find the above method to be the easiest way to insert data to a WordPress database using $wpdb->insert.

First of all, I have included $wpdb global object and after that, since it was needed to insert current date into database, I have added current MySQL time to a variable (this line shows you how current time, in the format requested by MySQL, can be added to a database):

global $wpdb;
$date= current_time('mysql');

When you want to generate a unique ID, you can use uniqid().

After that I have created a variable which will contain the database table where data will be inserted. Please note that I have used $wpdb->prefix. This is useful because when you will change WP database prefix, this will always have the right value.

$table = $wpdb->prefix.'wpam_transactions';

Then an array with the data which needs to be inserted:

$data = array('dateCreated' => $date, 'affiliateId' => 10, 'amount' => 25, 'status' => 'confirmed', 'referenceId' => $referenceId, 'email' => $affiliate_email );

And an array with the formats of the data from $data array:

$format = array( '%s', '%d', '%f', '%s', '%s', '%s');

The final step is to use the insert method:

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

At this point, if all is coded right, then the data is inserted to the db.

How to fix wpdb insert

Even if all looks right and the above method is used, you might have the unpleasant surprise that $wpdb->insert is not working.

Here's the things which might not be right in your code:

  1. global $wpdb is missing. This means that the PHP code will actually not recognize the $wpdb object and the whole code is useless.
  2. You table name it not correct. In this case, if the table name is wrong, the code will never add anything data to the database.
  3. Data and Format are not right. If you have 5 elements in the $data array and just 4 elements in the $format array, then this could prevent the code to work.

Conclusion

The above method to insert data to a WP database table is the best and the easiest one to use, in my opinion. It gives you total control over the inserted data and it is easy to troubleshoot.

Comments closed

Please contact me, if you have any questions or suggestions.