viernes, 16 de noviembre de 2012

Tutorial JqGrid + PHP + MYSQL

Siguiendo los cursos de JqGrid, ahora voy a mostrarles como hacer para montar el lado del servidor con PHP y MYSQL.

JqGrid es un sistema muy poderoso pero tiene la limitante que en modo data local máximo puede contener mil registros por las limitantes de Javascript, pero conectado directamente con un servidor que se encargue de todos los procesos, puede contener el numero de registros que necesitemos. Lo cual nos permite almacenar tranquilamente 20.000 filas de elementos sin ningún problema.

En este tutorial explicamos como conectar JqGrid con mysql, para cargar los datos JqGrid requiere para poder realizar la consulta ajax, que el archivo este en formato json o xml, en este tutorial vamos a explicar como se usa JqGrid en formato json, para cargar la data desde un servidor en php.



Formato de la solicitud AJAX
La solicitud ajax echa por JqGrid al servidor tiene estos parámetros:

  • nd: es simplemente la hora en la que se hizo la solicitud.
  • rows: el numero de lineas que se va a mostrar al usuario.
  • page: la pagina que se va a consultar en el momento.
  • sidx: el elemento por el cual se va a ordenar la consulta.
  • sord: el tipo de orden de la tabla asc o desc.
  • _search: esto es un bolleano que contiene false o true en el caso de que se se use la barra de filtros y se este buscando algo en especifico.
  • Listas de elementos: cada uno de los index de los elementos que usemos en la búsqueda van a salir de la filter toolbar también serán enviados como parámetros.


Formato del archivo JSON
El archivo json por el servidor debe contener los siguientes atributos:

  • page: La pagina actual que esta mostrando.
  • records: el numero de registros que hay en total en la tabla.
  • rows: las filas que vamos a devolver actualmente al sistema
  • total: el numero de paginas en total que hay en el sistema.
Conectando JqGrid con PHP y Mysql.
El proceso para hacer el puente de la información es:
  1. Primero hacemos lo normal y nos conectamos a la base de datos. 
  2. Después guardamos en un array todos los elementos de la solicitud que hace JqGrid.
  3. Construimos la consulta de filtrado para la barra de búsqueda. para construir la consulte lo dividi en dos partes el area de los like y el área de los equal, porque hay campos como los nombres que la búsqueda se puede parecer y hay campos como un total que tienen que ser exactos, pero ya cada uno podrá elegir como prefiera.
  4. Hacemos una consulta a la base de datos para saber el numero de filas que hay en total. 
  5. Calculamos el numero de paginas que hay, y averiguamos si la pagina es la correcta.
  6. Construimos la consulta añadiéndole los filter, los search y el orden.
  7. Hacemos la consulta para traernos los resultados que necesitamos en especifico.
  8. Por ultimo le devolvemos los datos al JqGrid.
Aquí esta el código explicado:
(isset($_REQUEST['rows']))?$_REQUEST['rows']:'',
  'page'=>(isset($_REQUEST['page']))?$_REQUEST['page']:'',
  'orderby'=>(isset($_REQUEST['sidx']))?$_REQUEST['sidx']:'',
  'orden'=>(isset($_REQUEST['sord']))?$_REQUEST['sord']:'',
  'search'=>(isset($_REQUEST['_search']))?$_REQUEST['_search']:'',
 );
 $se ="";
 //creamos la consulta de busqueda. 
 if($post['search'] == 'true'){
  $b = array();
  //Usamos la funci{on elements para crear un arreglo con los datos que van a ser para buscar por like
  $search['like']=elements(array('total','nota'),$_REQUEST);
  //haciendo un recorrido sobre ellos vamos creando al consulta.
  foreach($search['like'] as $key => $value){
   if($value != false) $b[]="$key like '%$value%'";
  }
  //Usamos la funci{on elements para crear un arreglo con los datos que van a ser para buscar por like
  $search['where']=elements(array('fecha','cantidad','taza','cliente'),$_REQUEST);
  //haciendo un recorrido sobre ellos vamos creando al consulta.
  foreach($search['where'] as $key => $value){
   if($value != false) $b[]="$key = '$value'";
  }
  //Creamos la consulta where
  $se=" where ".implode(' and ',$b );  
 }
 //Realizamos la consulta para saber el numero de filas que hay en la tabla con los filtros
 $query = mysql_query("select count(*) as t from inventario".$se);
 if(!$query)
  echo mysql_error();
 $count = mysql_result($query,0);

 if( $count > 0 && $post['limit'] > 0) {
  //Calculamos el numero de paginas que tiene el sistema
  $total_pages = ceil($count/$post['limit']);
  if ($post['page'] > $total_pages) $post['page']=$total_pages;
  //calculamos el offset para la consulta mysql.
  $post['offset']=$post['limit']*$post['page'] - $post['limit'];
 } else {
  $total_pages = 0;
  $post['page']=0;
  $post['offset']=0;
 }
 //Creamos la consulta que va a ser enviada de una ves con la parte de filtrado
 $sql = "select id, fecha, cliente, cantidad, taza, total, nota from inventario  ".$se;
 if( !empty($post['orden']) && !empty($post['orderby']))
  //Añadimos de una ves la parte de la consulta para ordenar el resultado
  $sql .= " ORDER BY $post[orderby] $post[orden] ";
 if($post['limit'] && $post['offset']) $sql.=" limit $post[offset], $post[limit]";
  //añadimos el limite para solamente sacar las filas de la apgina actual que el sistema esta consultando
  elseif($post['limit']) $sql .=" limit 0,$post[limit]";
 
  
 $query = mysql_query($sql);
 if(!$query)
  echo mysql_error();
 $result = array();
 $i = 0;
 while($row = mysql_fetch_object($query)){
   $result[$i]['id']=$row->id;
  $result[$i]['cell']=array($row->id,$row->fecha,$row->cliente,$row->cantidad,$row->taza,$row->total,$row->nota);
  $i++;
  
 }
 //Asignamos todo esto en variables de json, para enviarlo al navegador.
 
 $json = new stdClass();
 $json->rows=$result;
 $json->total=$total_pages;
 $json->page=$post['page'];

 $json->records=$count;
 echo json_encode($json);
 
 mysql_close($link);
 
 function elements($items, $array, $default = FALSE)
 {
  $return = array();
  if ( ! is_array($items)){
   $items = array($items);
  }
  foreach ($items as $item){
   if (isset($array[$item])){
    $return[$item] = $array[$item];
   }else{
    $return[$item] = $default;
   }
  }
  return $return;
 }
?>
Este es el resultado final: http://bomba1990.pythonanywhere.com/sosinformatico/js/JqGrid_Basico4
Codigo Github: https://github.com/bomba1990/JqGrid-Ejemplos/blob/master/JqGrid-Basico-4-DB.html
https://github.com/bomba1990/JqGrid-Ejemplos/blob/master/ajax.php

Suerte y espero que les sea de utilidad a todos los que lo solicitaron.

31 comentarios:

  1. Mariano, soy Nacho de Workana. Mi skype es nachobuey. Nos hablamos

    ResponderEliminar
  2. Quisiera compartir mis avances con jqgrid mi pagina es:

    https://plus.google.com/116785059505984459570/posts

    David Rafael Blanco Leon

    ResponderEliminar
  3. Hola estoy usando una grilla y quisiera agregar una funcion para recargarla con un boton, segun he visto esto no se logra al menos que el data sea 'json' o xml, yo estoy usando un arreglo en el que estoy obteniendo los datos al cargar con php. Se lo agradeceria si pudiera ayudarme

    ResponderEliminar
    Respuestas
    1. que quieres hacer en especifico, añadir nuevos datos a la grilla, porque para esto si hay funciones cuando estas trabajando con un array

      Eliminar
  4. Hola recien inicio en jqgrid, me pueden ayudar con un ejemplo de como añdadir,modificar y eliminar registros con una base de datos mysql, ya he logrado ver los datos pero cuando guardo o modifico me dice no ha especificado url, gracias

    ResponderEliminar
  5. este es mi correo por si me pueden enviar algun archivo, ya que no he logrado hacer que los de la red guarden, modifiquen y eliminen los registros de la tabla, creo que se debe hacer algunos archivos en php para acceder a la bases de datos y todo lo demas, pero no veo cual sea el que modifique los datos, gracias de antemano lo necesito para un proyecto, jperez_barrios@hotmail.com

    ResponderEliminar
  6. hola muy buen tuto!!!
    pero me pasa algo a mi muy peculiar
    tal parece

    tengo un campo en mi base de datos llamado
    title varchar (255) null utf8_general_ci

    resulta que es el unico campo que no me quiere mostrar en l tabla
    de jqgrid es mas cuando lo activo de dejan de ver los demas
    lo quito y se ven todos
    sabes por que es ???


    //Creamos la consulta que va a ser enviada de una ves con la parte de filtrado
    $sql = "select id,title from e8ogb_k2_items".$se;
    if( !empty($post['orden']) && !empty($post['orderby']))


    $result[$i]['cell']=array($row->id,$row->title);

    ResponderEliminar
    Respuestas
    1. creo que es un problema del acento. a mi hace tiempo me paso, que cuando venian acentos daba error. creo que con htmlentities si usas php lo solucionas.

      Eliminar
  7. ya has probado que la consulta select id, title from e8ogb_k2_items por si sola funcione. pienso que es probable que el campo no pertenezca, no veo otra razón por la cual no te lo muestre

    ResponderEliminar
  8. estoy usando jqgrid, conectado con una base de datos mysql, resulta que una vez cargados los datos desde la BD, cuando hago click sobre el nombre de la columna(para ordenar por esa columna) hace el evento deordenar pero no ordena, podrias decirme como mirar este tema?

    ResponderEliminar
    Respuestas
    1. podrias montar tu eejemplo en jsfiddle? para poder comprobarlo mejor.

      Eliminar
  9. Estoy probando el ejemplo en mysql y no me esta mostrando nada y me da el error en el php
    $json->rows=$result;
    $json->total=$total_pages;
    $json->page=$post['page'];
    $json->records=$count;

    ResponderEliminar
  10. necesito hacer un grid, pero el codigo antes explicado es en un solo archivo o se debe seccionar en ajax, css, grid; la programacion no es mi fuerte. te agradeceria si me pudieras contestar mi pregunta

    ResponderEliminar
    Respuestas
    1. Son varios archivos, php que es el que devuelve los datos.

      css, js y html. en el github estan todos juntos.

      Eliminar
  11. que archivo ejecutas para que te muestre el jqgrid??? como lo haces??

    ResponderEliminar
  12. Buenas noches, no se si alguien me puede ayudar, ya monte la base de datos en el mysql, modifique el archivo ajax.php con las credenciales de mi conexion, ya revise y la consulta si me retorna datos pero el grid no las esta capturando. me gustaria saber si hay algo que estare obviando

    ResponderEliminar
    Respuestas
    1. deberias enviar un print screen. asi te puedo ayudar mejor.

      Eliminar
    2. A mi ancontece lo mismo... no arroja ningun tipo de errror , pero la grilla no muestra nada....

      Eliminar
    3. Si directo al archivos ajax.php me aparece los datos de la base...

      {"rows":[{"id":"1","cell":["1","2012-11-16","Elias","200","10","220","se fue sin pagar,,,,elias"]},{"id":"2","cell":["2","2012-02-10","test","300","1","480",""]},{"id":"3","cell":["3","2012-02-10","test","300","1","480",""]},{"id":"4","cell":["4","2012-02-10","test","0","0","0",""]},{"id":"5","cell":["5","2012-02-10","test","169","19","4883",""]},{"id":"6","cell":["6","2012-02-10","test","280","2","3946",""]},{"id":"7","cell":["7","2012-02-10","test2","167","12","2148",""]},{"id":"8","cell":["8","2012-02-10","test2","291","17","2224",""]},{"id":"9","cell":["9","2012-02-10","test2","68","9","2631",""]},{"id":"10","cell":["10","2012-02-10","test2","134","1","114",""]},{"id":"11","cell":["11","2012-02-10","test2","215","2","1659",""]},{"id":"12","cell":["12","2012-02-10","test2","197","19","4014",""]},{"id":"13","cell":["13","2012-02-10","test3","288","8","749",""]},{"id":"14","cell":["14","2012-02-10","test3","177","15","265",""]},{"id":"15","cell":["15","2012-02-10","test3","248","17","3546",""]},{"id":"16","cell":["16","2012-02-10","test3","121","16","3657",""]},{"id":"17","cell":["17","2012-02-10","test3","145","17","3843",""]},{"id":"18","cell":["18","2012-02-10","test3","116","4","3966",""]}],"total":0,"page":0,"records":"18"}

      Eliminar
  13. Buenas tades, tengo un problema con una grilla jqgrid. Utilizo php y postgres. Tengo una tabla muy grande de mas de 100000 registros pero la grilla solo me carga hasta 74766 registros. Que puedo hacer?
    $query = 'SELECT nombre, asignacion, saldo, ciudad, tel1 FROM py_cartera LIMIT 74766';
    $stmt = $this->dbh->prepare($query);

    ResponderEliminar
  14. Esa consulta es sólo un ejemplo. El límite que pongo ahí es el número máximo de registros que me muestra la grilla. Si pongo un número mayor a ese, no me muestra nada, sale en blanco

    ResponderEliminar
  15. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  16. Hay alguna forma de aumentar el número de registros que muestra la grilla? Con tablas pequeñas me funciona muy bien pero tengo una tabla de 100000 registros y con esa tabla me muestra en blanco al consultar toda la tabla, si le pongo un límite en la consulta menor a 74766 ahí si me muestra datos

    ResponderEliminar
  17. tienes que hacer paginación ajuro. del lado del servidor. or eso la pregunta. por eso ves que en mi codigo hago limit y offset.

    obviamente si muestras tantos registros de un solo golpe la tabla no podra manejarlos.

    ResponderEliminar
  18. Ejecute el codigo y no me muestra nada. Probando el php me lanza este error
    Warning: Creating default object from empty value in C:\xampp\htdocs\JqGrid-Ejemplos-master\ajax.php on line 78
    donde la linea 78 es:
    $json->rows=$result;
    que podria estar fallando?

    ResponderEliminar
  19. Halle la solucion al objeto vacio, lo dejo por si le sirve a alguien mas.....

    Antes de la linea
    $json->rows=$result;

    debe definirse la variable que se esta utilizando, por lo que quedaria de esta manera:

    $json = new stdClass();
    $json->rows=$result;

    ResponderEliminar
    Respuestas
    1. Muchas gracias por los comentarios, ya edite el código.

      Eliminar