Query Database Using Wordpress REST - caskiSPACE

This code crumb will query a WordPress database using WP REST. It tackles all the basic steps to interact and get data from the WordPress database in three different ways; directly from the browser using a URL, calling from a PHP function or by a user-driven event from the front-end interface using Javascript which will then call a PHP function which in turn will query the database and return the result.

Step 1. Register an Endpoint

Register an Endpoint

Create a function which will contain the route registration.
Define the endpoint’s route using ‘register_rest_route’.
Add the function to the ‘rest_api_init’ hook

function register_route() {

   register_rest_route('get-user/v1', '/first/(?P<first>.+)/last/(?P<last>.+)', [
      'methods' => 'GET',
      'callback' => 'get_user_callback',
      'permission_callback' => '__return_true',
   ]);

}

add_action('rest_api_init', 'register_route');

Step 2. Create a Callback Function to Query the Database

Step 2. Create the Callback Function that Will Query the Database

Create the callback function for the endpoint
Get reference to the WordPress Database Access object (wpdb).
Get the parameters passed to the endpoint.
Prepare the database query using wpdb->prepare. Use ‘%s’ for string, ‘%d’ for integer and ‘%f’ for float values.
Query the database and get results. Use wpdb->get_results for SELECT statements and wpdb->query for DELETE/UPDATE.
Return the data in a WP_REST_Response object.

function get_user_callback(WP_REST_Request $request) {

   global $wpdb;

   $first_name = urldecode($request->get_param( 'first' ));
   $last_name = urldecode($request->get_param( 'last' ));

   $query = "SELECT * FROM 'users_table' WHERE 'first_name' = %s AND 'last_name' = %s";
   $prepared_query = $wpdb->prepare($query, $first_name, $last_name);

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

   return new WP_REST_Response($data);

}

Step 3.a Call Directly From a Browser using a URL

Call Call Directly From a Browser using a URL

Call directly from a browser by prefixing the Route with /wp-json/.

With parameters: (first name = ‘Gieon Felice’ and last name = ‘Aborot’)

https://yourdomain.com/wp-json/get-user/v1/first/Gieon Felice/last/Aborot

Step 3.b Call the Endpoint Using PHP

Call the Endpoint From PHP

Use wp_remote_get to request data from the endpoint.
Handle errors and unsuccessful requests using is_wp_error and WP_Error.
Finally use wp_remote_retrieve_body to get the response.

function call_from_php() {

   $response = wp_remote_get(
      "https://yourdomain.com/wp-json/get-user/v1/first/Gieon Felice/last/Aborot", [
         'sslverify' => false,
      ]
   );

   if ( is_wp_error( $response ) ) {
      return new WP_Error( 'call_from_php unsuccessful', $response->get_error_message() );
   }

   return wp_remote_retrieve_body($response);

}

Step 3.c Trigger the Call to the Endpoint From the Front-end Using Javascript

Trigger the Call to the Endpoint From the Front-end Using Javascript

In PHP, declare the ajax_url object using wp_localize_script. This is needed to call the PHP function from JS using the AJAX request.
Add wordpress hooks to make a connection between the AJAX request action and the name of php function to call. ‘wp_ajax_nopriv’ fires ONLY if the user is not logged-in while ‘wp_ajax’ fires ONLY if the user is logged-in.
Create a PHP function which will be called from Javascript and in turn will call the Endpoint.
Finally in Javascript, create the function that will make an AJAX request to call the PHP function.

wp_localize_script(
   'id_on_the_enqueued_script_that_will_use_it',
   'my_ajax_object',
   [ 'ajax_url' => admin_url( 'admin-ajax.php' ) ]
);

add_action('wp_ajax_nopriv_call_from_js_to_php', 'call_from_php');
add_action('wp_ajax_call_from_js_to_php', 'call_from_php');

function call_from_php() {

   $first_name = $_GET['first_name'];
   $last_name = $_GET['last_name'];

   $response = wp_remote_get(
      "https://yourdomain.com/wp-json/get-user/v1/first/$first_name/last/$last_name", [
         'sslverify' => false,
      ]
   );

   if ( is_wp_error( $response ) ) {
      return new WP_Error( 'call_from_php unsuccessful', $response->get_error_message() );
   }

   wp_send_json(wp_remote_retrieve_body($response), 200);
}

function call_from_js() {
   jQuery.ajax(my_ajax_object.ajax_url, {
      type: 'GET',
      data: {
            action: 'call_from_js_to_php',
            first_name: 'Gieon Felice',
            last_name: 'Aborot',
      },
   }).done(response => {
      const parsedResults = JSON.parse(response);
      // Do something with the result
   }).fail(error => {
      // Do something when an unsuccessful
   });
}