CODEWITHSUNDEEP
phpjquerymysqlajaxjson
sherly
sherly

Reputation: 305

How to pass mysql result as jSON via ajax

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;

});

Upvotes: 4

Views: 8034

Answers (5)

hanshenrik
hanshenrik

Reputation: 21463

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);

Upvotes: 0

shyammakwana.me
shyammakwana.me

Reputation: 5752

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'])

Upvotes: 0

Jitendra
Jitendra

Reputation: 578

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;

});

Upvotes: 1

Ali insan Soyaslan
Ali insan Soyaslan

Reputation: 846

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

Here is an example :

Parse JSON in JavaScript?

Upvotes: 0

xNeyte
xNeyte

Reputation: 622

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

Upvotes: 3

Related Questions