MySQL loop inside Datatables column

Question

In my Datatables grid, I load MySQL data with a server-side PHP script. I need to loop inside column. For example my datatables currently (select from tb order):

//product table
|  id  |      Salesman 
-----------------------------------------
|  1  |     Arthur
|  2  |     Richard
|  3  |     Patrick

Since there is more than one product per order, I want to loop inside column to show all products from that order like this (INNER JOIN tb products x tb order):

 |  id  |      Salesman       Products 
-----------------------------------------  
|  1  |     Arthur          | Link stabilizer |
                            | CV joint kits |
                            | Parke brake lever |

|  2  |   Richard          | Radiator hose |
                           | Park brake lever |
|  3  |   Patrick          | Radiator hose |

I tried to use while{} inside "render" function, but didn't work cause JS is client side. 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));
?>

//fetch.php to build whole table:

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

Ricardo · Accepted Answer · 2020-01-18 20:15:57Z

1

I solved the question with GROUP_CONCAT, considering 3 tables: "app_request" as products of the order; "app" as order number; "app_product" as product names.

MySQL script:

SELECT a.*, GROUP_CONCAT(r.product) as product_ids, GROUP_CONCAT(p.product) as product_names FROM app 
as a LEFT JOIN app_request as r ON r.request = a.request LEFT JOIN app_product as p ON p.id = r.product;

answered Jan 18, 2020 at 20:15

Ricardo

538 bronze badges

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

Comments

Maulik patel · Accepted Answer · 2019-12-11 07:42:40Z

0

SELECT
  productdata.id,
  student.Salesman,
  app_order.Products
FROM
  productdata
INNER JOIN app_order ON productdata.id = app_order.order
GROUP BY productdata.id;

answered Dec 11, 2019 at 7:42

Maulik patel

2,4502 gold badges21 silver badges28 bronze badges

2 Comments

Ricardo Over a year ago

Actually my SQL script works that feeds the main table is working (fetch.php added in main question). List.php loop works by itself, my difficulty is getting SQL response inside Ajax Jquery. My ajax call is not getting any response from list.php. Datatables build rows in $sub_array[] in fetch.php, I tried MySQL query, but don´t work inside an array.

Maulik patel Over a year ago

You can't fetch response because require to return response with json_encode with array

Collectives™ on Stack Overflow

MySQL loop inside Datatables column

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related