Data disappears when trying to add array in column

Tivvy

I'm sorry I have no test case, this is on my localhost.

Ajax code:

require_once('../connection/connection.php');

$sql = "SELECT * FROM accounts WHERE invoice='sent'";

$result = mysqli_query($con,$sql);
 
if ($result->num_rows > -1) {

    while($row = mysqli_fetch_assoc($result)){

        $id = $row['id'];   

        $sql2 = "SELECT * FROM invoices WHERE account_id=$id";
        $result2 = mysqli_query($con,$sql2);

        if ($result2->num_rows > -1) {

            while($row2 = mysqli_fetch_assoc($result2)){
                $data[] = $row2;
                
                /*
                $item_array = array("Bob Marley", "Harley Quinn", "Jack Daniels");  
                $items = implode(', ', $item_array);
                $data['items'] = $items;
                */

                
                $invoice_no = $row2['invoice_no'];  

                $sql3 = "SELECT * FROM item_list WHERE invoice_no=$invoice_no";
                $result3 = mysqli_query($con,$sql3);

                if ($result3->num_rows > -1) {

                    $item_array = array();

                    while($row3 = mysqli_fetch_assoc($result3)){
                        $item_array[] = $row3['item_name'];
                    }   

                    $items = implode(', ', $item_array);

                    $data['items'] = $items;
                }


            }       
        }
    }
}

$results = ["sEcho" => 1,
            "iTotalRecords" => count($data),
            "iTotalDisplayRecords" => count($data),
            "aaData" => $data ];
 
echo json_encode($results);

Jquery/Javascript Datatable Function:

$('#paid-invoices').dataTable({
    "bProcessing": true,
    "sAjaxSource": "ajax-fetch-processing-invoices.php",
    "aoColumns": [
        { mData: 'invoice_no' } ,
        { mData: 'first_name' },
        { mData: 'last_name' },
        { mData: 'company_name' },
        { mData: 'items' },
        { mData: 'date' }
        ]
}); 

No data available in table

Good day,
I've been having some trouble adding an array to a table, the table contains invoice information, and the array contains item information descriptive of that particular invoice. When I run this code, there are no errors per se, but the table is instead completely blank of records, and displays this message: "No data available in table". Like I said, not exactly an error, but it doesn't display any of the data on the invoices or items that are stored in the database.

I figured that that might just be how the queries are configured, and perhaps my invoice didn't have items linked up to it or vice versa, so I tested it out using a dummy array (quoted markup in ajax syntax above), but the same thing happened.

I also checked the network for the ajax request sent out, and everything has been sent out successfully:
{"sEcho":1,"iTotalRecords":3,"iTotalDisplayRecords":3,"aaData":{"0":{"invoice_no":"139","first_name":"Tivya","last_name":"Breytenbach","company_name":"Faero","account_id":"41","date":"2021-07-27"},"items":"zd","1":{"invoice_no":"137","first_name":"Tivya","last_name":"Breytenbach","company_name":"Faero","account_id":"43","date":"2021-07-27"}}}

I'm not sure if this is just not the proper way to write the syntax, but I've looked through tons of documentation and questions related to this (both on the forum and stackoverflow) to no avail. This is the first question I've ever posted on a forum, I would really appreciate it if someone could help me, I'm not sure what I'm doing wrong.

Best wishes

allan

"aaData":{"0":

That's an object for some reason. DataTables needs the data (or aaData if you prefer the legacy style) to be an array.

I think it is becoming an object rather than an array because of:

$data['items'] = $items;

Which results in JSON like this, which I suspect is not what you intended:

{
    "sEcho": 1,
    "iTotalRecords": 3,
    "iTotalDisplayRecords": 3,
    "aaData": {
        "0": {
            "invoice_no": "139",
            "first_name": "Tivya",
            "last_name": "Breytenbach",
            "company_name": "Faero",
            "account_id": "41",
            "date": "2021-07-27"
        },
        "items": "zd",
        "1": {
            "invoice_no": "137",
            "first_name": "Tivya",
            "last_name": "Breytenbach",
            "company_name": "Faero",
            "account_id": "43",
            "date": "2021-07-27"
        }
    }
}

Allan

Tivvy

Hi, Allan. Thank you so much for your quick response, I added the array as a string, value pair to the first array and now it works perfect! Thanks so much!

require_once('../connection/connection.php');

$sql = "SELECT * FROM accounts WHERE invoice='sent'";

$result = mysqli_query($con,$sql);
 
if ($result->num_rows > -1) {

    while($row = mysqli_fetch_assoc($result)){
        
        $id = $row['id'];   

        $sql2 = "SELECT * FROM invoices WHERE account_id=$id";
        $result2 = mysqli_query($con,$sql2);

        if ($result2->num_rows > -1) {

            while($row2 = mysqli_fetch_assoc($result2)){
                //$item_array = array("Bob Marley", "Harley Quinn", "Jack Daniels");    
                //$items = implode(', ', $item_array);
                //$data[] = $items;
                
                
                $invoice_no = $row2['invoice_no'];  

                $sql3 = "SELECT * FROM item_list WHERE invoice_no=$invoice_no";
                $result3 = mysqli_query($con,$sql3);

                if ($result3->num_rows > -1) {

                    $item_array = array();

                    while($row3 = mysqli_fetch_assoc($result3)){
                        $item_array[] = $row3['item_name'];
                    }   

                    $items = implode(', ', $item_array);

                    //$data[] = $items;
                }
                
                $sql3 = "SELECT * FROM property_list WHERE mem_id=$id";
                $result3 = mysqli_query($con,$sql3);

                if ($result3->num_rows > -1) {

                    $property_area = array();

                    while($row3 = mysqli_fetch_assoc($result3)){
                        $property_area[] = $row3['area'];
                    }   

                    $areas = implode(', ', $property_area);

                    //$data[] = $items;
                }
                
                $row2['items'] = $items;
                $row2['areas'] = $areas;
                $data[] = $row2;

            }       
        }
    }
}

$results = ["sEcho" => 1,
            "iTotalRecords" => count($data),
            "iTotalDisplayRecords" => count($data),
            "aaData" => $data ];
 
echo json_encode($results);