How to parse Json data, which is made by select mysql query?

Question

My server code is returning json data, which have select mysql query. Now I have to parse this information and I need to fill there json information into table, How I will do that?

My server code

<?php
header('Access-Control-Allow-Origin: *');//Should work in Cross Domaim ajax Calling request
mysql_connect("localhost","root","2323");
mysql_select_db("service");

if(isset($_POST['type']))
{
    if($_POST['type']=="carpenter"){
        $startDate=$_POST['startDate'];
        $endDate=$_POST['endDate'];
        $query="select * from booking where scheduledDate between $startDate AND $endDate"; 
        $result=mysqi_query($query);
        $count=mysql_num_rows($result);         
        $retVal=array();

        while($row=mysqli_fetch_assoc($result)){
            $$retVal[]=$row;
        }
        echo json_encode($retVal);
    }
} else{
    echo "Invalid Format";
}

My script

<script>
    function fetchData2(){
      $(".data-contacts2-js tbody").empty();
      var startDate=$('#datepicker1').val();
      var endDate=$('#datepicker2').val();
      $.ajax({
              url: "http://localhost/service/cleaning.php",
              type:"POST",
              dataType:"json",
              data:{type:"carpenter", startDate:startDate, endDate:endDate},
              ContentType:"application/json",
              success: function(response){                           
                 alert(obj);
             },
             error: function(err){
                alert("fail");
            }       
        });             
     }  

     $(document).ready(function(){
         $(".data-contacts2-js tbody").empty();               
         $('#fetchContacts2').click(function() {
                 fetchData2();
         });
      });

 </script>

My html code

<div class="block-content collapse in">
      <div class="span12">
        <table class="data-contacts2-js table table-striped" >
             <thead>
                    <tr>
                          <th>ID</th>
                          <th>Customer Name</th>
                          <th>Customer Mobile</th>
                          <th>Customer Email</th>
                          <th>Address</th>
                          <th>Date</th>
                          <th>Time</th>
                          <th>Status</th>
                    </tr>
          </thead>
             <tbody>

             </tbody>
      </table>                                  
  </div>
 <button id="fetchContacts2" class="btn btn-default" type="submit">Refresh</button>                         
          </div>

My Json format is

[
    {
        "b_id": "101",
        "cust_name": "qwq",
        "cust_mobile": "323232323",
        "cust_email": "[email protected]",
        "cust_address": "kslaksl",
        "scheduledDate": "2015-02-26",
        "scheduledTime": "14:30:00",
        "sc_id": "3",
        "sps_id": "1"
    }
]

My dataBase table: enter image description here

you could build your markup table rows from the response using $.each then just .html(markup) on the tbody. is this a typo? $result=mysqi_query($query); — Kevin
– Kevin, Commented Feb 19, 2015 at 6:37
@Outlooker, thanks for reply, I add my database snapshot, please see it, I am new in JSon, I dn't know How would be response.. — Neelabh Singh
– Neelabh Singh, Commented Feb 19, 2015 at 6:51
@neelabhsingh just use that function inside the success block, $.each(response, function(index, element){ // build html here }); and remove that ContentType:"application/json", you don't need that — Kevin
– Kevin, Commented Feb 19, 2015 at 6:54

Nibin · Accepted Answer · 2015-02-19 07:11:42Z

1

The $.each() function can be used to iterate over any collection, whether it is an object or an array. In the case of an array, the callback is passed an array index and a corresponding array value each time. Inside the ajax success try the each function and loop through the response data that has been received from the php file.Hope that gives you an idea mate.. :)

        success: function(response){                           
             $.each(response, function(idx, obj) {
                $('table tbody').append(
                $('<tr>')
                    .append($('<td>').append(obj.id))
                    .append($('<td>').append(obj.cust_email))
                    .append($('<td>').append(obj.cust_mobile))
                );
             });
         },

FYI

$.each

edited Feb 19, 2015 at 7:11

answered Feb 19, 2015 at 6:58

Nibin

3,9602 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nibin Over a year ago

yes idx means index..check the updated answer mate.

Community · Accepted Answer · 2017-05-23 12:28:30Z

One way is to use $.each and start building your markup table rows, then putting it inside the tbody tag.

You could just build them inside the success block. Here's the basic idea.

$.ajax({
    url: "http://localhost/service/cleaning.php",
    type: "POST",
    dataType: "json",
    data: {type:"carpenter", startDate:startDate, endDate:endDate},
    success: function(response){                
        var rows = '';           
        $.each(response, function(index, element){
            rows += '<tr>'; // build the row
                $.each(element, function(key, val){
                    rows += '<td>' + val + '</td>'; // build the value
                });
            rows += '</tr>';
        });
        $('table tbody').html(rows);
    }   
});

Sidenote: Based on your code, your mixing MySQLi and MySQL functions.

Obligatory Note:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

I suggest use PDO with prepared statements:

<?php
header('Access-Control-Allow-Origin: *');//Should work in Cross Domaim ajax Calling request
$db = new PDO('mysql:host=localhost;dbname=service', 'root', '2323');
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
if(isset($_POST['type'])) {
    $startDate = $_POST['startDate'];
    $endDate = $_POST['endDate'];
    $query = 'SELECT * FROM booking WHERE scheduledDate BETWEEN :startDate AND :endDate'; 
    $select = $db->prepare($query);
    $select->bindParam(':startDate', $startDate);
    $select->bindParam(':endDate', $endDate);
    $select->execute();

    $data = $select->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($data);
    exit;
}

Collectives™ on Stack Overflow

How to parse Json data, which is made by select mysql query?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related