I have a controller that contains sample for table in string format and returns it as a JSON response to index.blade.php
After that, I use datatables with AJAX-option to get the data and, upon success, it should convert the JSON format to a normal table in HTML.
I still don't understand how this works unless you guys show me how. Please help.
Here's my script code (esources\views\layouts\app.blade.php):
var table1 = $('#table1').DataTable(
{
ajax:{
url:'/',
type:"GET",
dataType: 'json',
success:function(data){
$('.table1display').html(data);
//console.log(data);
}
}
});
Here's the HTML code (esources\views\posts\index.blade.php):
@extends('layouts.app')
@section('content')
<div class="table1display"></div>
@endsection
Here's my controller code (\Controllers\PostController.php):
public function index()
{
$getNote2 = DB::connection('mysql')->table('note2')->select('stockcode', 'note')->orderBy('stockcode', 'ASC')->get();
$display = '';
$display .= "<table id='table1' class='cell-border wrap' cellspacing='0' width='100%'>
<thead>
<tr>
<th>Test</th>
</tr>
</thead>
<tbody>";
foreach ($getNote2 as $val)
{
$display .= "<tr>
<td>".($val->stockcode)."</td>
</tr>";
}
$display .="</body></table>";
return response()->json($display) ;
}
Here's my route code:
Route::get('/', 'PostController@index');
Route::resource('posts', 'PostController');
I recommend to get your view, or frontend, separated from your backend. I think it is a bad idea to create the view inside the controller. So, with that in mind, you had to return only the value from you database and working with it in your view with JS.
For example, in you controller you may do following changes:
public function index()
{
$getNote2 = DB::connection('mysql')->table('note2')->select('stockcode', 'note')->orderBy('stockcode', 'ASC')->get();
return response()->json($getNote2) ;
}
After that you have to create the table in the view. You have several options, like create all the table or maybe you can have the table prepared in you html code.
For example:
Here's the html of index.blade.php code
<table class="table">
<thead>
<tr>
<th scope="col">stockcode</th>
<th scope="col">note</th>
</tr>
</thead>
<tbody id = "table1">
</tbody>
</table>
And finally in you js code
var table1 = $('#table1').DataTable(
{
ajax:{
url:'/',
type:"GET",
dataType: 'json',
success:function(data){
//Here you can use a for to work with the data, but is better working with es6, so im doing with map
data.map(function(dataValue) {
var tr = document.createElement('tr');
var td1 = document.createElement('td');
var text1 = document.createTextNode(dataValue.stockcode);
var text2 = document.createTextNode(dataValue.id);
var td2 = document.createElement('td');
td1.appendChild(text1);
td2.appendChild(text2);
tr.appendChild(td1);
tr.appendChild(td2);
var tbody = document.getElementById('table1')
tbody.appendChild(tr);
})
}
}
});
I dont test this code but its something like this. If you want to try, you can check a example here:
I hope this be useful. Happy code!!
Assuming you have all of the correct scripts and styles loaded, there are a few issues with your existing code.
First, the routes don't make sense. Your homepage / and /posts are both pointed at the same controller method PostController@index, and the AJAX calls the / route again, which is now supposed to return a JSON response?
Make yourself another controller, lets call it PageController.
class PageController extends Controller
{
public function index()
{
return view('index');
}
}
Then lets change your routes to:
// pages
Route::get('/', 'PageController@index');
// posts
Route::resource('posts', 'PostController');
Next, let's create another blade file to make it easier to manage your table layout. You accidentally had a closing </body> tag instead of </tbody> which would not work out well for you. This would be easier to catch with code styling as opposed to being defined in the PHP as a string.
/resources/views/layouts/table.blade.php
<table id='table1' class='cell-border wrap' cellspacing='0' width='100%'>
<thead>
<tr>
<th>Test</th>
</tr>
</thead>
<tbody>
@foreach ($getNote2 as $val)
<tr>
<td>{{ $val->stockcode }}</td>
</tr>
@endforeach
</tbody>
</table>
Then clean up your PostController. We'll actually render the new table blade file right here in the controller using the View facade.
use \Illuminate\Support\Facades\View;
class PostController extends Controller
{
public function index()
{
$getNote2 = DB::connection('mysql')->table('note2')->select('stockcode', 'note')->orderBy('stockcode', 'ASC')->get();
$display = View::make('layouts.table',compact('getNote2'))->render();
return response()->json($display);
}
}
And finally complete your layout. This assumes you have a scripts section defined in your /resources/views/layouts/app.blade.php file. And we'll change the AJAX to call the /posts route in order to receive our JSON response.
/resources/views/index.blade.php
@extends('layouts.app')
@section('content')
<div class="table1display"></div>
@endsection
@section('scripts')
<script>
$(document).ready( function () {
$.ajax({
url: "/posts",
type: "GET",
dataType: "json",
success: function(data) {
// set your target node
var target = $('.table1display');
// log to console
console.log(data);
// hide table prior to styling
target.hide();
// load data in table
target.html(data);
// initialize DataTable
initializeDataTable();
// show table
target.show();
}
});
});
function initializeDataTable() {
$('#table1').DataTable();
}
</script>
@endsection
And I think that should be it. Hope this helps and good luck!
EDIT
You asked:
Is it possible to pass sql and mysql queries as json on submit button based on value inserted?
You never want to pass raw queries from the client side because anyone could change the query and steal, alter, or trash your data.
However, you can certainly submit new form values and build the query on the server side.
I have not tested this code, but I imagine you would do something like this:
@extends('layouts.app')
@section('content')
<!-- Form -->
<form id="my_form">
<input type="text" name="my_data" value=""/>
<button>Submit</button>
</form>
<!-- End Form -->
<!-- Datatable -->
<div class="table1display"></div>
<!-- End Datatable -->
@endsection
@section('scripts')
<script>
// wait until document is fully loaded
$(document).ready( function () {
// update table with default form params
updateTableData();
});
// listen for changes on input if you want to submit instantly
// however, I really recommend you find and use a library
// such as lodash to debounce the inputs if you are going to do this
$('input[name="my_data"]').on('keyup',function(e) {
('#my_form').submit();
});
// listen for form submission
$('#my_form').submit(function(e) {
// prevent the default form action since we didn't define one
// otherwise it may try to reload the current page
e.preventDefault();
// update table with new form params
updateTableData();
});
// we'll break this out into a self contained function that
// takes the existing form data and builds a query accordingly
function updateTableData() {
// get the form data
var data = $('#my_form').serialize();
// your ajax request
$.ajax({
url: "/posts?" + data,
type: "GET",
dataType: "json",
success: function(data) {
// call our success function
successUpdateTable(data);
}
});
}
// now that you have the data
// perform the actions to update the table
function successUpdateTable(data) {
// set your target node
var target = $('.table1display');
// log response to console
console.log(data);
// hide table prior to styling
target.hide();
// empty the table contents
target.empty();
// load data in table
target.html(data);
// initialize DataTable
$('#table1').DataTable();
// show table
target.show();
}
</script>
@endsection
Then you would need to handle the filtering on the server side via the Request received.
use \Illuminate\Http\Request;
use \Illuminate\Support\Facades\View;
class PostController extends Controller
{
public function index(Request $request)
{
// validate your request
// to make sure you received what you expected
$request->validate([
'my_data' => 'required'
]);
// create your filter based on the request values
// I cannot write this for you as only you know your data
$getNote2 = '...';
// render the table element
$display = View::make('layouts.table',compact('getNote2'))->render();
// send your response
return response()->json($display);
}
}