How can I return data from MYSQL as JSON using PHP so that I can use it in on my HTML page-CodePudding

I am trying to get data from the mysql database using php and wordpress which I want to convert to json to get all the data from my mysql query. I have made the call using AJAX to the php file that runs the mysql but I don't know how to convert the sql query to json and return it on my HTML page. Any help would be appreciated.

AJAX

$(document).on("click", ".get_notes", function(e){
e.preventDefault();
 const notesSection = $(this).data("notes-section")
    $.ajax({
    url: WP.ajax_url,
    type: 'POST',
    dataType: 'json',
    data: {
    'action': 'notes',
    'notes_section' : notesSection
    },

    success: function(data) {
     if (data.success == true) {
       $(".notes-timestamp).text(**json data here**); // Somehow get access to all data from mysql query to use in HTML page 
       $(".notes-user-note").text(**json data here**); // Somehow get access to all data from mysql query to use in HTML page
      }
    },        
  });
});

PHP


  if (isset($_POST['notes_section'])) {
    $notesSection = $_POST['notes_section'];

    $getNotes = $wpdb->prepare("SELECT * FROM wp_activity_notes WHERE userId =%d AND postId =%d AND siteId =%d ORDER BY id DESC", $user_id, $notesSection, $site_id, OBJECT);
    
    $getNotesQuery = $wpdb->get_var($getNotes);
    json_encode($getNotesQuery);
    foreach($getNotesQuery as $f){
      $json_array = array(
        $f->notes
      );
    }

    wp_send_json_success($json_array);

  }

CodePudding user response：

if (isset($_POST['notes_section'])) {
    $notesSection = $_POST['notes_section'];

    $getNotes = $wpdb->get_results( 
        $wpdb->prepare( "SELECT * FROM wp_activity_notes WHERE userId=%d AND postId=%d AND siteId=%d ORDER BY id DESC", $user_id, $notesSection, $site_id), ARRAY_A
    );

    $getNotesQuery = $wpdb->get_var($getNotes);
    $json_array = array();
    foreach($getNotesQuery as $f){
      $json_array[] = $f->notes;
    }
    $output = json_encode($json_array);
    wp_send_json_success($output);

}

CodePudding user response：

I ended up having to use the below sql query instead of using a prepare

$results = $wpdb->get_results( "SELECT * FROM wp_activity_notes WHERE userId = $user_id AND postId = $notesSection AND siteId = $site_id ORDER BY id DESC" );