Ajax from JS to PHP (to exec sql request) then from PHP to JS

Question

I'm using $.ajax method to pass data from my JS file to my PHP file "requetes.php". This PHP file execute SQL request depending on a variable I have on my JS file.

My Javascript:

$.ajax({
  url: 'requetes.php',
  type: 'POST',
  data: {
    datepicker: formattedDate
  },
  success: function(data) {             
    $("#sql-results").text(data);            
  },
  error: function(xhr) {
    console.log("echec de l'ajax !");
  }
})

In my PHP file:

$rq_datepicker = "
    SELECT * 
    FROM partie
    WHERE date = '"$_POST['datepicker']"'";


$query = mysqli_query($connect, $rq_datepicker);
while ($result = mysqli_fetch_array($query)) {  
    echo "Il y a ".$result['Reussie']." partie(s)";
    echo "Il y a ".$result['Perdue']." partie(s);"
}

I have reduced the code so it is easier to explain, but this works perfectly. I can get the "datepicker" variable from JS to PHP so I can execute my SQL request depending on this variable. And the line $("#sql-results").text(data); return my 2 echos from my PHP file.

MY PROBLEM IS :

I just want to access those 2 variables $result['Reussie'] & $result['Perdue'] from my JS file.

I've tried many things like using $.ajax with method GET, or $.get, but without success..

Answer 1

You have to structure your PHP response in a way your data will be accessible in JS. To do so, usually you use JSON, in a way that gives you data already processed and you just have to access them locally in JS.

In this case you have 2 variables which you want to access. But i guess you also want to format them into some object you have selected in jQuery afterwards.

To achieve that, there are 2 ways.

First one - do all the processing in PHP and just make variables available.

PHP side:

$rq_datepicker = "
    SELECT * 
    FROM partie
    WHERE date = '"$_POST['datepicker']"'";


    $query = mysqli_query($connect, $rq_datepicker);
    while ($result = mysqli_fetch_array($query)) { 
            $response['dataRaw'] = array('Reussie' => $result['Reussie'],
                                     'Perdue' => $result['Perdue']);
            $response['dataProcessed'] = array('Reussie' =>  "Il y a ".$result['Reussie']." partie(s)",
                                    'Perdue' => "Il y a ".$result['Perdue']." partie(s)");
        }
    }
echo json_encode($response); // <-- using this will encode the variable according to the json specifics

Once the php handles the data, in JS you just have to access them in your anonymous function bind to success of the Ajax call

JS Side ( in case HTTP request goes 200 ):

success: function( data )
{               
    var response = JSON.parse(data);
    $("#sql-results").text(response.dataProcessed.Reussie+response.dataProcessed.Perdue);
    // to access the variables instead:
    // response.dataRaw.Reussie
    // response.dataRaw.Perdue
}

Second one - PHP just passes variables to JS and then you build your string locally there.

PHP side:

$rq_datepicker = "
    SELECT * 
    FROM partie
    WHERE date = '"$_POST['datepicker']"'";


    $query = mysqli_query($connect, $rq_datepicker);
    while ($result = mysqli_fetch_array($query)) { 
            $response['data'] = array('Reussie' => $result['Reussie'],
                                     'Perdue' => $result['Perdue']);
        }
    }
echo json_encode($response); // <-- using this will encode the variable according to the json specifics

In this case you don't have preprocessed data, so you have to build your strings locally

JS Side ( in case HTTP request goes 200 ):

success: function( data )
{               
    // to access the variables in this case:
    // response.data.Reussie
    // response.data.Perdue
    var response = JSON.parse(data);
    var stringReussie = "Il y a "+response.data.Reussie+" partie(s)";
    var stringPerdue = "Il y a "+response.data.Perdue+" partie(s)";
    $("#sql-results").text(stringReussie+stringPerdue);
}

Some extra:

Using just an json_encode with an echo as output without declaring the response type, will usually set the response type to "application/json".

echo json_encode($response);

To avoid that "usually" and ensure that the content type part of the response is "application/json" ( if that's the case ), you should set the header of the response also.

header("Content-type:application/json")

Further reading about headers: PHP Manual header()

About json_encoding, read further information here: https://www.php.net/manual/en/function.json-encode.php