How do I get value from SQL using AJAX

Question

I know how to pass data from JS to PHP using AJAX, but have no idea how to select data to JS from db using AJAX+php.

I tried to find examples of it but nothing clear found.

Could anyone show me how can I get data from SQL? How I tried:

js function

getdata() {
    // ?
    var result // I want result to store here
    var data = new FormData();
    data.append('somekey', 'somevalue');

    // AJAX CALL
    var xhr = new XMLHttpRequest();
    // query for getting some data from SQL
    xhr.open('POST', "../php/get_answer.php", true);
    xhr.onload = function(){
        result = this.response // I presume that I can get result here
    };
    xhr.send(data);
    console.log("RESULT GETTING JSON")
    console.log(result)
}

get_answer.php

<?php

include("config.php");
$con = setConnection();
$id = $_COOKIE["id"];
$query    = "SELECT results FROM `survey_results` WHERE user_id='$id'";
$n   = mysqli_query($con, $query);
$results = 0;
while ($row = mysqli_fetch_assoc($n)) {
    $results = $row['results'];
}

// return results ?

$con->close();

?>

As Danny alluded to, you're not outputting the results. XHR/Fetch/Promises, etc will all make a request for your PHP to do it's thing, and wait for it to output something (usually a JSON string) and return it to the function that initialized it. — Xhynk, Sep 03 '20 at 18:27
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Sep 03 '20 at 18:48

JB_DELR · Answer 1 · 2020-09-04T00:09:05.643

In your php file, you can return your data as JSON string. To do this, tell the client it's json by settings the response header to

header('Content-Type: application/json');

and return the results or data with

echo json_encode($data);

For the Javascript part, XMLHttpRequest is now an old way to make Ajax request but it's a good start to learn. Fisrt, in your code you have to check if XMLHttpRequest is available in the navigator and try to use the old IE fashion way if not. To do this:

if (window.XMLHttpRequest) {
   // code for modern browsers
   xmlhttp = new XMLHttpRequest();
 } else {
   // code for old IE browsers
   xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}

now you have your object so you have to set a listener witch listen for change in the state of XMLHttpRequest. If all seems ok the result go there:

    xhr.onreadystatechange = function()
       { 
         console.log("Wait server...");                
         if(xhr.readyState == 4) // 4 : request finished and response is ready
         {
            if(xhr.status == 200) // HTTP OK
            { 
              console.log("Got data!");   
              result=xhr.responseText; // or result = JSON.parse(xhr.responseText) to have your data as js object
//It's here you have to modify your dom to show your data, change a variable, etc...
            } ;
           else // bad http response header like forbiden, not found, ...
            { 
                  console.log("Error: returned status code", xhr.status, xhr.statusText);
           } 
        } 
     };

now you can set the URI and send your request:

 xhr.open("GET", "../php/get_answer.php", true);                
 xhr.send(null)

;

If you want more informations about states and status, have a look at XMLHttpRequest Object Methods

How do I get value from SQL using AJAX

1 Answers1