Wondering how to use stored procedures with PHP and MySQL? So was I and here’s what I’ve learned. In this tutorial I’ll explain how to use PHP (I’m using 5.2.6) to call MySQL (I’m using 5.0.2) stored procedures using the following database extensions:
- MySQL - http://us.php.net/manual/en/book.mysql.php
- MySQLi - http://uk2.php.net/manual/en/class.mysqli.php
- PDO - 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 (I’ll be using the MySQL Query Browser) 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;
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;
