HOla I'm starting in this world I would like to know if someone can give me a hand with what I want to achieve, the idea is to load a table using json but it shows only the records that match some variable that I plan to bring from another page either by session variable or post or get (as for example it would be all the records that match the art number for my example). my load code is ...
$(document).on("click", ".open-AddBookDialog", function () {
var myUserName = $(this).data('art');
$(".modal-body #UserName").val( myUserName );
});
$(document).ready(function () {
$('#tabla_stk').DataTable({
"columns": [
{"data": "art"},
{"data": "descripcion"},
{"data": "marca"},
{"data": "modelo"},
{"data": "stock"},
{ // fifth column (Borrar link)
"sName": "Agregar",
"bSearchable": false,
"bSortable": false,
"mData": "art",
"mRender": function (data, type, full) {
//var id = full[2]; //row id in the first column
//return "<a href='javascript:alert(Borrar);'>Borrar</a>";
//return '<button type="button" data-id='+data+' class="open-AddBookDialog btn btn-default btn-sm" data-toggle="modal" data-target="#modal-deluser">Borrar</button>';
return '<a href="#" data-art='+data+' class="open-AddBookDialog" data-toggle="modal" data-target="#modal-addpre"><i class="fa fa-plus-circle fa-fw"></i></a>';
}
}
],
"processing": true,
"bLengthChange": false,
"bFilter": true,
"bpaginate": false,
"serverSide": true,
"bAutoWidth": false,
"iDisplayLength": "4",
"bInfo" : false,
"pagingType": "numbers",
"ajax": {
url: 'tablastk.php',
type: 'POST'
}
});
});
the other files tablastk.php that contains my connection and others.
if (!empty($_POST) ) {
/*
* Database Configuration and Connection using mysqli
*/
define("HOST", "localhost");
define("USER", "root");
define("PASSWORD", "");
define("DB", "bd");
define("MyTable", "tblrep");
$connection = mysqli_connect(HOST, USER, PASSWORD, DB) OR DIE("Impossible to access to DB : " . mysqli_connect_error());
/* END DB Config and connection */
/*
* @param (string) SQL Query
* @return multidim array containing data array(array('column1'=>value2,'column2'=>value2...))
*
*/
function getData($sql){
global $connection ;//we use connection already opened
$query = mysqli_query($connection, $sql) OR DIE ("Can't get Data from DB , check your SQL Query " );
$data = array();
foreach ($query as $row ) {
$data[] = $row ;
}
return $data;
}
/* Useful $_POST Variables coming from the plugin */
$draw = $_POST["draw"];//counter used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables
$orderByColumnIndex = $_POST['order'][0]['column'];// index of the sorting column (0 index based - i.e. 0 is the first record)
$orderBy = $_POST['columns'][$orderByColumnIndex]['data'];//Get name of the sorting column from its index
$orderType = $_POST['order'][0]['dir']; // ASC or DESC
$start = $_POST["start"];//Paging first record indicator.
$length = $_POST['length'];//Number of records that the table can display in the current draw
/* END of POST variables */
$recordsTotal = count(getData("SELECT * FROM ".MyTable));
/* SEARCH CASE : Filtered data */
if(!empty($_POST['search']['value'])){
/* WHERE Clause for searching */
for($i=0 ; $i<count($_POST['columns']);$i++){
$column = $_POST['columns'][$i]['data'];//we get the name of each column using its index from POST request
$where[]="$column like '%".$_POST['search']['value']."%'";
}
$where = "WHERE ".implode(" OR " , $where);// id like '%searchValue%' or name like '%searchValue%' ....
/* End WHERE */
$sql = sprintf("SELECT * FROM %s %s", MyTable , $where);//Search query without limit clause (No pagination)
$recordsFiltered = count(getData($sql));//Count of search result
/* SQL Query for search with limit and orderBy clauses*/
$sql = sprintf("SELECT * FROM %s %s ORDER BY %s %s limit %d , %d ", MyTable , $where ,$orderBy, $orderType ,$start,$length );
$data = getData($sql);
}
/* END SEARCH */
else {
$sql = sprintf("SELECT * FROM %s ORDER BY %s %s limit %d , %d ", MyTable ,$orderBy,$orderType ,$start , $length);
$data = getData($sql);
$recordsFiltered = $recordsTotal;
}
/* Response to client before JSON encoding */
$response = array(
"draw" => intval($draw),
"recordsTotal" => $recordsTotal,
"recordsFiltered" => $recordsFiltered,
"data" => $data
);
echo json_encode($response);
} else {
echo "NO POST Query from DataTable";
}
?>