How to use stored procedure in PHP
- Create procedure
delimiter //
CREATE PROCEDURE selectOrders(IN user varchar(255), IN keyword varchar(255))
BEGIN
SELECT * from orders where username=user and ordername =keyword;
END;
- Check procedure
show procedure status;
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| shopping | selectOrders | PROCEDURE | root@localhost | 2017-04-14 17:37:18 | 2017-04-14 17:37:18 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
show create procedure selectOrders;
---------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+--------------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| selectOrders | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `selectOrders`(IN user varchar(255), IN keyword varchar(255))
BEGIN
SELECT * from orders where username=user and ordername =keyword;
END | utf8 | utf8_general_ci | latin1_swedish_ci |
+--------------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
call selectOrders('User1','Buy books');
+----+----------+-----------+--------+---------------------+---------------------+--------+--------+
| id | username | ordername | amount | create_time | update_time | extra1 | extra2 |
+----+----------+-----------+--------+---------------------+---------------------+--------+--------+
| 1 | User1 | Buy books | 5000 | 2017-04-07 14:07:30 | 2017-04-07 14:07:30 | NULL | NULL |
+----+----------+-----------+--------+---------------------+---------------------+--------+--------+
- Call procedure from PHP (PDO)
<?php
$ds = "mysql:host=localhost;dbname=shopping";
$user = "root";
$pass = "root";
$dbh = new PDO($ds, $user, $pass);
$stmt = $dbh->prepare("call selectOrders(?,?)");
$stmt->bindParam(1,$username,PDO::PARAM_STR);
$stmt->bindParam(2,$keyword,PDO::PARAM_STR);
$stmt->execute();
$query = $stmt->queryString;
while($row = $stmt->fetch()){
print $row["username"];
print $row["ordername"];
print $row["amount"];
}
?>
Recent Comments