I have been coding a registration page(login system) in php and
mysql for a website. I'm using two stored procedures for the same. First stored procedure checks wether the email address already exists in database.Second one inserts the user supplied data into
mysql database. User has EXECUTE permission on both the procedures.When is execute them individually from php script they work fine. But when i use them together in script second Stored procedure(insert) not working.
Stored procedure 1.
DELIMITER $$
CREATE PROCEDURE reg_check_email(email VARCHAR(80))
BEGIN
SET @email = email;
SET @sql = 'SELECT email FROM user_account WHERE user_account.email=?';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @email;
END$$
DELIMITER;
Stored procedure 2
DELIMITER $$
CREATE PROCEDURE reg_insert_into_db(fname VARCHAR(40), lname VARCHAR(40), email VARCHAR(80), pass VARBINARY(32), licenseno VARCHAR(80), mobileno VARCHAR(10))
BEGIN
SET @fname = fname, @lname = lname, @email = email, @pass = pass, @licenseno = licenseno, @mobileno = mobileno;
SET @sql = '
INSERT INTO user_account(email,pass,last_name,license_no,phone_no) VALUES(?,?,?,?,?)';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @email,@pass,@lname,@licenseno,@mobileno;
END$$
DELIMITER;
When i test these from php sample script
insert is not working , but first stored procedure(reg_check_email()) is working. If i comment off first one(reg_check_email), second stored procedure(reg_insert_into_db) is working fine.
<?php
require("/wamp/mysql.inc.php");
$r = mysqli_query($dbc,"CALL reg_check_email('
[email protected]')");
$rows = mysqli_num_rows($r);
if($rows == 0) {
$r = mysqli_query($dbc,"CALL reg_insert_into_db('a','b','
[email protected]','c','d','e')");
}
?>
i'm unable to figure out the mistake.
Thanks in advance,
ravi.
