PHP, PDO Stored Procedure return nothing or unchanged value

Question

i am using php with mysql.

My stored proc returns values via out parameter via Toad4MySQL but when it comes to php Pdo, it does not capture the return value.

here's my code

$validusername= 'x';
$validemail = 'x'; 
$validkey   = 'x';

$query = $pdo->prepare("Call InsertNewUser(:fullname, :usernamex, :email, :password, :con_password, @:validusername, @:validemail, @:validkey); ");

$query->bindParam(':fullname', $fullname , PDO::PARAM_STR);
$query->bindParam(':usernamex', $usernamex , PDO::PARAM_STR);
$query->bindParam(':email', $email, PDO::PARAM_STR);
$query->bindParam(':password', $password, PDO::PARAM_STR);
$query->bindParam(':con_password', $con_password, PDO::PARAM_STR);
$query->bindParam(':validusername', $validusername, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);
$query->bindParam(':validemail', $validemail , PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);
$query->bindParam(':validkey', $validkey   , PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);

$results = $query->  execute();

echo $validemail ; # returns x but not the value that i wanted.

Can anyone help me with this puzzle ?

My Stored Procedure code:

DROP PROCEDURE IF EXISTS InsertNewUser;
CREATE PROCEDURE xxx.`InsertNewUser`(

 fullname varchar(255),
 username varchar(255),
 email_address varchar(255), 
 password varchar(255),
 con_password varchar(255),
 out validusername varchar(10),
 out validemail varchar(10),
 out validkey varchar(155)
)
BEGIN

  declare xcnt_1 int;
  declare xcnt_2 int;
    declare xkey varchar(155);
  set xkey = MD5(RAND());

  select count(*) into xcnt_1 from user where user.email_address = email_address;
  select count(*) into xcnt_2 from user where user.username = username;

    if xcnt_1 > 0  then
      set validemail = 'false';
    else 
      set validemail = 'true';
    end if;

     if xcnt_2 > 0  then
      set validusername = 'false';
    else 
      set validusername = 'true';
    end if;

    if xcnt_1 = 0 and xcnt_2 = 0 then

      set validkey = xkey;
      INSERT INTO user
      (user.fullname, user.username, user.email_address, user.password, user.con_password, user.md5validate) 
      VALUES
      (fullname, username, email_address, password, con_password, xkey);
    else
       set validkey = 'false';
    end if;
    commit;

END;

ah... finally solved this problem :

for some unknown reasons, i have to change the call statement to :

Call InsertNewUser(:fullname, :usernamex, :email, :password, :con_password, @validusername_global, @validemail_global, @validkey_global);

and use this line of codes to get the data...

$SQL = ' select @validusername_global as validusername, @validemail_global as validemail, @validkey_global as validkey';
$query = $pdo->query($SQL);

while($row = $query->fetch(PDO::FETCH_ASSOC))
{
    print_r($row);
}

score 1 · Answer 1 · answered Jul 09 '11 at 22:14

1

Executing the PDO prepared statement won't have any effect on $validemail.

After $results = $query->execute();, try adding print_r($query->fetchAll()); to get a better sense of what is being returned from MySQL to PHP when your PHP statement runs the MySQL stored procedure.

Keep in mind that $results is just going to be a boolean indicating whether or not the statement could be executed.

answered Jul 09 '11 at 22:14

Schwartzie

309
2
6

@Schwartzie, it SHOULD have an effect on it if it's an input/output parameter. – Mike Jul 09 '11 at 22:25
Look at Example #10 on this page: http://php.net/manual/en/pdo.prepared-statements.php – Schwartzie Jul 09 '11 at 22:25
On second thought, it's not that instructive. Mike's answer is a better fit. – Schwartzie Jul 09 '11 at 22:37
Well, none of the ideas work.. i still can't get it to work no matter what i tried :( Out should be working but that's not the case.. – Melvin Jul 09 '11 at 22:39

score 1 · Answer 2 · answered Jul 09 '11 at 22:19

1

From the PDO manual:

int $length

Length of the data type. To indicate that a parameter is an OUT parameter from a stored procedure, you must explicitly set the length.

http://php.net/manual/en/pdostatement.bindparam.php

You have omitted this parameter, which would explain why it is not being used as an OUT parameter.

answered Jul 09 '11 at 22:19

Mike

23,542
14
76
87

tried b4, it still does not return any value in my php but it returns on Toad. – Melvin Jul 09 '11 at 22:22
Well it's still necessary, so you should keep it there just in case. – Mike Jul 09 '11 at 22:26
@Schwartzie and @diEcho, Anyway, thanks for the feedbacks. i got it working finally.. but i still do not understand why the "_global" must be added for it to work.. – Melvin Jul 09 '11 at 23:04

score 0 · Answer 3 · answered Jul 09 '11 at 21:56

0

if you set $validemail=x and dont manipulate/change nothing with $validemail in later code then obvioulsy echo $validemail will returns x.? whats' wrong with that? either u missed to paste some code

answered Jul 09 '11 at 21:56

xkeshav

53,360
44
177
245

i have $validemail=x to detect the change in value but for some uncertain reasons, it does not changed. My storedP returns true or false strings from Toad4MySQL but in my php, it does not do anything to bounded variable. echo $validemail should return true / false but not x. – Melvin Jul 09 '11 at 21:58
but the code you show in your question not having that specific code which use `$validemail` except inserting into database – xkeshav Jul 09 '11 at 22:00
this is the header of my sp : CREATE PROCEDURE `InsertNewUser`( fullname varchar(255), username varchar(255), email_address varchar(255), password varchar(255), con_password varchar(255), out validusername varchar(10), out validemail varchar(10), out validkey varchar(155) ) – Melvin Jul 09 '11 at 22:02
@Melvin what means by `storedP`is that typo?? and i m sorry i am not familiar with`Toad4MySQL` what it do?? Please write desirable output?? – xkeshav Jul 09 '11 at 22:02
i am using this software : http://www.quest.com/toad-for-mysql/ to manage my mysql codes and queries. – Melvin Jul 09 '11 at 22:04
@melvin; please update your question with the stored Procedure code; somebody will helps you sure. i m not expert in that, sorry :( – xkeshav Jul 09 '11 at 22:05

score 0 · Answer 4 · answered Jul 09 '11 at 22:04

0

Try wrapping your prepare statement, bind parameters and execute in try, catch blocks and see if PDO spits out any sort of error.

answered Jul 09 '11 at 22:04

Mike

23,542
14
76
87

PHP, PDO Stored Procedure return nothing or unchanged value

4 Answers4