How to Access custom database content with AJAX onClick refresh of div inside member-only WordPress page?

Question

The use case is providing the user with 20 different buttons on a single login protected page, each button is a category from a custom database table and the content is shown in a textarea element on a button onClick event. It is like a random quotes button on a page that refreshes a div without reloading the page. Sort of Hello Dolly plugin with an AJAX button.

I have coded a basic PHP script (main.php) that gets a random row from a MySQL database using an ajax call to a separate PHP page (getData.php). Database credentials are hardcoded in the getData page. This works OK.

Now I want to put this functionality inside a membership access-only page on a WordPress site. However, I'm finding it very difficult to know what is the best practise approach to this. I have read many different approaches to the problem from building my own plugin, putting database credentials and SQL in the functions.php file of my theme (clearly a bad idea), shortcodes, etc. I am very confused.

I want to use the WordPress framework and have next to no knowledge of enqueing and nonce. And not sure how to get (if I can) data from a custom table in the wp_ database.

I think I have broken the problem into these steps/questions:

1: How to access custom database table row content using WordPress framework

2: How to add a javascript/AJAX function to a button on a protected page in WordPress

3: How to refresh a div/element on a protected page via AJAX

4: Where to put the PHP function that connects 1, 2 and 3

5: How do I ensure a bad actor can't just access these functions directly and hammer my database and steal my content

I'm planning to use the WP Members plugin to restrict access to the page and am using the Understrap theme.

Let me know if I can provide further information. Help is much appreciated.

Answer 1

I'd use the default WP AJAX API so WP can handle all the authentication for you.

You'd add a function and an action to trigger it:

function wpse_314311_get_quote() {
    // get quote from db
    // print json to client
    exit;
}
add_action( 'wp_ajax_get_quote', 'wpse_314311_get_quote' );

That way, WP will make sure that only logged in users are allowed to access the API. If you wanted to allow anonymous users as well, you'd add

add_action( 'wp_ajax_nopriv_get_quote', 'wpse_314311_get_quote' );

On the server side, you'll use WPDB to access your custom tables inside the WP database:

global $wpdb;
if($quote = $wpdb->get_row("SELECT * FROM my_quotes_table ORDER BY RAND() LIMIT 0, 1")) {
    print $quote->quote_body . " by " . $quote->quote_author;
}

Together this might look something like

function wpse_314311_get_quote() {
    global $wpdb;
    if($quote = $wpdb->get_row("SELECT * FROM my_quotes_table ORDER BY RAND() LIMIT 0, 1")) {
        print json_encode(array("success" => true, "message" => $quote->quote_body));
    }
    else {
        print json_encode(array("success" => false, "message" => "no quote found"));
    }
    // print json to client
    exit;
}
add_action( 'wp_ajax_get_quote', 'wpse_314311_get_quote' );

In the front end, you can then access it by passing action=get_quote to the API (usually /wp-admin/admin-ajax.php, but you might want to look into putting it into a variable with wp_localize_script):

jQuery(document).ready(function($) {
    $("#myButton").click( function() {
        jQuery.post("/wp-admin/admin-ajax.php", { "action": "get_quote"}, function(response) {
            console.log(response);
            $("#myDiv").text(response.message);
        });
    }
});

Put that in a script (for this example, I'm assuming inside your theme in a js folder in a file named quote-refresh.js and make WP load it with wp_enqueue_script. Be sure to let WP know your script requires jQuery to be loaded, e.g.

wp_enqueue_script( "quote-refresh", get_template_directory_uri() . "/js/quote-refresh.js", array("jquery") );

You can put everything in your theme's function.php assuming you're using a child theme and not Understrap directly (which you should if you ever want to customize anything in the theme and still be able to update the original theme), or create a very simple plugin to add it to WP.

As for controlling access: that's hard depending on your paranoia. Protecting unauthorized access is easy, WP will make sure the requests are coming from logged in users. Making sure those users don't use automation to suck out everything in your DB is much harder - you'd have to decide which pattern of behavior is a bored human hitting "refresh" every few seconds to read something else and which is a bot that just wants the content.

Please excuse typos and bugs in code, I don't have any means to run it right now.