Display information for a specific user type
Display information for a specific user type
rodrigo2324
Posts: 5Questions: 1Answers: 0
Hello, sorry for my bad english,
I am new to the forum and also using Datatables
I hope you can help me with a query that I have:
I have a table called "users", and inside my "users" table I manage a type of user (user_type), where I indicate that if the type is equal to 1, then it is an administrator, if it is type 2, is and user normal .
Well, I would like to know, how do I show in my datatable only the data of the users that are normal (user_type = 2) without showing the administrators? Ie I want to see only users who are type 2 in my datatable.
I have tried several ways to create the condition but I do not succeed. I hope they can help me.
My code I have it as follows:
A script specifically in the index.php as follows
**<script>
$(document).ready(function(){
$("tr:odd").css("background-color", "#fff"); // filas impares
$("tr:even").css("background-color", "#dddddd"); // filas pares
$('#mitabla').DataTable({
"order": [[0, "asc"]],
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_process.php",
"dom": "<'row'<'col-sm-3'l><'col-sm-6 text-center'B><'col-sm-3'f>>" + "<'row'<'col-sm-12'tr>>" + "<'row'<'col-sm-5'i><'col-sm-7'p>>",
//traduccion
"language":{
"lengthMenu": "Mostrar _MENU_ registros por pagina",
"info": "Mostrando pagina _PAGE_ de _PAGES_",
"infoEmpty": "No hay registros disponibles",
"infoFiltered": "(filtrada de _MAX_ registros)",
"loadingRecords": "Cargando...",
"processing": "Procesando...",
"search": "Buscar:",
"zeroRecords": "No se encontraron registros coincidentes",
"buttons": {
"colvis": "OCULTAR COLUMNAS",
},
"paginate": {
"next": "Siguiente",
"previous": "Anterior"
}
},
//botones para exportar
buttons: [
{
extend: 'print',
text: 'IMPRIMIR',
exportOptions: {
columns: ':visible'
}
},
{
extend: 'excelHtml5',
text: 'EXCEL',
exportOptions: {
columns: ':visible',
modifier: {
page: 'current'
}
}
},
{
extend: 'pdfHtml5',
text: 'PDF',
orientation: 'portrait',
pageSize: 'LETTER',
title: 'Reporte de Usuarios',
exportOptions: {
columns: ':visible',
modifier: {
page: 'current'
}
},
},
'colvis'
],
columnDefs: [ {
visible: false
} ]
} );
} );
</script>
**
.
.
.
.
.
And I have my Server_process.php as follows
.
.
.
**<?php
require 'conexion.php';
/* Nombre de La Tabla */
$sTabla = "usuarios";
/* Array que contiene los nombres de las columnas de la tabla*/
$aColumnas = array('nombres', 'apellidos', 'cedula', 'telefono','correo');
/* columna indexada */
$sIndexColumn = "cedula";
// Paginacion
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".$_GET['iDisplayStart'].", ".$_GET['iDisplayLength'];
}
//Ordenacion
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumnas[ intval( $_GET['iSortCol_'.$i] ) ]."
".$_GET['sSortDir_'.$i] .", ";
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
//Filtracion
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumnas) ; $i++ )
{
$sWhere .= $aColumnas[$i]." LIKE '%".$_GET['sSearch']."%' OR ";
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
// Filtrado de columna individual
for ( $i=0 ; $i<count($aColumnas) ; $i++ )
{
if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumnas[$i]." LIKE '%".$_GET['sSearch_'.$i]."%' ";
}
}
//Obtener datos para mostrar SQL queries
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumnas))."
FROM $sTabla
$sWhere
$sOrder
$sLimit
";
$rResult = $mysqli->query($sQuery);
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = $mysqli->query($sQuery);
$aResultFilterTotal = $rResultFilterTotal->fetch_array();
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTabla
";
$rResultTotal = $mysqli->query($sQuery);
$aResultTotal = $rResultTotal->fetch_array();
$iTotal = $aResultTotal[0];
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = $rResult->fetch_array())
{
$row = array();
for ( $i=0 ; $i<count($aColumnas) ; $i++ )
{
if ( $aColumnas[$i] == "version" )
{
/* Special output formatting for 'version' column */
$row[] = ($aRow[ $aColumnas[$i] ]=="0") ? '-' : $aRow[ $aColumnas[$i] ];
}
else if ( $aColumnas[$i] != ' ' )
{
/* General output */
$row[] = $aRow[ $aColumnas[$i] ];
}
}
$row[] = " <td><a href='subir.php?cedula=".$aRow['cedula']." 'title='Subir Historia' '><span class='glyphicon glyphicon-circle-arrow-up' style='color:green'></span></a></td>
<td><a href='modificar.php?cedula=".$aRow['cedula']." 'title='Modificar Informacion' '><span class='glyphicon glyphicon-pencil'></span></a></td>
<td><a href='#' data-href='eliminar.php?cedula=".$aRow['cedula']." 'title='Eliminar Paciente' ' data-toggle='modal' data-target='#confirm-delete'><span class='glyphicon glyphicon-trash' style='color:red'></span></a></td>";
$output['aaData'][] = $row;
}
echo json_encode( $output );
?>
**
.
.
.
.
.
I hope they can help me with my query since as I said at the beginning, I can not solve the problem and more than my knowledge are just the basics with respect to Datatables.
To whom you can help me, I thank you very much! and again, sorry for my bad english!
This discussion has been closed.
Answers
Someone who can help me? Reading the documentation found something but it did not work for me
I want to better understand your question, where is the user type being selected?
Ted
Hi hiswillpower, thanks for answering.
In my code I have not called the user type yet.
That is the question, how could I call the type of user in the code and thus display in the datatable only the users that are of a specific type?
I thought I would call the type of user through a select, but I did not go out or just did not find it where it was.
Seeing my code, I hope you can help me with my doubt regarding just showing the data of a specific user type (administrator 1 or user 2).
I suppose that the validation so that it only shows the data of a type of user should be in server_process.php?
rodrigo2324:
You have data tables setup for server side processing so all of our queries are done on the server side. I would modify the WHERE clause to include your condition.
// Filtrado de columna individual
Specifically the line that starts with $sWHERE = $aCol....
Ted
But what you tell me is for the search for specific columns.
What I want to do is that when starting the datatable only the users who are of a type are left.
What I'm doing is a CRUD for patients. It is a clinical system, and when I enter the CRUD of patients, I want it to be only patient (user_type 2). For now I have a CRUD where all the users of my users table (administrators, patients ..) and if it is only a CRUD of patients, should only show users type 2 of my users table and is what I can not do.
your WHERE clause controls what you fetch from your database when you use server side processing, it should be AND user_type == 1 or something like that. Of course your script will have to pass the user_type.
Hello!
Solved!!!
The line I needed was $sWhere = "WHERE user_type=2 ";
And place it exactly here