Loop inside Datatables column

rihcardo

In my Datatables grid, I load MySQL data with a server-side PHP script (fetch.php) that result this:

//product table

|  id  |      product     
-----------------------------------------
|  1  | Link stabilizer 
|  2  | Park brake lever  
|  3  | Radiator hose 

Since there is more than one product per order, I want to loop inside column to show all products from that order:

|  id  |      Product      
-----------------------------------------
|  1  | Link stabilizer  
        CV joint kits   
|  2  | Radiator hose 
        Park brake lever 
        Radiator hose 
|  3  | Radiator hose 

Server side script (fetch.php) is working fine, but I can´t make loop inside specific column ("targets":[1]). I tried ajax inside "render" function, but always give me an alert: DataTables warning: table id=user_data - Requested unknown parameter '0' for row 0, column 0

JAVASCRIPT:

    var dataTable = jQuery('#user_data').DataTable
({
    "processing":true,
    "serverSide":true,
    "order":[],
    "ajax":{
        url:"fetch.php", 
        type:"POST"
        },
      "columnDefs":[{
        "targets":[1],
        "render": function (data, type, row, meta) {
          var dados=data;
         $.ajax({
         url: "list.php",
         dataType:"json",
         type: "POST",
         data: {dados: dados},
         success: function(data) {console.log(data);}
               })
                                           }
                 }],**
});

//SERVER SIDE SCRIPT TO MAKE LOOP INSIDE COLUMN(list.php):

<?php
include("db.php");
$dados = $_POST['dados'];

$sql1="SELECT product FROM app_order WHERE order='$dados'";
$result1=$mysqli->query($sql1); 
 while($row1=mysqli_fetch_array($result1)){
$product=$row1['product']; }
echo json_encode(array($product)); 
?>        

I tried to provide a test case, but it is a server side script.

colin

Can you post the JSON that's returned, please.

Colin

kthorngren

It looks like you are using an Ajax request inside columns.render. columns.render expects you to use a return statement to return the data for the column. You aren't doing this which results in the error you are receiving.

Using an Ajax request in columns.render is not recommended as it will slow down the render process. I suggest either providing that array for each row in the fetch.php response or use a jQuery Ajax to fetch all of the list.php data and in the success function initialize Datatables and build your column.render arrays using that array. Much more efficient to have one request than lots of small ones.

Kevin

rihcardo

@Colin, if I execute my list.php alone with a SQL WHERE clause predetermined '1', JSON returns the array below as it should return:

 [{"product":"1"},{"product":"5"},{"product":"6"},{"product":"3"},{"product":"26"},{"product":"2"},{"product":"20"},{"product":"654"},{"product":"19"},{"product":"14"}]

//list.php

<?php
    include("connection.php");
    $dados = $_POST['dados'];
    $return_arr = array();
    $sql1="SELECT product FROM order WHERE order='1'";
    $result1=$mysqli->query($sql1); 
         while($row1=mysqli_fetch_array($result1)){
     $product=$row1['product']; 
         $return_arr[] = array("product" => $product);}
            
    echo json_encode($return_arr);
?>   

@Kthorngren, I am not using "return" really, if I use "return:data" return data by itself, don't result JSON array, but as you say it will slow down the process. My fetch.php below. I tried to make an array for each row inside array, but it didn´t work:

<?php
include('db.php');
include('function.php');
$query = '';
$output = array();
$query = "SELECT * FROM order ";

$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();

$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
    $sub_array = array();
    $sub_array[]= $row['order'];
    $sub_array[] = $row['salesman'];
    $sub_array[] = 'I need an array here';
    $data[] = $sub_array;
}
$output = array(
    "draw"              =>  intval($_POST["draw"]),
    "recordsTotal"      =>  $filtered_rows,
    "recordsFiltered"   =>  get_total_all_records(),
    "data"              =>  $data
);
echo json_encode($output);
?>