Problems with mysqli_query and a Stored Procedure
Posted: Tue Dec 23, 2008 4:42 pm
My SP
My PHP code
This code should return the 5 first records, but an error happens
$n can be any string or NULL because for TipoConsulta=2 I only need the last parameter
The php's null is not the same thing as the mysql's null… or Am I making a mistake?... I suppose that because if $n=NULL the error is the same
ERROR
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in …
I suppose that mysqli_query returns FALSE. But if I replace $query for
The 5 first records are returned satisfactorily and when I call the SP from mysql
Again the 5 first records are returned
Why does this happen? Where or What is my mistake?
DB
http://www.4shared.com/file/77323067/b8 ... emplo.html
Code: Select all
CREATE DEFINER = 'root'@'localhost' PROCEDURE `SP_Proveedores`(IN _TipoConsulta TINYINT, IN _IdProveedor INTEGER, IN _NombreCompania VARCHAR(40), IN _Direccion VARCHAR(60), IN _Telefono VARCHAR(24), IN _NombreContacto VARCHAR(30), IN _CargoContacto VARCHAR(30),IN _listaInicio INTEGER)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
IF _TipoConsulta=1 THEN
UPDATE proveedores
SET NombreCompañía = _NombreCompania,
Dirección = _Direccion,
Teléfono=_Telefono,
NombreContacto = _NombreContacto,
CargoContacto = _CargoContacto
WHERE IdProveedor=_IdProveedor;
ELSEIF _TipoConsulta=2 THEN
SET @li=_listaInicio;
PREPARE stmt FROM "SELECT * FROM proveedores LIMIT ?,5";
EXECUTE stmt USING @li;
DEALLOCATE PREPARE stmt;
ELSEIF _TipoConsulta=3 THEN
INSERT INTO proveedores
(NombreCompañía
,Dirección
,Teléfono
,NombreContacto
,CargoContacto)
VALUES
(_NombreCompania
,_Direccion
,_Telefono
,_NombreContacto
,_CargoContacto);
ELSEIF _TipoConsulta=4 THEN
DELETE FROM proveedores
WHERE IdProveedor=_IdProveedor;
ELSEIF _TipoConsulta=5 THEN
SELECT IdProveedor,NombreCompañía,Dirección,Teléfono,NombreContacto,CargoContacto
FROM proveedores
WHERE IdProveedor=_IdProveedor;
ELSEIF _TipoConsulta=6 THEN
SELECT IdProveedor,NombreCompañía,Dirección,Teléfono,NombreContacto,CargoContacto
FROM proveedores
WHERE LOWER(NombreCompañía) like CONCAT('%',LOWER(_NombreCompania),'%');
END IF;
END;
Code: Select all
$host="localhost";
$usuario="root";
$password="";
$db="ejemplo";
$cnn=mysqli_connect($host,$usuario,$password,$db);
$n=”n”;
$query="CALL SP_Proveedores(2,$n,$n,$n,$n,$n,$n,0)";
$rs = mysqli_query($cnn,$query);
while ($registro=mysqli_fetch_array($rs))
{
$coleccion[]=$registro;
}$n can be any string or NULL because for TipoConsulta=2 I only need the last parameter
The php's null is not the same thing as the mysql's null… or Am I making a mistake?... I suppose that because if $n=NULL the error is the same
ERROR
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in …
I suppose that mysqli_query returns FALSE. But if I replace $query for
Code: Select all
$query="select * from proveedores limit 0,5";Code: Select all
CALL SP_Proveedores(2, NULL, NULL, NULL, NULL, NULL,NULL,0);Why does this happen? Where or What is my mistake?
DB
http://www.4shared.com/file/77323067/b8 ... emplo.html