I have a textbox for users to insert their SQL statement. I need to sort my DataTables based on the Asc and Desc in that SQL statement. When users see the datatable content it will either be in Asc or Desc depending on whatever they type in the SQL statement. How do I go about with it
<label> Query </label>
<textarea class="form-control" name="sql_input" rows="5" id="comment" placeholder="Select statement input here..."><?php if (isset($_POST['submit'])){ echo $_POST['sql_input'];} ?></textarea>
<br><label> X-Axis </label> <input type="text" name = "xaxis" class="form-control" id="xaxis" placeholder="X Axis e.g `id`" value = <?php if (isset($_POST['submit'])){ echo $_POST['xaxis'];} ?>>
<br><label> Y-Axis </label> <input type="text" name = "yaxis" class="form-control" id="yaxis" placeholder="Y Axis e.g `created_by`" value = <?php if (isset($_POST['submit'])){ echo $_POST['yaxis'];} ?>>
<table class="table table-striped table-bordered table-hover" id="dataTable_table">
<thead>
<tr>
<?php
//generate query
$query = $row['sql_statement'];
$result = $conn_temp->query($query);
while($row1 = $result->fetch_assoc())
{
foreach((array)$row1 as $key=>$val)
{
?>
<th><?php echo $key ?></th>
<?php
}
break;
}
?>
</tr>
</thead>
<tbody>
<?php
//generate query
$query = $row['sql_statement'];
$result = $conn_temp->query($query);
while($row1 = $result->fetch_assoc())
{ ?>
<tr>
<?php
foreach((array)$row1 as $key=>$val)
{ ?>
<th><?php echo $val ?></th>
<?php
} ?>
</tr>
<?php
} ?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
<link href="scripts/dataTables/dataTables.bootstrap.css" rel="stylesheet">
<script src="scripts/dataTables/jquery.dataTables.js"></script>
<script src="scripts/dataTables/dataTables.bootstrap.js"></script>
<script>
$.fn.dataTable.ext.search.push(
function( settings, data, dataIndex ) {
var select_value = $('#select_value').val();
var select_column = $('#select_column').val();
var column_index = '';
//var column = data[1] || 0; // use data for the age column
if ( select_value == '' || select_column == '' )
{
return true;
}
else {
//get the column name of data table
var column = 0;
$('#dataTable_table thead tr th').each(function(){
if( $(this).text() == select_column.toString())
{
return false;
}
column = column + 1;
});
column = data[column] || 0;
if(column!==0 && column.indexOf(select_value.toString()) >= 0)
{
return true;
}
}
return false;
}
);
$.fn.dataTableExt.sErrMode = 'throw';
$(document).ready(function () {
var table = $('#dataTable_table').DataTable();
$('#select_value').keyup( function() {
table.draw();
});
});
</script>