Friday 13 July 2018

PHP MySQL: Call MySQL Stored Procedures

PHP MySQL: Call MySQL Stored Procedures?

Summary: in this tutorial, you will learn how to call MySQL stored procedures using PHP PDO. We will show you how to call stored procedures that return a result set and stored procedures that accept input/output parameters.

Calling stored procedures that return a result set

The steps of calling a MySQL stored procedure that returns a result set using PHP PDO are similar to querying data from MySQL database table using the SELECT statement. Instead of sending a SELECTstatement to MySQL database, you send a stored procedure call statement.

First, create a stored procedure named GetCustomers() in the sample database for the demonstration. The GetCustomers() stored procedure retrieves the name and credit limit of customers from the customers table.

The following GetCustomers() stored procedure illustrates the logic:

1

2

3

4

5

6

7

DELIMITER $$

CREATE PROCEDURE GetCustomers()

BEGIN

 SELECT customerName, creditlimit

 FROM customers;

    END$$

Second, create a new PHP file named phpmysqlstoredprocedure1.php with the following code:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

<!DOCTYPE html>

<html>

    <head>

        <title>PHP MySQL Stored Procedure Demo 1</title>

        <link rel="stylesheet" href="css/table.css" type="text/css" />

    </head>

    <body>

        <?php

        require_once 'dbconfig.php';

        try {

            $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

            // execute the stored procedure

            $sql = 'CALL GetCustomers()';

            // call the stored procedure

            $q = $pdo->query($sql);

            $q->setFetchMode(PDO::FETCH_ASSOC);

        } catch (PDOException $e) {

            die("Error occurred:" . $e->getMessage());

        }

        ?>

        <table>

            <tr>

                <th>Customer Name</th>

                <th>Credit Limit</th>

            </tr>

            <?php while ($r = $q->fetch()): ?>

                <tr>

                    <td><?php echo $r['customerName'] ?></td>

                    <td><?php echo '$' . number_format($r['creditlimit'], 2) ?>

                    </td>

                </tr>

            <?php endwhile; ?>

        </table>

    </body>

</html>

Everything is straightforward except the SQL query:

1

CALL GetCustomers();

We send the statement that calls the GetCustomers() stored procedure to MySQL. And we execute the statement to get a result set.

Third, test the script in the web browser to see how it works.

You can download the script via the following link:

Download PHP MySQL Stored Procedure Source Code

Calling stored procedures with an OUT parameter

It is a little bit tricky to call a stored procedure with the OUT parameter. We will use the GetCustomerLevel() stored procedure that accepts a customer number as an input parameter and returns the customer level based on credit limit.

Check the MySQL IF statement tutorial for detailed information on the GetCustomerLevel() stored procedure.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(

    in  p_customerNumber int(11), 

    out p_customerLevel  varchar(10))

BEGIN

    DECLARE creditlim double;

    SELECT creditlimit INTO creditlim

    FROM customers

    WHERE customerNumber = p_customerNumber;

    IF creditlim > 50000 THEN

    SET p_customerLevel = 'PLATINUM';

    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN

        SET p_customerLevel = 'GOLD';

    ELSEIF creditlim < 10000 THEN

        SET p_customerLevel = 'SILVER';

    END IF;

END$$

In MySQL, we can call the GetCustomerLevel() stored procedure as follows:

1

2

CALL GetCustomerLevel(103,@level);

SELECT @level AS level;

In PHP, we have to emulate those statements:

First, we need to execute the GetCustomerLevel() stored procedure.
Second, to get the customer level, we need to query it from the variable @level . It is important that we must call the method closeCursor() of the PDOStatement object in order to execute the next SQL statement.

Let’s take a look at how the logic is implemented in the following PHP script:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

<?php

require_once 'dbconfig.php';

/**

 * Get customer level

 * @param int $customerNumber

 * @return string

 */

function getCustomerLevel(int $customerNumber) {

    try {

        $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

        // calling stored procedure command

        $sql = 'CALL GetCustomerLevel(:id,@level)';

        // prepare for execution of the stored procedure

        $stmt = $pdo->prepare($sql);

        // pass value to the command

        $stmt->bindParam(':id', $customerNumber, PDO::PARAM_INT);

        // execute the stored procedure

        $stmt->execute();

        $stmt->closeCursor();

        // execute the second query to get customer's level

        $row = $pdo->query("SELECT @level AS level")->fetch(PDO::FETCH_ASSOC);

        if ($row) {

            return $row !== false ? $row['level'] : null;

        }

    } catch (PDOException $e) {

        die("Error occurred:" . $e->getMessage());

    }

    return null;

}

$customerNo = 103;

echo sprintf('Customer #%d is %s', $customerNo, getCustomerLevel($customerNo));

If you test the script in the web browser, you will see the following screenshot:

You can download the script via the following link:

Download PHP MySQL Stored Procedure with the OUT Parameter Source Code

In this tutorial, you have learned how to call MySQL stored procedures using PHP PDO.

Tech Blog

Friday 13 July 2018