php - Custom route and query

I am trying to use a custom WordPress route and function, but I am getting a route and function not found error. Below is my code but I am not sure where it is breaking:

add_action('rest_api_init', function () {
 register_rest_route( 'boss/v1/latest-quiz-results',array(
            'methods'  => 'GET',
            'callback' => 'get_latest_quiz_results'
  ));
});
function get_latest_quiz_results() {
 $query = $wpdb->prepare(
 "SELECT post_id, 
   5PxenC_learndash_user_activity_meta.activity_meta_key, 
   5PxenC_learndash_user_activity_meta.activity_meta_value, 
   5PxenC_learndash_user_activity.activity_id, user_id, course_id
  FROM 5PxenC_learndash_user_activity
 INNER JOIN 5PxenC_learndash_user_activity_meta ON 5PxenC_learndash_user_activity_meta.activity_id = 5PxenC_learndash_user_activity.activity_id
  WHERE 5PxenC_learndash_user_activity_meta.activity_meta_key = 'pass'");
$results = $wpdb->get_results($query);
if (empty($results)) {
return new WP_Error( 'empty_category', 'there is no quiz data', 
array('status' => 404) );

}

$response = new WP_REST_Response($results);
$response->set_status(200);

 return $results;
 }

I am trying to use a custom WordPress route and function, but I am getting a route and function not found error. Below is my code but I am not sure where it is breaking:

add_action('rest_api_init', function () {
 register_rest_route( 'boss/v1/latest-quiz-results',array(
            'methods'  => 'GET',
            'callback' => 'get_latest_quiz_results'
  ));
});
function get_latest_quiz_results() {
 $query = $wpdb->prepare(
 "SELECT post_id, 
   5PxenC_learndash_user_activity_meta.activity_meta_key, 
   5PxenC_learndash_user_activity_meta.activity_meta_value, 
   5PxenC_learndash_user_activity.activity_id, user_id, course_id
  FROM 5PxenC_learndash_user_activity
 INNER JOIN 5PxenC_learndash_user_activity_meta ON 5PxenC_learndash_user_activity_meta.activity_id = 5PxenC_learndash_user_activity.activity_id
  WHERE 5PxenC_learndash_user_activity_meta.activity_meta_key = 'pass'");
$results = $wpdb->get_results($query);
if (empty($results)) {
return new WP_Error( 'empty_category', 'there is no quiz data', 
array('status' => 404) );

}

$response = new WP_REST_Response($results);
$response->set_status(200);

 return $results;
 }

• php
• wp-query
• mysql
• rest-api
• endpoints

Share Improve this question edited Jan 22, 2019 at 23:21 Joel Scalera asked Jan 22, 2019 at 22:39 Joel ScaleraJoel Scalera 344 bronze badges 3

• Is that latest-quiz-results/) (the route) just a typo in the question? – Sally CJ Commented Jan 22, 2019 at 22:52
• @SallyCJ sorry, typo issue. now fixed – Joel Scalera Commented Jan 22, 2019 at 23:22
• Check my answer. I hope it helps you. – Sally CJ Commented Jan 23, 2019 at 0:29

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

There are 3 issues with your code:

1. The proper syntax of registering your custom REST API route, is as follows:

   register_rest_route( $namespace, $base_URL, $args, $override );
   

   So:

   register_rest_route( 'boss/v1', '/latest-quiz-results', array(
       'methods'  => 'GET',
       'callback' => 'get_latest_quiz_results'
   ) );
   

   Consult the reference for more information.

2. In the get_latest_quiz_results(), $wpdb is not defined because you're missing global $wpdb; at the top:

   function get_latest_quiz_results() {
       global $wpdb; // Add this.
       ...
   
       return $results;
   }
   

3. Also in get_latest_quiz_results(), you incorrectly called wpdb::prepare() — the second parameter is mandatory, and there needs to be at least one placeholder (e.g. %s). So for example:

   $query = $wpdb->prepare( "SELECT ... WHERE 5PxenC_learndash_user_activity_meta.activity_meta_key = %s", 'pass' );