How to Retrieve Data from Multiple Tables using wpdb in WordPress Plugin

Posted on 21st June 2023

When developing a WordPress plugin, you may need to retrieve data from multiple tables. This can be done using the wpdb class.

In this article, we will show you how to retrieve data from multiple tables using wpdb in a WordPress plugin.

We will assume that you have a plugin with the following structure:

/my-plugin/

my-plugin.php

/includes/

my-plugin-functions.php

The first thing you need to do is include the my-plugin-functions.php file in your my-plugin.php file.

Next, you need to create a function that will retrieve the data from the database.

In this function, you need to use the $wpdb->get_results() method. This method takes two arguments: the SQL query and the output type.

The SQL query is the query that you want to run on the database. The output type is the type of data that you want to receive from the database.

In our example, we want to retrieve data from the posts and postmeta tables. We also want to receive an associative array as the output type.

Here is the code for the function:

function my_plugin_get_data() {

global $wpdb;

$sql = “SELECT p.ID, p.post_title, p.post_content, m.meta_value

FROM $wpdb->posts AS p

LEFT JOIN $wpdb->postmeta AS m ON p.ID = m.post_id

WHERE p.post_type = ‘my_plugin’

AND m.meta_key = ‘my_plugin_meta_key'”;

$data = $wpdb->get_results( $sql, ‘ARRAY_A’ );

return $data;

}

In the code above, we are using the LEFT JOIN SQL keyword to join the posts and postmeta tables.

We are also using the ‘my_plugin’ post type and the ‘my_plugin_meta_key’ meta key to retrieve the data from the database.

You can learn more about the LEFT JOIN SQL keyword in the WordPress Codex.

Once you have created the function, you can call it in your my-plugin.php file.

Here is an example of how you would call the function:

$data = my_plugin_get_data();

foreach ( $data as $row ) {

echo $row[‘ID’] . ‘ – ‘ . $row[‘post_title’] . ‘
‘;

echo $row[‘post_content’] . ‘
‘;

echo $row[‘meta_value’] . ‘
‘;

}

In the code above, we are looping through the $data array and printing the ID, title, content, and meta value for each row.

You can also use the $wpdb->get_row() method to retrieve a single row from the database.

This method takes two arguments: the SQL query and the output type.

The SQL query is the query that you want to run on the database. The output type is the type of data that you want to receive from the database.

In our example, we want to retrieve a single row from the posts table. We also want to receive an associative array as the output type.

Here is the code for the function:

function my_plugin_get_data() {

global $wpdb;

$sql = “SELECT p.ID, p.post_title, p.post_content

FROM $wpdb->posts AS p

WHERE p.post_type = ‘my_plugin’

AND p.ID = 1”;

$data = $wpdb->get_row( $sql, ‘ARRAY_A’ );

return $data;

}

In the code above, we are using the ‘my_plugin’ post type and the ID 1 to retrieve the data from the database.

You can learn more about the $wpdb->get_row() method in the WordPress Codex.

Once you have created the function, you can call it in your my-plugin.php file.

Here is an example of how you would call the function:

$data = my_plugin_get_data();

echo $data[‘ID’] . ‘ – ‘ . $data[‘post_title’] . ‘
‘;

echo $data[‘post_content’] . ‘
‘;

In the code above, we are printing the ID, title, and content for the row.

You can also use the $wpdb->get_var() method to retrieve a single value from the database.

This method takes two arguments: the SQL query and the column number.

The SQL query is the query that you want to run on the database. The column number is the number of the column that you want to retrieve the data from.

In our example, we want to retrieve the post_title column from the posts table.

Here is the code for the function:

function my_plugin_get_data() {

global $wpdb;

$sql = “SELECT p.post_title

FROM $wpdb->posts AS p

WHERE p.post_type = ‘my_plugin’

AND p.ID = 1”;

$data = $wpdb->get_var( $sql, 1 );

return $data;

}

In the code above, we are using the ‘my_plugin’ post type and the ID 1 to retrieve the data from the database.

We are also specifying the column number as 1. This is because the post_title column is the first column in the posts table.

You can learn more about the $wpdb->get_var() method in the WordPress Codex.

Once you have created the function, you can call it in your my-plugin.php file.

Here is an example of how you would call the function:

$data = my_plugin_get_data();

echo $data;

In the code above, we are printing the post title for the row.

You can also use the $wpdb->get_col() method to retrieve a single column from the database.

This method takes two arguments: the SQL query and the column number.

The SQL query is the query that you want to run on the database. The column number is the number of the column that you want to retrieve the data from.

In our example, we want to retrieve the post_title column from the posts table.

Here is the code for the function:

function my_plugin_get_data() {

global $wpdb;

$sql = “SELECT p.post_title

FROM $wpdb->posts AS p

WHERE p.post_type = ‘my_plugin'”;

$data = $wpdb->get_col( $sql, 1 );

return $data;

}

In the code above, we are using the ‘my_plugin’ post type to retrieve the data from the database.

We are also specifying the column number as 1. This is because the post_title column is the first column in the posts table.

You can learn more about the $wpdb->get_col() method in the WordPress Codex.

Once you have created the function, you can call it in your my-plugin.php file.

Here is an example of how you would call the function:

$data = my_plugin_get_data();

foreach ( $data as $title ) {

echo $title . ‘
‘;

}

In the code above, we are looping through the $data array and printing the post title for each row.