WindowsでPHPからストアドプロシージャ／ストアドファンクションを実行する

CREATE PROCEDURE procedure_name ([procedure_parameter[,...]])
    routine_body

call books.proc(@a);
select @a;

CREATE FUNCTION function_name ([function_parameter[,...]])
RETURNS type
routine_body

CREATE FUNCTION simple_operation (price int) RETURNS int
RETURN price*1000

SELECT simple_operation(5)

create table bookstore 
   (id int not null auto_increment primary key,
    book varchar(50),
    author varchar(50),
    isbn varchar(50),
    price int);

INSERT INTO bookstore (id,book,author,isbn,price) VALUES   
  (1,"Introduction to PHP","Mark User","3334-4424-334-3433",500)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES 
  (2,"DHTML and CSS","Teague Sanders","4545-23-23-23-23232",1500)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES 
  (3,"Introduction to PHP","Weeling Tom","4334-2323-23233-434",300)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES 
  (4," Web design"," Weeling Tom"," 4334-2323-23233-434",600)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES 
  (5," PHP 5"," Weeling Tom"," 444-87-67665-678678",600)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES 
  (6," JavaServer Pages"," Tick Own"," 897-9898-987-099",800)

CREATE PROCEDURE proc ( )
BEGIN
  SELECT * from bookstore;
END

<?php
 //Create the connecting to MySQL
 $con = mysql_connect('localhost','root','',false,65536);
 mysql_select_db('books');
 
 //Call the proc() procedure
 $result= mysql_query("CALL proc();")
 or die(mysql_error());
 
//Output the result
while($row = mysql_fetch_row($result))
{
 for($i=0;$i<=6;$i++){
   echo $row[$i]."<br>";
 }
echo "---";
}
//Close the connection
mysql_close($con);
?>

1---Introduction to PHP---Mark User---3334-4424-334-3433---500--------
2---DHTML and CSS---Teague Sanders---4545-23-23-23-23232---1500-------
3---Introduction to PHP---Weeling Tom---4334-2323-23233-434---300-----
4---Web design---Weeling Tom---4334-2323-23233-434---600---------
5---PHP 5---Weeling Tom---444-87-67665-678678---600---------
6---JavaServer Pages---Tick Own---897-9898-987-099---800---------

CREATE PROCEDURE total_price ( OUT total int)
BEGIN
SELECT sum(price) into total from bookstore;
END

<?php
 $con = mysql_connect('localhost','root','',false,65536);
 mysql_select_db('books');
 
 //Calling the total_price stored procedure using the @t OUT parameter
 $result= mysql_query("CALL total_price(@t);")
 or die(mysql_error());
 
 //Listing the result
 $rs = mysql_query( 'SELECT @t' );
 while($row = mysql_fetch_row($rs))
 {
  echo 'The total price is = '.$row[0];
 }
mysql_close($con);
?>

The total price is = 4300

CREATE FUNCTION simple_operation (price int) RETURNS int(11)
RETURN price*1000

<?php
 $con = mysql_connect('localhost','root','',false,65536);
 mysql_select_db('books');
 
 //Calling the simple_operation function
 $rs = mysql_query( 'SELECT simple_operation(5)' );
 while($row = mysql_fetch_row($rs))
 {
  echo 'The total price is = '.$row[0];
 }
mysql_close($con);
?>

The total price is = 5000

$mysqli->query( 'CALL proc()' );

<?php
//Connecting to the books database 
$mysqli = new mysqli('localhost', 'root', '', 'books');
//Calling the proc() procedure
$rs = $mysqli->query( 'CALL proc()' );
while($row = $rs->fetch_object())
{
print_r($row);
echo "<br />";
}
?>

stdClass Object ( [id] => 1 [book] => Introduction to PHP [author] => 
   Mark User [isbn] => 3334-4424-334-3433 [price] => 500 ) 
stdClass Object ( [id] => 2 [book] => DHTML and CSS [author] => 
   Teague Sanders [isbn] => 4545-23-23-23-23232 [price] => 1500 ) 
stdClass Object ( [id] => 3 [book] => Introduction to PHP [author] => 
   Weeling Tom [isbn] => 4334-2323-23233-434 [price] => 300 ) 
stdClass Object ( [id] => 4 [book] => Web design [author] => 
   Weeling Tom [isbn] => 4334-2323-23233-434 [price] => 600 ) 
stdClass Object ( [id] => 5 [book] => PHP 5 [author] => 
   Weeling Tom [isbn] => 444-87-67665-678678 [price] => 600 ) 
stdClass Object ( [id] => 6 [book] => JavaServer Pages [author] => 
   Tick Own [isbn] => 897-9898-987-099 [price] => 800 )

<?php
$mysqli = new mysqli('localhost', 'root', '', 'books');
$rs = $mysqli->query( 'CALL total_price(@t)' );
$rs = $mysqli->query( 'SELECT @t' );
while($row = $rs->fetch_object())
{
print_r($row);
echo "<br />";
}
?>

The total price : stdClass Object ( [@t] => 4300 )

extension=php_pdo.dll
extension=php_pdo_mysql.dll

<?php
$pdo = new PDO('mysql:dbname=books;host=127.0.0.1', 'root', '');
print 'PDO: simple select';
$pdo->query( 'CALL total_price(@t)' );
foreach($pdo->query( 'SELECT @t' ) as $row)
{
print_r($row);
}
?>

PDO: simple select
Array ( [@t] => 4300 [0] => 4300 )

CREATE PROCEDURE proc_new ()
BEGIN
SELECT * from bookstore where id=3;
END

<?php
//Connecting to MySQL server and to the books database
$pdo = new PDO('mysql:dbname=books;host=127.0.0.1', 'root', '');
print 'Calling the proc_new() stored procedure using PDO';
foreach($pdo->query( 'CALL proc_new()' ) as $row)
{
print_r($row);
}
?>

Calling the proc_new() stored procedure using PDO
Array ( [id] => 3 [0] => 3 
        [book] => Introduction to PHP [1] => 
           Introduction to PHP 
        [author] => Weeling Tom [2] => Weeling Tom 
        [isbn] => 4334-2323-23233-434 [3] => 4334-2323-23233-434 
        [price] => 300 [4] => 300 )