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.
- Using jQuery AJAX to pass JSON Object to PHP file
- How to parse nested JSON object in ajax using DataTables
- How to pass JSON object to PHP
- How to pass json object using PHP in Wepay API
- 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.