Re: regarding php
Hi
check this it really help you:
stored procedures using the following database extensions:
MySQL - http://us.php.net/manual/en/book.mysql.phpMySQLi - http://uk2.php.net/manual/en/class.mysqli.phpPDO - http://us.php.net/manual/en/class.pdo.php
First we need to setup our enviroment which consists of a new
database with one table and two stored procedures. In your db tool of
choice create a new database named test.
After you create the new database, make sure to add a user called example
with password example to the database and give it read
access.
CREATE DATABASE
`test`;
Now create the table users:
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;
Before we create the stored procedures, lets put some dummy data in the users
table. To do that just run the following query:
INSERT INTO `test`.`users` VALUES (NULL, ‘Joey’,
‘Rivera’), (NULL, ‘John’,
‘Doe’);
Next create the first stored procedure get_user:
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 ;
Finally create the second and last stored procedure get_users:
DELIMITER $$
DROP PROCEDURE IF EXISTS
`test`.`get_users`$$
CREATE PROCEDURE `test`.`get_users`()
BEGIN
SELECT *
FROM users;
END $$
DELIMITER ;
If you understand the sql above, skip this section. The first script
we ran to create a database is pretty self explanitory. The second
script will delete the table users if it’s already in your
database then it will recreate it. The table will consist of three
fields: users_id, first_name, and last_name.
The insert script will create two users: ‘Joey Rivera’ and ‘John Doe’.
If stored procedures are new to you, don’t worry. They aren’t that
complicated once you start playing with them. When looking at the code
for the first stored procedure, drop procedure works the same
way as dropping a table. First you want to check if the stored procedure
is there and deleted before you recreate it. Create does just
that, create the stored procedure in the database. get_user has
three parameters: userId, firstName, and lastName.
IN means when this stored procedure is called, this variable
should be passed with a value. OUT means after the stored
procedure executes, it will set the OUT variables with a value
that can then be retrieved. You can also have INOUT variables
but we don’t need them for this example.
The blulk of the code for the stored procedure goes in the BEGIN
to END block. get_user is selecting the first and
last name fields from the table users where the user id is equal to the userId
variable being passed in. The other thing happening here is the
two OUT variables are getting the values retrieved from the
select statement. Variable firstName is set to the field first_name
and lastName is being set to last_name. That’s
it for get_user. get_users doesn’t have any IN nor
OUT variables. When that stored procedure is executed it will
return a recordset instead of variables.
Now that we have our environment set, we are ready to start our
tests. Depending on what you are trying to achieve, you may be using mysql,
mysqli, or PDO. I’m going to run the same tests with
all three to show you the difference as well as the limitation of mysql
compared to mysqli and PDO. One of the tests
I’ll be running doesn’t work with mysql while all the tests
work with mysqli and PDO.
The three tests will be:
A simple select statementCalling stored procedure passing IN variable and retrieve OUT
variables – get_userCalling stored procedure with no parameters and returns a recordset –
get_users
Below is the code to run all three tests with each of the database
extensions:
<?php
// MYSQL
$mysql = mysql_connect(‘localhost’, ‘example’, ‘example’);
mysql_select_db(‘test’, $mysql);
print ‘<h3>MYSQL: simple
select</h3>’;
$rs = mysql_query( ‘SELECT * FROM users;’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
print ‘<h3>MYSQL:
calling sp with out variables</h3>’;
$rs = mysql_query( ‘CALL get_user(1, @first, @last)’ );
$rs = mysql_query( ‘SELECT @first, @last’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
print ‘<h3>MYSQL:
calling sp returning a recordset – doesn\’t
work</h3>’;
$rs = mysql_query( ‘CALL get_users()’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
// MYSQLI
$mysqli = new mysqli(‘localhost’, ‘example’, ‘example’, ‘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 returning a recordset</h3>’;
$rs = $mysqli->query( ‘CALL
get_users()’ );
while($row = $rs->fetch_object())
{
debug($row);
}
// PDO
$pdo = new PDO(‘mysql:dbname=test;host=127.0.0.1′,
‘example’, ‘example’);
print ‘<h3>PDO: simple
select</h3>’;
foreach($pdo->query( ‘SELECT * FROM users;’ ) as $row)
{
debug($row);
}
print ‘<h3>PDO: calling
sp with out variables</h3>’;
$pdo->query( ‘CALL get_user(1, @first, @last)’
);
foreach($pdo->query( ‘SELECT @first, @last’ ) as $row)
{
debug($row);
}
print ‘<h3>PDO: calling
sp returning a recordset</h3>’;
foreach($pdo->query( ‘CALL get_users()’ ) as $row)
{
debug($row);
}
function debug($o)
{
print ‘<pre>’;
print_r($o);
print ‘</pre>’;
}
?>
When you run this code you get the following results:
MYSQL: simple select
Array
(
[users_id] => 1
[first_name] =>
Joey
[last_name] =>
Rivera
)
Array
(
[users_id] => 2
[first_name] =>
John
[last_name] =>
Doe
)
MYSQL: calling sp with out variables
Array
(
[@first] =>
Joey
[@last] =>
Rivera
)
MYSQL: calling sp returning a recordset – doesn‘t work
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL
result resource in ***test.php on line 24
MYSQLI: simple select
stdClass Object
(
[users_id] => 1
[first_name] => Joey
[last_name] => Rivera
)
stdClass Object
(
[users_id] => 2
[first_name] => John
[last_name] => Doe
)
MYSQLI: calling sp with out variables
stdClass Object
(
[@first] => Joey
[@last] => Rivera
)
MYSQLI: calling sp returning a recordset
stdClass Object
(
[users_id] => 1
[first_name] => Joey
[last_name] => Rivera
)
stdClass Object
(
[users_id] => 2
[first_name] => John
[last_name] => Doe
)
PDO: simple select
Array
(
[users_id] => 1
[0] => 1
[first_name] => Joey
[1] => Joey
[last_name] => Rivera
[2] => Rivera
)
Array
(
[users_id] => 2
[0] => 2
[first_name] => John
[1] => John
[last_name] => Doe
[2] => Doe
)
PDO: calling sp with out variables
Array
(
[@first] => Joey
[0] => Joey
[@last] => Rivera
[1] => Rivera
)
PDO: calling sp returning a recordset
Array
(
[users_id] => 1
[0] => 1
[first_name] => Joey
[1] => Joey
[last_name] => Rivera
[2] => Rivera
)
Array
(
[users_id] => 2
[0] => 2
[first_name] => John
[1] => John
[last_name] => Doe
[2] => Doe
)
As you can see from the results above, mysql could not get
the recordset returned by the stored procedure while mysqli and
PDO could. After some more research, some people mentioned (Bob’s
World, php.net)
that by adding ‘false,65536′ to the end of the mysql_connect line,
mysql could then get recordsets from stored procedures. I
tried this and in fact it does work. So by changing
$mysql = mysql_connect(‘localhost’, ‘example’, ‘example’);
to:
$mysql = mysql_connect(‘localhost’, ‘example’, ‘example’,false,65536);
all the different database extensions work on all tests. So in the
end, it seems all of these can work with stored procedures just as well.Imran Shafiq Khan
--- On Tue, 20/4/10, priyanshu <priyanshu_gupta0007@xxxxxxxxx> wrote:
From: priyanshu <priyanshu_gupta0007@xxxxxxxxx>
Subject: regarding php
To: php-objects@xxxxxxxxxxxxxxx
Date: Tuesday, 20 April, 2010, 20:51
hiiii can u help me....... how to get the result from an stored procedure to a php page?????
[Non-text portions of this message have been removed]
[PHP Home]
[PHP Users]
[PHP Soap]
[Kernel Newbies]
[Yosemite]
[Yosemite Campsites]
