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 'MYSQLI: simple select';
$rs = $mysqli->query( 'SELECT * FROM users;' );
while($row = $rs->fetch_object())
{
debug($row);
}

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

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

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

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

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

function debug($o)
{
print '';
print_r($o);
print '';
}

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!