[SOLUCIONADO] Logica para paginar con stored procedures

[flaviovich]

Hola amigos,
tengo un SP (stored procedure) en mysql 5.1, el cual me permite extraer informacion segun los parametros recibidos, entre los cuales tengo 2 que son para limitar la cantidad de registros (para la paginacion).
Mi duda es que no se si crear el mismo SP sin los 2 parametros para la paginacion ya que necesito primero saber la cantidad total de registros para poder saber cuantas paginas tendre.
Debo decir que no tengo mucha experiencia en mysql/php y tal vez no lo este haciendo de la forma correcta.
Espero sus recomendaciones!

[a18327]

¿Has intentado usar LIMIT?, por ejemplo
SELECT * FROM miTabla LIMIT 0,10

El primer numero indica cuantas filas se van a saltar, y el segundo numero indica cuantas filas vas a tomar, asi el ejemplo muestra las priemeras 10 filas ya que no se salta ninguna fila

¿Cuales son los dos parametros que utilizas para la paginacion?

[flaviovich]

Hola a18327, gracias por la pronta respuesta.
Este es mi procedimiento:

Código MySQL:

CREATE PROCEDURE `EncuestaRegistros` (IN Base VARCHAR(50), IN Estado VARCHAR(2),
    IN Asesor VARCHAR(30), IN Cliente VARCHAR(100), IN Desde INT, IN CantReg TINYINT UNSIGNED,
    OUT TotalRows INT UNSIGNED)
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
 
CREATE TEMPORARY TABLE temp
SELECT T.base,T.CLIENTE,T.FECHACIERREOR FECHACIERRE,T.ASESOR,T.resultados,T.fecha_grabacion,T.registro
FROM tabla1 T
INNER JOIN resultados R ON T.resultados = R.nombre_resultado
INNER JOIN categorias C ON R.id_cat_result = C.id_cat_result
WHERE T.base = concat('CCP-',upper(Base))
AND (C.id_cat_result = Estado OR Estado = '*')
AND (T.ASESOR = Asesor OR Asesor = '*')
AND (T.CLIENTE LIKE concat('%',Cliente,'%') OR Cliente = '');
 
SELECT COUNT(1) INTO TotalRows FROM temp;
 
PREPARE STMT FROM " SELECT * FROM temp LIMIT ?,? "; 
SET @START = Desde; 
SET @LIMIT = CantReg; 
EXECUTE STMT USING @START, @LIMIT;
DEALLOCATE PREPARE STMT;
 
DROP TABLE temp;
 
END
 

Funciona pero no logro obtener la cantidad total de registros.
Los 2 parametros son Desde y CantReg.

[gnzsoloyo]

Código MySQL:

CREATE PROCEDURE `EncuestaRegistros` (IN Base VARCHAR(50), IN Estado VARCHAR(2),
    IN Asesor VARCHAR(30), IN Cliente VARCHAR(100), IN Desde INT, IN CantReg TINYINT UNSIGNED, OUT TotalRows INT UNSIGNED)
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
 
    CREATE TEMPORARY TABLE temp
    SELECT T.base,T.CLIENTE,T.FECHACIERREOR FECHACIERRE, 
        T.ASESOR, T.resultados, T.fecha_grabacion, T.registro
    FROM tabla1 T
        INNER JOIN resultados R ON T.resultados = R.nombre_resultado
        INNER JOIN categorias C ON R.id_cat_result = C.id_cat_result
    WHERE T.base = concat('CCP-',upper(Base))
        AND (C.id_cat_result = Estado OR Estado = '*')
        AND (T.ASESOR = Asesor OR Asesor = '*')
        AND (T.CLIENTE LIKE concat('%',Cliente,'%') OR Cliente = '');
 
    SET TotalRows = ROW_COUNT();
 
    SELECT *  FROM temp  LIMIT Desde, CantReg; 
 
END
 

En el contexto de un servidor MySQL 5.1.x o superior, debería reconocer las variables como parametros del LIMIT.
En cuanto al DROP TABLE no es necesario siendo una tabla TEMPORARY creada así. Simplemente se borra al terminar la ejecución del SP.

En cuanto a obtener el valor del parámetro, dependerá de cómo lo recuperes. Muestra el codigo de PHP d esta ejecucion.

[a18327]

Las modificaciones de gnzsoloyo son correctas.
Para para obtener el total de registros tienes que hacer otra consulta del parametro
es decir Primero ejecutas el procedimiento y luego haces la consulta del parametro TotalRows

Código MySQL:

SELECT @TotalRows;
 

[gnzsoloyo]

Iniciado por a18327

Las modificaciones de gnzsoloyo son correctas.
Para para obtener el total de registros tienes que hacer otra consulta del parametro
es decir Primero ejecutas el procedimiento y luego haces la consulta del parametro TotalRows

Código MySQL:

SELECT @TotalRows;
 

Cuidado con el uso de "@" en MySQL.
Para MySQL @TotalRows y TotalRows son diferentes variables.
No puedes en este caso recuperar el valor del parámetro "TotalRows" usando "@TotalRows".

[a18327]

Iniciado por gnzsoloyo

Cuidado con el uso de "@" en MySQL.
Para MySQL @TotalRows y TotalRows son diferentes variables.
No puedes en este caso recuperar el valor del parámetro "TotalRows" usando "@TotalRows".

TotalRows es la variable dentro del procedimiento, pero @TotalRows es la variable que se utiliza como parametro para ejecutar el procedimiento, una vez que se ejecute el procedimiento, la variable @TotalRows tendra el numero total de filas y no la variable TotalRows.

Primero se ejecuta

Código MySQL:

Call EncuestaRegistros(...,@TotalRows);
 

y despues se hace la consulta

Código MySQL:

SELECT @TotalRows;
 

[gnzsoloyo]

Iniciado por a18327

TotalRows es la variable dentro del procedimiento, pero @TotalRows es la variable que se utiliza como parametro para ejecutar el procedimiento, una vez que se ejecute el procedimiento, la variable @TotalRows tendra el numero total de filas y no la variable TotalRows.

Primero se ejecuta

Código MySQL:

Call EncuestaRegistros(...,@TotalRows);
 

y despues se hace la consulta

Código MySQL:

SELECT @TotalRows;
 

¿Estás ejecutando esa llamada a un SP dentro de otro SP de MySQL o a través de una query directa?



Si es así sería mejor que nos muestres el contexto entero de los códigos de PHP, porque pareces estar hablando de una cosa, cuando en realidad sería otra...

[a18327]

Iniciado por gnzsoloyo

¿Estás ejecutando esa llamada a un SP dentro de otro SP de MySQL o a través de una query directa?



Si es así sería mejor que nos muestres el contexto entero de los códigos de PHP, porque pareces estar hablando de una cosa, cuando en realidad sería otra...

Son dos consultas que se hacen, una para el SP, y otra para el total del filas
el codigo php seria algo como

Código PHP:

  <?php
    $mysqli = new mysqli("www.servidor.com", "usuario", "contraseña", "base_de_datos");
    if ($mysqli->connect_errno) {
        echo "Falló la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    //Obtener las filas de la pagina
    $filasResult=$mysqli->query("CALL EncuestaRegistros(...,@TotalRows)");
    //Obtener el total de filas
    mysqli_next_result($mysqli);
    $totalFilasResult = $mysqli->query("SELECT @TotalRows as total");
    $totalFilasArray = $totalFilas ->fetch_assoc();
    $totalFilas=$total['total'];
?>   

[gnzsoloyo]

OK, es lo que suponía: estás usando variables de usuario de MySQL en la llamada.
Básicamente es correcto lo que planteas, siempre que tengas en cuenta una cosa: Luego de ejecutarse el SP, bajo ninguna circunstancia se debe cerrar la conexión antes de leer el valor obtenido en ella, ni perder el enlace a la base, o la variable se muere.
Es la única precaución que se debe tener, por cuanto las variables de usuario de MySQL existen sólo en la misma conexión a MySQL en que se declaran, no tienen tipo de dato, su valor incial es NULL, y conservan el valor que se les asignó mientras la conexión persista.
Una nota a tener en cuenta es que como su valor es NULL al inicio, o se las inicaliza, o se debe tener cuidado al cargarlas en el SP, porque de no incializarla una asignación incorrecta podría devolver datos nulos.
Ese sería el caso de un SP que hiciera:

Código MySQL:

DECLARE var1 INT;
 
SELECT COUNT(1) INTO var1 FROM tabla;
 
SET @varexterna = @varexterna + var1;
 

En un caso así, si la variable @varexterna no llega inicializada con un valor dado, la asignación se destruye, porque en MySQL cuando sumas, multiplicas, concatenas, etc un valor con NULL... da NULL.

¿Se entiende?

Es decir, lo que habría que hacer, preventivamente en tu código sería:

Código PHP:

  <?php
    $mysqli = new mysqli("www.servidor.com", "usuario", "contraseña", "base_de_datos");
    if ($mysqli->connect_errno) {
        echo "Falló la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    // Inicialziar variable:
    $result=$mysqli->query("SET @TotalRows = 0;");

    //Obtener las filas de la pagina
    $filasResult=$mysqli->query("CALL EncuestaRegistros(...,@TotalRows)");

    //Obtener el total de filas
    mysqli_next_result($mysqli);
    $totalFilasResult = $mysqli->query("SELECT @TotalRows as total");
    $totalFilasArray = $totalFilas ->fetch_assoc();
    $totalFilas=$total['total'];
?>   

[a18327]

Iniciado por gnzsoloyo

OK, es lo que suponía: estás usando variables de usuario de MySQL en la llamada.
Básicamente es correcto lo que planteas, siempre que tengas en cuenta una cosa: Luego de ejecutarse el SP, bajo ninguna circunstancia se debe cerrar la conexión antes de leer el valor obtenido en ella, ni perder el enlace a la base, o la variable se muere.
Es la única precaución que se debe tener, por cuanto las variables de usuario de MySQL existen sólo en la misma conexión a MySQL en que se declaran, no tienen tipo de dato, su valor incial es NULL, y conservan el valor que se les asignó mientras la conexión persista.
Una nota a tener en cuenta es que como su valor es NULL al inicio, o se las inicaliza, o se debe tener cuidado al cargarlas en el SP, porque de no incializarla una asignación incorrecta podría devolver datos nulos.
Ese sería el caso de un SP que hiciera:

Código MySQL:

DECLARE var1 INT;
 
SELECT COUNT(1) INTO var1 FROM tabla;
 
SET @varexterna = @varexterna + var1;
 

En un caso así, si la variable @varexterna no llega inicializada con un valor dado, la asignación se destruye, porque en MySQL cuando sumas, multiplicas, concatenas, etc un valor con NULL... da NULL.

¿Se entiende?

Es decir, lo que habría que hacer, preventivamente en tu código sería:

Código PHP:

  <?php 
    $mysqli = new mysqli("www.servidor.com", "usuario", "contraseña", "base_de_datos"); 
    if ($mysqli->connect_errno) { 
        echo "Falló la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; 
    } 
    // Inicialziar variable: 
    $result=$mysqli->query("SET @TotalRows = 0;"); 
 
    //Obtener las filas de la pagina 
    $filasResult=$mysqli->query("CALL EncuestaRegistros(...,@TotalRows)"); 
 
    //Obtener el total de filas 
    mysqli_next_result($mysqli); 
    $totalFilasResult = $mysqli->query("SELECT @TotalRows as total"); 
    $totalFilasArray = $totalFilas ->fetch_assoc(); 
    $totalFilas=$total['total']; 
?>   

En el caso del SP del flaviovich no era necesario inicializar la variable, pero si es cierto que se debe inicializar antes pues no siempre se sabe el código del SP.

[flaviovich]

Amigos, gracias por sus respuestas.
Ya lo logré, con la excepcion que me funcionó con:

Código MySQL:

SELECT COUNT(1) INTO TotalRows FROM temp;