Get info from mysql database with jquery and php

Question

I'm trying to get data from mysql database using ajax and restapi in php. I wanted that I inserted a value on a search bar and it shows me the data from mysql

api code:

        $response = array();
        $posts = array();
        $ID = $_POST['ID'];
        $sql = "SELECT * FROM table WHERE client_id = ?";
        $stmt = $mysqli->prepare($sql);
        $stmt->bind_param("s", $ID);
        $stmt->execute();
        $result= $stmt->get_result();
        $row = $result->fetch_assoc();
        while($row=mysql_fetch_array($result)) { 
          $user_id=$row['id'];
          $username=$row['username'];   
          $client_id=$row['client_id']; 
          $token=$row['token'];  

          $posts = array('id'=> $user_id, 'username'=> $username, 'client_id'=> $client_id, 'token'=> $token);
        } 

        echo json_encode($posts);

        $stmt = null;   
        $mysqli = null;
    }

?>

And this is the ajax code

$('document').ready(function()
                { 
                    $("#search").on("click", function(e){
                        e.preventDefault();
                        var to_search = $("#search_value").val();
                        console.log(to_search);

                        var formData = {
                        'ID' : to_search,
                      };

                      $.ajax({
                        type : 'POST',
                        url : 'search.php',
                        data : formData,
                        dataType : 'JSON',
                        encode : true,
                        success: function (data, response, status, xhr) {
                          if (response.result) {
                            console.log('done');
                            console.log(data);
                          }else{
                            console.log('fail');
                            console.log(data);
                          }
                        },
                        error: function (xhr, status, error) {

                        }
                                });              
                             });    
                  });

It's not working, I would just like that it print the result on the browser console. The button is working because it shows on the console the value that I inserted on the search bar Any advices?

Answer 1

I don't see any problem in your ajax call, however i did some amendments to your PHP code (this is untested).

    $response = array();
    $posts = array();
    $ID = isset($_POST['ID']) ? $_POST['ID'] : 0; // Avoid warning because of missing "ID" post field
    $sql = "SELECT * FROM table WHERE client_id = ?";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('i', $ID); // ID's probably an integer, not a string
    $stmt->execute();
    $result= $stmt->get_result();
    // Removed $row = $result->fetch_assoc();
    while($row = $result->fetch_assoc()) { // Better use it this way
        $user_id = $row['id'];
        $username = $row['username'];   
        $client_id = $row['client_id']; 
        $token = $row['token'];  

        array_push($posts, array('id'=> $user_id, 'username'=> $username, 'client_id'=> $client_id, 'token'=> $token)); // Add the current loop element data to $posts
    } 

    echo json_encode($posts);

    $stmt = null;   
    $mysqli = null;