Hi all
I am goiing to develop a system with PHP 5 and MySQL, and this is the first time I want to use MySQL stored procedures. I've heard that using stored procedures has lotf of benefits, but I am not going to discuss about it now.
Well, when we use mysql as usual, we perform the query, and fetch the results using mysql_fetch_array, mysql_fetch_object, etc. But when it comes to use stored procedures, you have to use mysqli. It works fime with my system, but I have some troubles about retrieving results.
Because I've implemented most of my logic in stored procedure, it may return different results. For example, I want to have user ID and his name when successful login has been performed, but when user provided wrong username or password (thus stored procedure called by PHP with wrong values), it must return an error code.
I wanna know, what is the best approach to do it. Here is my SP, but I don't know how can I retrieve the result:
DELIMITER $$
DROP PROCEDURE IF EXISTS `doLogin` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `doLogin`(inpUsername VARCHAR(255), inpPassword VARCHAR(255))
BEGIN
/*********************************/
/* Variable declaration */
/*********************************/
DECLARE tblID INT;
DECLARE tblName VARCHAR(255);
/*********************************/
/* Variable declaration finished */
/*********************************/
/* Select user details */
SELECT ID,Name INTO tblID,tblName FROM users WHERE Username = inpUsername AND Pass = inpPassword ;
/* If any result returned from database. Means user has been found */
IF (tblID) THEN
/* Send back data */
SELECT tblID,tblName;
ELSE
SELECT 'invalid username or password';
END IF;
END $$
DELIMITER ;
What do you suggest to retrieve the results with PHP?
