How to Retrieve Data from External Databases using wpdb in WordPress Plugin
Posted on 20th June 2023
The WordPress wpdb class is used to interact with external databases. In this article, we will show you how to use wpdb to retrieve data from external databases in your WordPress plugin.
What is wpdb?
Wpdb is a WordPress class that is used to interact with database. It is located in the wp-includes folder and you can use it to run database queries.
How to Use wpdb to Retrieve Data from External Databases?
In order to use wpdb to retrieve data from external databases, you first need to connect to the database. You can do this by using the wpdb::connect() method.
Once you have connected to the database, you can run queries using the wpdb::query() method.
The following example shows how to use wpdb to retrieve data from an external database.
query(“SELECT * FROM table_name”);
// Print the results
print_r($results);
?>
How to Use wpdb to Retrieve Data from External Databases in Your WordPress Plugin?
In order to use wpdb to retrieve data from external databases in your WordPress plugin, you need to follow these steps:
1. Create a Plugin
First, you need to create a WordPress plugin. You can do this by creating a new file and name it wp-external-db.php.
2. Connect to the Database
Next, you need to connect to the database. You can do this by using the wpdb::connect() method.
3. Run a Query
Once you have connected to the database, you can run queries using the wpdb::query() method.
The following example shows how to use wpdb to retrieve data from an external database.
query(“SELECT * FROM table_name”);
?>
4. Print the Results
Finally, you need to print the results. You can do this by using the print_r() function.
query(“SELECT * FROM table_name”);
// Print the results
print_r($results);
?>
That’s all. You have successfully retrieved data from external databases in your WordPress plugin.
Before you can use the wpdb class to interact with a database, you need to set up a connection to the database. This is done by instantiating the wpdb class with the following parameters:
The database server hostname or IP address.
The database username.
The database password.
The database name.
For example:
$wpdb = new wpdb(‘localhost’, ‘username’, ‘password’, ‘database’);
Once you have a database connection, you can run queries against it. The wpdb class has a query() method for this purpose. The query() method takes a SQL query string as its only parameter. For example, the following code selects all posts from the WordPress database:
$posts = $wpdb->query(“SELECT * FROM wp_posts”);
The query() method returns a result object on success or false on failure. The result object can be used to fetch the data returned by the query. For example, the following code fetches the title and content of all posts:
foreach ($posts as $post) {
echo $post->post_title . “n”;
echo $post->post_content . “n”;
}
The wpdb class also provides a get_results() method for fetching data from the database. The get_results() method takes a SQL query string as its first parameter and an optional data type as its second parameter. The data type parameter can be one of the following values:
OBJECT – Return an associative array of objects (default).
OBJECT_K – Return an associative array of objects with the first column as the key.
ARRAY_A – Return an associative array of arrays.
ARRAY_N – Return an indexed array of arrays.
For example, the following code fetches the title and content of all posts as an associative array of objects:
$posts = $wpdb->get_results(“SELECT * FROM wp_posts”, OBJECT);
foreach ($posts as $post) {
echo $post->post_title . “n”;
echo $post->post_content . “n”;
}
The wpdb class also provides a get_var() method for fetching a single value from the database. The get_var() method takes a SQL query string as its only parameter. For example, the following code fetches the title of the first post in the WordPress database:
$title = $wpdb->get_var(“SELECT post_title FROM wp_posts LIMIT 1”);
echo $title;
The wpdb class also provides an insert() method for inserting data into the database. The insert() method takes an associative array of data as its first parameter and the table name as its second parameter. The following code inserts a new post into the WordPress database:
$data = array(
‘post_title’ => ‘My New Post’,
‘post_content’ => ‘This is my new post.’,
‘post_status’ => ‘publish’
);
$wpdb->insert(‘wp_posts’, $data);
The wpdb class also provides an update() method for updating data in the database. The update() method takes an associative array of data as its first parameter, the table name as its second parameter, and an array of where conditions as its third parameter. The following code updates the post with ID 1 in the WordPress database:
$data = array(
‘post_title’ => ‘My Updated Post’,
‘post_content’ => ‘This is my updated post.’
);
$where = array(
‘ID’ => 1
);
$wpdb->update(‘wp_posts’, $data, $where);
The wpdb class also provides a delete() method for deleting data from the database. The delete() method takes the table name as its first parameter and an array of where conditions as its second parameter. The following code deletes the post with ID 1 from the WordPress database:
$where = array(
‘ID’ => 1
);
$wpdb->delete(‘wp_posts’, $where);
The wpdb class also provides a prepare() method for preparing SQL queries. The prepare() method takes a SQL query string and an optional array of data as its parameters. The data array can be used to substitute placeholders in the SQL query string. The following code prepares a SQL query for selecting posts by ID:
$sql = $wpdb->prepare(“SELECT * FROM wp_posts WHERE ID = %d”, 1);
$posts = $wpdb->get_results($sql);
foreach ($posts as $post) {
echo $post->post_title . “n”;
echo $post->post_content . “n”;
}
