Skip to content
Advertisement

How to pass correctly a php json_encode value to a table that is using DataTables plugin?

I’m working on a project that using a payment gateway and i would like to display values from payment API in JSON format to a table that use Datatable JS plugin. Firstly, i found some similar questions related with my doubt, but unfortunately i’m having some dificulties to find a correct way to solve this.

  1. Using jQuery AJAX to pass JSON Object to PHP file
  2. How to parse nested JSON object in ajax using DataTables
  3. How to pass JSON object to PHP
  4. How to pass json object using PHP in Wepay API
  5. Display JSON object using DataTables

I’m using a php Curl script that call an URL API and get results in JSON format:

all_payments.php

<?php 

$token = "TOKEN_PROD";

$ch = curl_init('URL_API');

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

curl_setopt($ch, CURLOPT_HTTPHEADER, array(
   'Content-Type: application/json',
   'Authorization: Bearer ' . $token
   ));

$data = curl_exec($ch);

$info = curl_getinfo($ch);

echo $data;

curl_close($ch);

?>

And below are the API results in JSON format when Curl script above are executed:

{
    "results": [
        {
            "metadata": {},
            "corporation_id": null,
            "operation_type": "regular_payment",
            "fee_details": [],
            "notification_url": null,
            "date_approved": null,
            "money_release_schema": null,
            "payer": {
                "first_name": "Test",
                "last_name": "Test",
                "email": "test_user_80534729@test.com",
                "identification": {
                    "number": "34579430",
                    "type": "DNI"
                },
                "phone": {
                    "area_code": "01",
                    "number": "1111-1111",
                    "extension": ""
                },
                "type": "registered",
                "entity_type": null,
                "id": "660948681"
            },
            "transaction_details": {
                "total_paid_amount": 10,
                "acquirer_reference": null,
                "installment_amount": 0,
                "financial_institution": "",
                "net_received_amount": 0,
                "overpaid_amount": 0,               
                "payable_deferral_period": null,
                "payment_method_reference_id": "1848987916",
                "verification_code": "1343987916"
            },
            "statement_descriptor": null,
            "call_for_authorize_id": null,
            "installments": 1,
            "pos_id": null,
            "external_reference": null,
            "date_of_expiration": "2020-10-20T22:59:59.000-04:00",
            "charges_details": [],
            "id": 1453987916,
            "payment_type_id": "ticket",
            "barcode": {
                "content": "23791841402309310003380250122998791600633330"
            },
            "order": {
                "id": "1820096336",
                "type": "mercadopago"
            },
            "counter_currency": null,
            "brand_id": null,
            "status_detail": "pending_waiting_payment",
            "differential_pricing_id": null,
            "additional_info": {
                "ip_address": "123.456.678.890",
                "nsu_processadora": null,
                "available_balance": null
            },
            "live_mode": false,
            "marketplace_owner": null,
            "card": {},
            "integrator_id": null,
            "status": "pending",
            "transaction_amount_refunded": 0,
            "transaction_amount": 10,
            "description": "Short",
            "money_release_date": null,
            "merchant_number": null,
            "refunds": [],
            "authorization_code": null,
            "captured": true,
            "collector_id": 58546234946,
            "merchant_account_id": null,
            "taxes_amount": 0,
            "date_last_updated": "2020-10-17T11:30:31.000-04:00",
            "coupon_amount": 0,
            "store_id": null,
            "date_created": "2020-10-17T11:30:31.000-04:00",
            "acquirer_reconciliation": [],
            "sponsor_id": null,
            "shipping_amount": 0,
            "issuer_id": null,
            "payment_method_id": "ticket",
            "binary_mode": false,
            "platform_id": null,
            "deduction_schema": null,
            "processing_mode": "aggregator",
            "currency_id": "USA",
            "shipping_cost": 0
        }
    ]
}

Based on JSON results above, i’m trying to pass some JSON objects to inside a table that use Datatables Plugin. Below are the html and javascript codes that display a table with Datatable plugin:

<link rel="stylesheet" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.css">

<script src="jquery-3.5.1.min.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>

<table id="example" class="display" width="100%">
    
   <thead>
       <tr>
            <th>id</th>
            <th>date created</th>
            <th>email</th>
            <th>total_paid_amount</th>
            <th>status</th>
       </tr>
   </thead>
    
</table>

<script>

    $(document).ready(function() {

    $('#example').DataTable({

          "ajax": {
          "url": "all_payments.php",
          "dataSrc": "results"
            },
        "columns": [
            { "data": "id" },
            { "data": "date_created" },
            { "data": "email" },
            { "data": "total_paid_amount" },
            { "data": "status" }
        ]
    });
    });
    
    </script>

When the page with Datatable reload, i get an error on browser console:

Uncaught TypeError: Cannot read property 'length' of undefined
    at jquery.dataTables.min.js:65
    at h (jquery.dataTables.min.js:52)
    at Object.success (jquery.dataTables.min.js:52)
    at c (jquery-3.5.1.min.js:2)
    at Object.fireWith [as resolveWith] (jquery-3.5.1.min.js:2)
    at l (jquery-3.5.1.min.js:2)
    at XMLHttpRequest.<anonymous> (jquery-3.5.1.min.js:2)

In this case, how could i improve the Curl php script (all_payments.php) to pass JSON correctly to table that is using Datatables plugin? I tryed to replace:

echo $data;

to

$decode = json_decode($data,true);

    foreach ( $decode["results"] as $value){
        echo $value["id"];
        echo $value["date_created"];
        echo $value["email"];
        echo $value["total_paid_amount"];
        echo $value["status"];
    }

but it did not work.

Advertisement

Answer

I found a solution. I edited all_payments.php script as below:

<?php

//setup your token payment or others
$token = "PROD_TOKEN";

//setup the request, you can also use CURLOPT_URL
$ch = curl_init('API_URL');

// Returns the data/output as a string instead of raw data
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

//Set your auth headers
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
   'Content-Type: application/json',
   'Authorization: Bearer ' . $token
   ));

// get stringified data/output. See CURLOPT_RETURNTRANSFER
$data = curl_exec($ch);

// get info about the request
$info = curl_getinfo($ch);

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

$new_array = array(); // this array should be empty

$decode = json_decode($data);

// access property of object in array
echo '{"data":';
foreach($decode->results as $item) {
  $new_array[] = array(
    'id' => $item->{'id'},'date_created' => $item->{'date_created'}, 'email' => $item -> payer ->{'email'}, 'total_paid_amount' => $item -> transaction_details ->{'total_paid_amount'}
    );  
}

echo json_encode($new_array);

echo '}';
// close curl resource to free up system resources
curl_close($ch);


?>

On foreach the script get object values and then output results passing json values to Datatables, as code below:

<table id="example" class="display" width="100%">
    
            <thead>
            <tr>
                <th>ID</th>
                <th>Date Created</th>
                <th>Email</th>
                <th>Total</th>
            </tr>
        </thead>
    
</table>

<script>
    $(document).ready(function() {
  
    $('#example').DataTable( {

        "ajax": "all_payments.php",
        "columns": [
            { "data": "id" },
            { "data": "date_created" },
            { "data": "email" },
            { "data": "total_paid_amount",
            
                render: $.fn.dataTable.render.number( ',', '.', 2, '$ ' )
                
            },
        ],
        
    "columnDefs":[
        {"targets":1, render:function(data){
      return moment(data).format('MM/DD/YYYY');
    }},
    
            {"targets": 3,
        "className": 'dt-body-right'}
    
    ]

    });
  
    });
    
    
    </script>

That’s it. If anyone would like to improve this code, feel free.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement