Convert JSON string to object and display as HTML table

Question

I have a dropdown select option with onchange event which will send selected value via AJAX to my controller and then query data based on the selected value from MySQL database and return it as JSON object to my blade.

My problem is the JSON data from mysql database currently in string as a single row. How to convert it back into normal JSON object and display each value based on the column name as HTML table in a new window.

Process that suppose to be like this

1. Select date from the dropdown option.
2. Get data from database based on date selected.
3. Convert JSON string to object and return it to the blade.
4. Display the data as HTML table on new window.

Controller Code for inserting JSON to MySQL database

$sqlquery = DB::connection('sqlsrv')
                ->table('Stocks')
                ->join('StockTransactions', 'Stocks.Id', '=', 'StockTransactions.StockId')
                ->select(
                   'TransactionDate',
                   'DocumentCode',
                   'DocType',
                   'StockName',
                   DB::raw("FORMAT(SUM(UOMQty), 'N') AS TotalUOMQty"),
                   DB::raw("FORMAT(SUM(UOMPrice), 'N') AS TotalUOMPrice"),
                   DB::raw("FORMAT(CurrentBalance, 'N') AS CurrentBalance"),
                   DB::raw("FORMAT(((SUM(UOMPrice) / SUM(UOMQty)) * CurrentBalance), 'N') AS Total")
                    )
                ->where([
                    ['UOMQty', '>', '0'],
                    ['DocumentCode', 'NOT LIKE', 'SA%'],
                    ['DocumentCode', 'NOT LIKE', 'SIN%']
                ])
                ->WhereBetween('TransactionDate', ['01-01-2000', $dateto])
                ->groupBy('StockName', 'TransactionDate', 'CurrentBalance', 'DocumentCode', 'DocType')
                ->orderBy('TransactionDate', 'ASC')
                ->get();

$array = json_decode(json_encode($sqlquery), true);
$json  = json_encode($array);

 DB::connection('mysql')
     ->table('records')
     ->insert(['record' => $json, 'date_from' => "2000-01-01", 'date_to' => "$dateto", 'date_inquiry' => "$getCurrentDateTimeTo"]);

Controllor Code for getting JSON from MySQL database

public function getmysqldata(Request $request)
{
  $request_data = $request->all();
  $dateInquiry = $request_data['dateInquiry'];

  $result = DB::connection('mysql')
                ->table('records')
                ->where([
                  'date_inquiry' => "$dateInquiry"
                ]) 
                ->get();

  return response()->json($result);
}

Index.blade.php

Record Available :                             
<select name="recordavailable" id="recordavailable">
  @if(!empty($inquiry_date))
    <option value="">Please select records</option> 
    @foreach ($inquiry_date as $date_inquiry)
      <option value="{{ $date_inquiry->date_inquiry}}">{{ $date_inquiry->date_inquiry}}</option>
    @endforeach  
  @else 
    option value="">No records</option>                
  @endif                                  
</select>

Scripts

$(document).on("change", 'select#recordavailable', function(e) 
{
   var dateInquiry = $(this).val();
   $.ajax
   ({
      type: "GET",               
      data: 'dateInquiry='+dateInquiry,               
      url: 'getmysqldata',
      dataType: 'JSON',                
      success: function(data) 
      { 
         var randomnumber = Math.floor((Math.random()*100)+1); 

         var winPrint = window.open('',"",'PopUp'+randomnumber+', "scrollbars=1,menubar=0,resizable=1"');

         winPrint.document.write('<title>Display Record of '+dateInquiry+'</title><style>table{ border-collapse: collapse;border-spacing: 0;'+
                            'width: 100%;border: 1px solid #ddd;}table, td, th{border: 1px solid black;text-align:center;padding: 5px;}#stockname {text-align: left;}'+
                            'h3 {text-align:center;}#qty, #cost, #balance, #total {text-align: right;}</style><h3>'+dateInquiry+'</h3>'); 

         winPrint.document.write(JSON.stringify(data));
      } 
   });            
});

Console

Output

The Output I want it to be

Answer 1

You need to cast the record column in records table as array, which will cast it in proper json in output.

https://laravel.com/docs/5.8/eloquent-mutators#array-and-json-casting