i'm making a school project, i have to build a bank and do transfers etc. so i've made two stored procedures, withdraw and deposit, both takes the account number and an amount as parameters.
CREATE DEFINER=`bank`@`localhost` PROCEDURE `withdraw`(
IN p_account INT(11),
IN p_amount DOUBLE
)
BEGIN
DECLARE p_balance double;
SELECT balance
INTO p_balance FROM
Accounts
WHERE
account_id = p_account;
IF p_balance >= p_amount THEN
UPDATE Accounts
SET balance = p_balance - p_amount
WHERE account_id = p_account;
//tell my php somehow
ELSE
//tell my php somehow
END IF;
END
i've been searching google dry, and i've tried:
to make a function and return a value from the function (some tutorial.. didnt work ofc xD)
to declare an output value (OUT p_result INT) and set the int, no luck
i've tried, to get that output value to work and im clueless.
what i want is in my php script
$sql = "CALL withdraw(1,500);"
$result = conn->query($sql);
if($result=true){
conn->query("CALL deposit(2,500)");
} else {
//inform user
}
EDIT!: i now SELECT something in my procedures, in deposit() the return is an email address.
In withdraw() the return is the account that has been withdrawed. Both works fine 1 by 1. but when i do withdraw() first, and then deposit() if withdraw succeeded, then it my var_dump($result) = bool(false)
Stored PROCEDURES can return a resultset. The last thing you SELECT in a stored procedure is available as a resultset to the calling environment.. Stored FUNCTIONS can return only a single result primitive.