How to pass mysql result as jSON via ajax

Question

I'm not sure how to pass the result of mysql query into html page via ajax JSON. ajax2.php

$statement = $pdo - > prepare("SELECT * FROM posts WHERE subid IN (:key2) AND Poscode=:postcode2");
$statement - > execute(array(':key2' => $key2, ':postcode2' => $postcode));
// $row = $statement->fetchAll(PDO::FETCH_ASSOC);
while ($row = $statement - > fetch()) {
    echo $row['Name']; //How to show this in the html page?
    echo $row['PostUUID']; //How to show this in the html page?
    $row2[] = $row;
}
echo json_encode($row2);

How to pass the above query result to display in the html page via ajax below?

my ajax

$("form").on("submit", function () {
    var data = {
        "action": "test"
    };

    data = $(this).serialize() + "&" + $.param(data);
    $.ajax({
        type: "POST",
        dataType: "json",
        url: "ajax2.php", //Relative or absolute path to response.php file
        data: data,
        success: function (data) {
            //how to retrieve the php mysql result here?
            console.log(data); // this shows nothing in console,I wonder why?
        }
    });
    return false;

});

check the network tab in console. – Sougata Bose May 06 '15 at 09:32 — Sougata Bose, May 06 '15 at 09:32
@sgtBOSE, teh status says ok for related files – sherly May 06 '15 at 09:43 — sherly, May 06 '15 at 09:43

score 3 · Accepted Answer · answered May 06 '15 at 09:39

Your json encoding should be like that :

 $json = array();
 while( $row = $statement->fetch()) {
     array_push($json, array($row['Name'], $row['PostUUID']));
 }

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

And in your javascript part, you don't have to do anything to get back your data, it is stored in data var from success function. You can just display it and do whatever you want on your webpage with it

score 1 · Answer 2 · answered May 06 '15 at 10:01

header('Content-Type: application/json');
$row2 = array();
$result = array();
$statement = $pdo->prepare("SELECT * FROM posts WHERE subid IN (:key2) AND Poscode=:postcode2");
$statement->execute(array(':key2' => $key2,':postcode2'=>$postcode));
   // $row = $statement->fetchAll(PDO::FETCH_ASSOC);
     while( $row = $statement->fetch())
     {
         echo $row['Name'];//How to show this in the html page?
          echo $row['PostUUID'];//How to show this in the html page?
          $row2[]=$row;
     }
if(!empty($row2)){
$result['type'] = "success";
$result['data'] = $row2;
}else{
$result['type'] = "error";
$result['data'] = "No result found";
}
echo json_encode($row2);

and in your script:

$("form").on("submit",function() {

    var data = {
      "action": "test"
    };
    data = $(this).serialize() + "&" + $.param(data);
    $.ajax({
      type: "POST",
      dataType: "json",
      url: "ajax2.php", //Relative or absolute path to response.php file
      data: data,
      success: function(data) {
        console.log(data);
         if(data.type == "success"){
           for(var i=0;i<data.data.length;i++){
              //// and here you can get your values //
             var db_data = data.data[i];
              console.log("name -- >" +db_data.Name );
              console.log("name -- >" +db_data.PostUUID);
           }
         }
         if(data.type == "error"){
            alert(data.data);
         }
      }
    });
    return false;

});

do I have to specifically locate this line'header('Content-Type: application/json');' somewhere? Because clearly something is wrong in my code, though you explicitly showed the code, it's not outputting anything!!!! — sherly, May 06 '15 at 10:23
its not mandatory. just put data = JSON.parse(data) in ajax success function. it will work. — Jitendra, May 06 '15 at 12:18

score 0 · Answer 3 · edited May 23 '17 at 11:46

0

In ajax success function you can use JSON.parse (data) to display JSON data.

Here is an example :

Parse JSON in JavaScript?

edited May 23 '17 at 11:46

Community

1
1

answered May 06 '15 at 09:57

Ali insan Soyaslan

836
5
14
33

score 0 · Answer 4 · answered May 06 '15 at 10:04

you can save json encoded string into array and then pass it's value to javascript.

Refer below code.

<?php 
// your PHP code 
$jsonData = json_encode($row2); ?>

Your JavaScript code

var data = '<?php echo $jsonData; ?>';

Now data variable has all JSON data, now you can move ahead with your code, just remove below line

 data = $(this).serialize() + "&" + $.param(data);

it's not needed as data variable is string.

And in your ajax2.php file you can get this through

json_decode($_REQUEST['data'])

score 0 · Answer 5 · answered May 06 '15 at 10:39

I would just..

$rows = $statement->fetchAll(FETCH_ASSOC);
header("content-type: application/json");
echo json_encode($rows);

then at javascript side:

xhr.addEventListener("readystatechange",function(ev){
//...
var data=JSON.parse(xhr.responseText);
var span=null;
var i=0;
for(;i<data.length;++i){span=document.createElement("span");span.textContent=data[i]["name"];div.appendChild(span);/*...*/}
}

(Don't rely on web browsers parsing it for you in .response because of the application/json header, it differs between browsers... do it manually with responseText);

How to pass mysql result as jSON via ajax

5 Answers5

Linked