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!
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