Display information for a specific user type

Display information for a specific user type

rodrigo2324rodrigo2324 Posts: 5Questions: 1Answers: 0
edited July 2017 in Free community support

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[] = "&nbsp;&nbsp; <td><a href='subir.php?cedula=".$aRow['cedula']."   'title='Subir Historia'    '><span class='glyphicon glyphicon-circle-arrow-up' style='color:green'></span></a></td> &nbsp;&nbsp;

    <td><a href='modificar.php?cedula=".$aRow['cedula']."   'title='Modificar Informacion'    '><span class='glyphicon glyphicon-pencil'></span></a></td> &nbsp;&nbsp;


    <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 );
<?php > ** ``` . . . . . ?>

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

  • rodrigo2324rodrigo2324 Posts: 5Questions: 1Answers: 0
    edited July 2017

    Someone who can help me? Reading the documentation found something but it did not work for me :(

  • hiswillpowerhiswillpower Posts: 9Questions: 1Answers: 1

    I want to better understand your question, where is the user type being selected?
    Ted

  • rodrigo2324rodrigo2324 Posts: 5Questions: 1Answers: 0

    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?

  • hiswillpowerhiswillpower Posts: 9Questions: 1Answers: 1

    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

    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]."%' ";
        }
    }
    

    Specifically the line that starts with $sWHERE = $aCol....

    Ted

  • rodrigo2324rodrigo2324 Posts: 5Questions: 1Answers: 0

    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.

  • hiswillpowerhiswillpower Posts: 9Questions: 1Answers: 1

    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.

  • rodrigo2324rodrigo2324 Posts: 5Questions: 1Answers: 0

    Hello!

    Solved!!!

    The line I needed was $sWhere = "WHERE user_type=2 ";

    And place it exactly here

    $sWhere = " WHERE  user_type = 2 ";
        $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumnas))."
        FROM   $sTabla
        $sWhere
        $sOrder
        $sLimit
        ";
    
        $rResult = $mysqli->query($sQuery);
    
This discussion has been closed.