I found a tutorial on the web about searching, listing, paging and the option to show 10 rows or 25, 50 or all, in the tutorial they listed data of a worker, their id, name, salary and agency number.
The data was listed as follows:
This is the HTML structure
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<!-- jQuery -->
<title>Lista de noticias</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jquery-bootgrid/1.3.1/jquery.bootgrid.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-bootgrid/1.3.1/jquery.bootgrid.js"></script>
<script src="ajax.js"></script>
</head>
<body class="">
<div role="navigation" class="navbar navbar-default navbar-static-top">
<div class="container">
<div class="navbar-header">
<button data-target=".navbar-collapse" data-toggle="collapse" class="navbar-toggle" type="button">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a href="#" class="navbar-brand">My Site</a>
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li class="active"><a href="index.php">Home</a></li>
</ul>
</div><!--/.nav-collapse -->
</div>
</div>
<div class="container" style="min-height:500px;">
<div class=''></div>
<div class="container">
<h2>Listado de Noticias</h2>
<br>
<br>
<table id="news" data-toggle="bootgrid" class="table table-condensed table-hover table-striped" width="100%" cellspacing="0">
<thead>
<tr>
<th data-column-id="id_news" data-type="numeric">Id</th>
<th data-column-id="title">Titulo</th>
<th data-column-id="description">Descripción</th>
<th data-column-id="url">URL</th>
</tr>
</thead>
</table>
<div class="insert-post-ads1" style="margin-top:20px;">
</div>
</div>
</body>
</html>
I like the system, it's simple, you don't have to add so much extensive code javascript
to add advanced features... The only work is to create a PHP JSON file according to the system.
Returning to the topic of javascript
, the only configurations that need to be implemented are simple code Ajax
.
<script>
$(function() {
$("#news").bootgrid({
ajax: true,
url: "miarchivo.php"
});
});
</script>
A big headache is the PHP code, it is very confusing, difficult for me to explain to others especially to understand it.
This is the actual PHP code from the tutorial.
<?php
//include connection file
include_once("connection.php");
// initilize all variable
$params = $totalRecords = $data = array();
$sqlTot = $sqlRec = $where = "";
$params = $_REQUEST;
$limit = $params["rowCount"];
if (isset($params["current"])) { $page = $params["current"]; } else { $page=1; };
$start_from = ($page-1) * $limit;
// check search value exist
if( !empty($params['searchPhrase']) ) {
$where .=" WHERE ";
$where .=" ( employee_name LIKE '".$params['searchPhrase']."%' ";
$where .=" OR employee_salary LIKE '".$params['searchPhrase']."%' ";
$where .=" OR employee_age LIKE '".$params['searchPhrase']."%' )";
}
if( !empty($params['sort']) ) {
$where .=" ORDER By ".key($params['sort']) .' '.current($params['sort'])." ";
}
// getting total number records without any search
$sql = "SELECT * FROM `employee` ";
$sqlTot .= $sql;
$sqlRec .= $sql;
//concatenate search sql if value exist
if(isset($where) && $where != '') {
$sqlTot .= $where;
$sqlRec .= $where;
}
if ($limit!=-1)
$sqlRec .= "LIMIT $start_from, $limit";
$queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));
$totalRecords = mysqli_num_rows($queryTot);
$queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch employees data");
//iterate on results row and create new index array of data
while( $row = mysqli_fetch_assoc($queryRecords) ) {
$data[] = $row;
//echo "<pre>";print_R($data);die;
}
$json_data = array(
"current" => intval( $params['current'] ),
"rowCount" => 10,
"total" => intval( $totalRecords ),
"rows" => $data // total data array
);
echo json_encode($json_data); // send data as json format
?>
This is another example https://ideone.com/eOQl7f found in the same tutorial, which I based on testing with my news logs
id_news url title description
1 .... un titulo ........
changing query data correctly
$sql_query = "SELECT id_news as emp_id, url as emp_name, title as emp_salary, description as emp_age FROM `news` ";
But it doesn't list any data.
Update
Following the examples given by Dev. Joel , I keep having problems that do not reflect results, that is, it does not list anything.
Following each step of the answer, there are additional configurations to add, such as installing mysqlnd
or at the same time using an alternative, through one function
that fulfills the same functions, although there is another way, another alternative using the bind_result
traditional one.
Apart from not understanding the code so specifically, it will surely cause me problems in the future, when trying to add certain extra conditions to the query usingWHERE .... AND ... AND
My proposal is the following, maybe it is more extensive or maybe not, but the reading is easy and friendly for my vision...
myfile.php
<?php
$ServerName = "localhost";
$Username = "";
$PassWord = "";
$DataBase = "";
$con = new mysqli($ServerName, $Username, $PassWord, $DataBase);
if ($con->connect_error) {
exit("Error de conexión: " . $con->connect_error);
}
if (!$con->set_charset("utf8")) {
printf("Error cargando el conjunto de caracteres utf8: %s\n", $con->error);
exit();
}
/********************************/
//Buscador de datos de los registros
$search = $_POST['searchPhrase']; //"%{$_POST['searchPhrase']}%";
$stmt = $con->prepare("SELECT id_news,title,url FROM news WHERE title LIKE CONCAT('%',?,'%')");
$stmt->bind_param("s", $search);
$stmt->execute();
$stmt->bind_result($id_news,$title);
/*
while ($stmt->fetch()) {
echo "Id: {$id_news}, title: {$title}";
}
*/
//Total de los registros registrados
$active = 1;
$stmtA=$con->prepare("SELECT COUNT(*) FROM news WHERE active=?");
$stmtA->bind_param("i",$active);
$stmtA->execute();
$totalRecords = null;
$stmtA->bind_result($totalRecords);
$stmtA->fetch();
$stmtA->close();
//Mostrar datos registrados
$stmtB = $con->prepare("SELECT id_news,title,url FROM news WHERE active = ?");
$stmtB->bind_param("i", $active);
$stmtB->execute();
$rows = array();
$stmtB->bind_result(
$rows['id_news'],
$rows['title'],
$rows['url']);
while ($stmtB->fetch()) {
$data = $rows;
}
$json_data = array(
"current" => intval($params['current']),
"rowCount" => 10,
"total" => intval($totalRecords),
"rows" => $data
);
echo json_encode($json_data);
?>
Following my own work, this prints me the following result
{"current":0,"rowCount":10,"total":85,"rows":{"id_news":103,"title":"Mi primera prueba de insertar noticia mediante Ajax","url":"mi-primera-prueba-de-insertar-noticia-mediante-ajax"}}
The results are favorable but it is not consistent with the system bootgrid
, despite having the results satisfactorily, it does not print them in the list design, and it is because it needs to be adapted and that is my biggest problem.
You have to add the settings for pagination, search engine, row limitation and adapt it to the functions of bootgrid
and all this must be configured in the PHP file, in myfile.php.
Note: The PHP codes in the tutorial work if they list the records in the layout
bootgrid
.
I will add a base example to obtain the data that bootgrid requires to work correctly , if we review the examples it expects a
json
with the following data.This data would be built from
PHP
, concatenating the query before preparing it. the code is explainedHTML note the name of the columns that are according to your table.
PHP (Code to be improved ;) according to what you need for your application)
If you don't have the driver installed
mysqlnd
, you can use a function to get the data ReferenceSo the part I would change from the full code above, would be to replace this
For this
JS Start
bootgrid
Since you modified your question, Yes. It is an alternative to use
bind_result
but with variables and add the object as key value to thearray
response.One bug in your code is that
$data = $rows;
at the end of the loop, $data will only have the last record.The final part of the code would remain.
If you don't understand the code, comment that you don't understand so it will be explained to you. the "complexity" is because of the parameters that the library expects.