Posts

insert data into WordPress database

How to insert data into WordPress database

To insert data into WordPress database, we have learned that you need to connect the database. If you don’t know how you can refer to the previous topic entitled How to connect to WordPress database.

After a successful connection to the database, we should start by populating it with data. We will once again use the $wpdb object to make modifications in the database.

In the previous topic, we left at having the following code:

function myFunction() {     
  global $wpdb; 
} 

The code above is needed to make a connection to the database.

Using the $wpdb->insert()

WordPress has another way of inserting into the database. This is not your usual using of INSERT INTO statement.

The basic syntax for inserting data to WordPress database is <?php $wpdb->insert($table_name, $data); ?>. The $table_name is a string that is the name of the database table to insert data into. On the other hand, $data is an array that will be inserted into the database table.

Now we have learned the basic syntax for the WordPress insert, we should have a code like this:

function myFunction() {     
  global $wpdb;     
  $table_name = $wpdb->prefix . 'my_table';     

  $wpdb->insert($table_name, array('column_1' => $data_1, 'column_2' => $data_2, //other columns and data (if available) ...)); 
} 

For example, I have a table named wp_myExampleTable that looks like this:

insert data to WordPress database
Figure 1. Newly created table named “wp_myExampleTable“.

content_id is the primary key which has a type of integer with auto increment.
content_message is a varchar type that has a length of 200.
content_date has a type of datetime.

I will try to insert some data into wp_myExampleTable using the code below:

function myFunction() {     
  global $wpdb;     
  $table_name = $wpdb->prefix . 'myExampleTable';     
  $message = 'Insert to database tutorial.';     
  $date = date('Y-m-d H:i:s');     
  $wpdb->insert($table_name, array('content_id' => NULL, 'content_message' => $message, 'content_date' => $date)); 
} 

If the code runs without any error then the data is successfully inserted into the database. The table wp_myExampleTableshould now look like this:

insert data to WordPress database
Figure 2. wp_myExampleTable after running the script.

Assuming that we run this script at exactly December 19, 2017 at 1:00 in the afternoon.

Using the $wpdb->query()

I personally use this method to insert since I am familiar with SQL queries. This method can be used in executing any SQL queries. So if you are familiar or more comfortable in using SQL queries then this would fit you more.

The basic syntax for this method would be <?php  $wpdb->query($query); ?>. $query is a string variable that holds the SQL query to execute. For this example, we would be using the INSERT INTO statement.

If we are going to insert another data in the wp_myExampleTable table then we should run the code below:

function mySecondFunction() {     
  global $wpdb;     
  $table_name = $wpdb->prefix . 'myExampleTable';     
  $message = 'Another method of insert.';     
  $date = date('Y-m-d H:i:s');     
  $wpdb->query("INSERT INTO $table_name(content_id, content_message, content_date) VALUES(NULL, '$message', '$date')"); 
} 
insert data to WordPress database
Figure 3. wp_myExampleTable after using the other method.

The database table should look like this if we successfully inserted the new data.

Assuming that we run the script at exactly after five (5) minutes.

That’s it. I hope you learned something.

connect to WordPress database

How to connect to WordPress database

Connect to WordPress database using PHP and MySQL. In PHP you can connect and modify databases in which MySQL is the most popular database system for this.

Database tables holds the data found in your website. Tables are collection of related data that consists of columns and rows. For you to be able to modify these data, you need to connect to the database. There are many ways to connect to the database and WordPress made it easier.

THE WPDB CLASS & WPDB OBJECT

Unlike in writing PHP code from scratch where you need to properly configure your database connection, WordPress provides a class called wpdb that is created upon successful installation of WordPress. This class is defined in www.yourdomain.com/wp-includes/wp-db.php.

Its primary purpose is to provide all functions needed to communicate with WordPress database.

You don’t use the wpdb class to your codes instead WordPress provides an object called $wpdb that came from wpdb class. Making a connection to the database was the primary purpose of this object.

Now, to make a connection to the database in your WordPress PHP code, you need to declare $wpdb as a global variable by using the global keyword.

You will now have a PHP code like this:

function myFunction() {     
  global $wpdb; 
} 

That’s it! You successfully connected to the WordPress database. From this point, you should be able to use all database functions of WordPress.

Database tables generated by WordPress is not the only tables that $wpdb can modify. It also includes the other tables generated by other means such as installing custom plugins and creating some yourselves manually.

random string

How to generate a random string in PHP

In this tutorial, we would be dealing with str_repeat, str_shuffle, and substr to generate a random string in PHP.

Random strings can be used in generating things that use random strings such as tokens, initial password, and some other things that need to be hard to guess.

Step 1: Declare the string where you will get your generated random string.

function randomString() {     
  $my_string = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHILJKLMNOPQRSTUVWXYZ0123456789';
  $my_random_string = $my_string;     
  return $my_random_string; 
} 

I declared the numbers 0-9 twice to compensate for having both upper and lowercase of letters. We don’t want our generated strings to be filled with letters right?

This function will simply return abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHILJKLMNOPQRSTUVWXYZ0123456789 on the screen which is composed of 73 characters.

Step 2: Use str_repeat to duplicate your initially declared string.

For this example, we used $my_string as our declared string.

function randomString() {     
  $my_string = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHILJKLMNOPQRSTUVWXYZ0123456789';     
  $my_random_string = str_repeat($my_string,10);     
  return $my_random_string; 
} 

This function will now return 10 times the length of the returned value from Step 1 which is composed of 730 characters.

Step 3: Use str_shuffle to randomize the position of every character present in $myString.

function randomString() {     
  $my_string = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHILJKLMNOPQRSTUVWXYZ0123456789';     
  $my_random_string = str_repeat($my_string,10);     
  $my_random_string = str_shuffle($my_random_string);     
  return $my_random_string; 
} 

The purpose of using str_repeat is to add repetitions among the characters. str_repeat together with str_shuffle to have 2 or more of the same characters in a string.

An example is having “aaa” or “abcddd”. If I didn’t use the str_repeat we can only generate a unique set of characters such as “jkl” but never “jjl”.

This function will return a shuffled version of the output in Step 2 and still composed of 730 characters.

Step 4: Use substr to print only the first n characters of the string where n is your desired number of random characters to be generated.

In this example, we would be generating 10 random characters.

function randomString() {     
  $my_string = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHILJKLMNOPQRSTUVWXYZ0123456789';     
  $my_random_string = str_repeat($my_string,10);     
  $my_random_string = str_shuffle($my_random_string);     
  $n = 10;     
  $my_random_string = substr($my_random_string,0,$n);     
  return $my_random_string; 
} 

This function will return a 10 characters random string. In Step 2, we generated a shuffled version of the 730 characters, but we don’t want to have it long like that right? So we used substr to limit the length of our output.

You can now copy this function as working code, but if you wish to have an additional feature such as no duplicates, then proceed to Step 5.

Step 5: Use recursion to check for duplicates. This will not allow our function to return a duplicated string.

function randomString() {     
  $my_string = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHILJKLMNOPQRSTUVWXYZ0123456789';     
  $my_random_string = str_repeat($my_string,10);     
  $my_random_string = str_shuffle($my_random_string);     
  $n = 10;     
  $my_random_string = substr($my_random_string,0,$n);     

  if($my_random_string has duplicates) {   
    /**
      This is not a valid condition, create your own condition to check for duplicates.
      You can use in_array() function to check for duplicates in a set of array or some other conditions before returning the string.
    */         
    randomString();     
  }
  else {         
    return $my_random_string;    
  } 
} 

This function will have the same output as we have in Step 4 but the upside of this function is that it will not return a duplicated string. Instead, the output will only be generated once and never again but you need the right condition for it to work.

Basic usage:

 echo randomString(); //This will return a 10 characters random string. 

That’s it. We have created a random string generator using PHP.