Problems with mysqli_query and a Stored Procedure

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ClaCS
Forum Newbie
Posts: 1
Joined: Tue Dec 23, 2008 4:36 pm

Problems with mysqli_query and a Stored Procedure

Post by ClaCS »

My SP

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;
 
My PHP code

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;
}
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

Code: Select all

$query="select * from proveedores limit 0,5";
The 5 first records are returned satisfactorily and when I call the SP from mysql

Code: Select all

CALL SP_Proveedores(2, NULL, NULL, NULL, NULL, NULL,NULL,0);
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
cptnwinky
Forum Commoner
Posts: 84
Joined: Sat Dec 27, 2008 10:58 am
Location: Williamstown, MA

Re: Problems with mysqli_query and a Stored Procedure

Post by cptnwinky »

Try this to see the error.

Code: Select all

 
$rs = mysqli_query($cnn,$query) or die(mysqli_error());
 
That should give you some more information as to why the query is failing.
Post Reply