0

This is my Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `call`.`sp_login`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_login`(userid varchar(50),pass varchar(50),out chk int(11),out user_alias varchar(50),out user_tariff varchar(50),out c_credit decimal(15,5),out r_reseller varchar(10),out r_level varchar(10),out c_tariff char(2))
BEGIN
select id into chk from cc_reseller  where useralias =userid and uipass=pass and level!=0 and activated!='0';
if(chk!="") then
select id,useralias,tariff,credit,reseller,level,create_tariff into chk,user_alias,user_tariff,c_credit,r_reseller,r_level,c_tariff from cc_reseller where useralias =userid and uipass=pass and activated!='0' and level!=0;
end if;
    END$$

DELIMITER ;

This is my Php Code to call StoreProcedure

<?php
    ob_start();
  include_once('config.php');
  $username = $_POST['username'];
  $password = $_POST['password'];
  
  if (isset($_POST['username']) && $_POST['username']!='')
        {
            $username=$_POST['username'];
            $password=$_POST['password'];        
            $query="call sp_login('$username','$password',@chk,@user_alias,@user_tariff,@c_credit,@r_reseller,@r_level,@c_tariff)";
            $result=mysql_query($query) or die('Query error'.''.mysql_error());
            $count=mysql_fetch_row($result);
           

            $chk = $count[0];
            if($chk !="")
            {
                $n_name = $count[1];
                $n_tariff = $count[2];
                $n_username = $count[3];
                $n_creator = $count[4];
                 $n_credit = $count[5];
                $_SESSION['username'] = $n_username;
                $_SESSION['user_id'] = $chk;
                $_SESSION['tariff'] = $n_tariff;
                $_SESSION['reseller'] = $n_creator;
                $_SESSION['ncredit'] = $n_credit;
                
                header('Location:welcomes.php');
            }
            else
            {
                header("location:index.php?q=-2");
            }    
        }
        else
        {
            if(!isset($_SESSION['username']) || $_SESSION['username']=='')
            {
                header("location:index.php?q=-1");
            }
        } 
?>

Can anyone tell where i m wrong..
is it true that Out parameter doesnt work with mysql..???

Help me out

Edited by sam023: n/a

2
Contributors
10
Replies
11
Views
8 Years
Discussion Span
Last Post by network18
0

No its not true, what error you get..?

Query errorPROCEDURE call.sp_login can't return a result set in the given context

Well i google little bit..!!

i got this article
http://www.artfulsoftware.com/infotree/tip.php?id=130

in it is said that Mysql Api cannot fetch out parameter..!!!

may be thats why people use mysqli..??

What do u say..?

0
$con = mysql_connect('localhost','root','',false,65536);

can u show some light over the code..!

last two parameters makes the difference..!!

now my procedure is running..!!!
Thanks for the link

0

still i dont understand the use and meaning of last two parameters..??
what is the use of it..!! earlier i never use it..?

Can u tell me that

0

still i dont understand the use and meaning of last two parameters..??
what is the use of it..!! earlier i never use it..?

Can u tell me that

sure, this last two parameters are optional,
the the but one parameter is called new_link, if its set to true, each time you call mysql_connect() new connection is created and that MYSQL link identifier is returned back.Otherwise no new link will be established, but instead, the link identifier of the already opened link will be returned.
and the last parameter called client_flags is an integer actually, has values like 'MYSQL_CLIENT_IGNORE_SPACE ', 'MYSQL_CLIENT_COMPRESS' and 'MYSQL_CLIENT_INTERACTIVE' etc.
Don't worry much about this last one, if you don't need it at the moment.

0

sure, this last two parameters are optional,
the the but one parameter is called new_link, if its set to true, each time you call mysql_connect() new connection is created and that MYSQL link identifier is returned back.Otherwise no new link will be established, but instead, the link identifier of the already opened link will be returned.
and the last parameter called client_flags is an integer actually, has values like 'MYSQL_CLIENT_IGNORE_SPACE ', 'MYSQL_CLIENT_COMPRESS' and 'MYSQL_CLIENT_INTERACTIVE' etc.
Don't worry much about this last one, if you don't need it at the moment.

Well i dont think last two parameters are optional in case of stored procedure...!! when i remove those paramemters.. Store Procedure does run at all..!! How these parameters affect the running of store procedures..?

0

yes, they seems to be important for stored procedures to execute -
When you connect and expect to use a stored procedure,you must pass a special flag to MySQL via the connect command, otherwise you will not get the results returned, and it will result in this error:
PROCEDURE AlexGrim.GetStats_ForumCategories can't return a result set in the given context

To fix this, change you connection string, adding ",false,65536" as the last 2 fields:
$this->con = mysql_connect($this->h,$this->u,$this->p,false,65536);

This is what you did.Because the constant 65536 represents the "CLIENT_MULTI_STATEMENTS", which will enable the support for using multiple statement in the query.
Why to use this, must be obvious now for you, as stored procedures are likely to have multiple statements

Edited by network18: light on using stored procedure in php

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.