need code example for stored procedure in mysql + php

[subhashish12]

hello

i need solution for to create a insert/select/delete/update stored procedure in mysql

and how to call them from php

currently i am using xampp 1.6.6 version of software

thank you

[subhashish12]

first in the mysql do the following code execution

CREATE DATABASE `test`;


USE `test`;


DROP TABLE IF EXISTS `test`.`users`;
CREATE TABLE `test`.`users` (
`users_id` int(10) unsigned NOT NULL auto_increment,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
PRIMARY KEY (`users_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `test`.`users` VALUES (null, 'Joey', 'Rivera'), (null, 'John', 'Doe');


DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_user`$$
CREATE PROCEDURE `test`.`get_user`
(
IN userId INT,
OUT firstName VARCHAR(100),
OUT lastName VARCHAR(100)
)
BEGIN
SELECT first_name, last_name
INTO firstName, lastName
FROM users
WHERE users_id = userId;
END $$
DELIMITER ;


DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_users`$$
CREATE PROCEDURE `test`.`get_users`()
BEGIN
SELECT *
FROM users;
END $$
DELIMITER ;



DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`add_user`$$
CREATE PROCEDURE `test`.`add_user`
(
IN userId INT,
IN firstName VARCHAR(100),
IN lastName VARCHAR(100)
)
BEGIN
InSERT into users(first_name,last_name)
values(firstName, lastName);
END $$
DELIMITER ;


DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`delete_user`$$
CREATE PROCEDURE `test`.`delete_user`
(
IN userId INT
)
BEGIN
delete from users where users_id=userId;
END $$
DELIMITER ;



update user


DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`update_user`$$
CREATE PROCEDURE `test`.`update_user`
(
IN userId INT,
IN firstName VARCHAR(100),
IN lastName VARCHAR(100)
)
BEGIN
update users set first_name=firstName,last_name=lastName where users_id=userId;
END $$
DELIMITER ;



DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`add_user`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_user`(
IN firstName VARCHAR(100),
IN lastName VARCHAR(100)
)
BEGIN
INSERT into users(first_name,last_name)
values(firstName, lastName);
END $$
DELIMITER ;

then in the php page

write the following code

you can check these of your own, you can use musql as mysqli no problem

// MYSQLI
$mysqli = new mysqli('localhost', 'root', '', 'test');



print '<h3>MYSQLI: simple select</h3>';
$rs = $mysqli->query( 'SELECT * FROM users;' );
while($row = $rs->fetch_object())
{
debug($row);
}


print '<h3>MYSQLI: calling sp with out variables</h3>';
$rs = $mysqli->query( 'CALL get_user(1, @first, @last)' );
$rs = $mysqli->query( 'SELECT @first, @last' );
while($row = $rs->fetch_object())
{
debug($row);
}


print '<h3>MYSQLI: calling sp with add variables</h3>';
$rs = $mysqli->query( 'CALL add_user("ss1", "bbsr1")' );



print '<h3>MYSQLI: calling sp with Delete variables</h3>';
$rs = $mysqli->query( 'CALL delete_user(9)' );


print '<h3>MYSQLI: calling sp with update variables</h3>';
$rs = $mysqli->query( 'CALL update_user(4,"Rakesh","mumtaz")' );



print '<h3>MYSQLI: calling sp returning a recordset</h3>';
$rs = $mysqli->query( 'CALL get_users()' );
while($row = $rs->fetch_object())
{
debug($row);
}


function debug($o)
{
print '<pre>';
print_r($o);
print '</pre>';
}

and you can write of your own code in side while loop

---------------
i hope it will be useful to other programmers too

[diafol]

Search this forum/google?

http://www.daniweb.com/forums/thread41143.html

http://www.joeyrivera.com/2009/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/

In fact, there are hundreds of sites out there (ref. Google, Bing).

//EDIT

Aha! You found it!

[gopi naidu]

hi stored procs are not working in my mysql database...what can i doplease help me..
i got some error while creating the procedure i.e
Error

SQL query: Documentation

DELIMITER $$ DROP PROCEDURE IF EXISTS get_users $$ CREATE PROCEDURE get_users ( ) BEGIN SELECT *
FROM test;

MySQL said: Documentation

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$

DROP PROCEDURE IF EXISTS get_users$$
CREATE PROCEDURE `get_use' at line 1

[Adnan_10]

Hi,
Thank you very much for providing a breif tutorial. Now I have learned Stored Procedures & its working properly.
I want to ask a question How can I get last-insert-id with using stored procedure?? I used following syntax:
DELIMITER $$
DROP PROCEDURE IF EXISTS myemp_db. add_enquiry $$# MySQL returned an empty result set (i.e. zero rows).

CREATE PROCEDURE myemp_db.add_enquiry (
IN EnquiryNo varchar(40),
IN OpeningDate date,
IN ClosingDate date,
IN Currency varchar(5),
IN MasterID int(11),
IN EmpID int(11),
IN Status int(2),
IN Comments text,
IN CreatedOn date,
OUT LID INT(11))

BEGIN
INSERT into enquiries SET enquiry_no=EnquiryNo ,opening_date=OpeningDate ,closing_date=ClosingDate,currency=Currency,master_id=MasterID ,emp_id=EmpID,status=Status,comments=Comments,created_on =CreatedOn;

SET LID=LAST_INSERT_ID();
END $$# MySQL returned an empty result set (i.e. zero rows).
DELIMITER ;

But I didnt get the last insert ID..Can you please provide the solution? How can I call on PHP?
Thanks & Regards
Adnan