{"id":4434,"date":"2024-07-31T10:39:53","date_gmt":"2024-07-31T03:39:53","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4434"},"modified":"2024-08-18T10:16:42","modified_gmt":"2024-08-18T03:16:42","slug":"php-sql-server-call-stored-procedure","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/php-sql-server\/php-sql-server-call-stored-procedure\/","title":{"rendered":"PHP SQL Server: Calling Stored Procedures"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to call a stored procedure from SQL Server using PHP.<\/p>\n\n\n\n

This tutorial begins where\u00a0performing a transaction using PHP tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

Creating a stored procedure #<\/a><\/h2>\n\n\n\n

Step 1. Launch the SQL Server Management Studio (SSMS) and connect to the BookStore<\/code> database.<\/p>\n\n\n\n

Step 2. Execute the following statement to create a new stored procedure that retrieves authors by birth date between start and end dates:<\/p>\n\n\n

CREATE<\/span> PROCEDURE<\/span> GetAuthorsByBirthDate\n    @StartDate DATE<\/span>,\n    @EndDate DATE<\/span>\nAS<\/span>\nBEGIN<\/span>\n    SET<\/span> NOCOUNT ON<\/span>;\n\n    SELECT<\/span> \n        AuthorID,\n        FirstName,\n        LastName,\n        BirthDate\n    FROM<\/span> \n        Authors<\/span>\n    WHERE<\/span> \n        BirthDate BETWEEN<\/span> @StartDate AND<\/span> @EndDate\n    ORDER<\/span> BY<\/span> \n        BirthDate;\nEND<\/span>;\nGO<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Calling the stored procedure from PHP #<\/a><\/h2>\n\n\n\n
Define a method findAuthorsByBirthDate<\/code> in the AuthorDB<\/code> class to call the GetAuthorsByBirthDate<\/code> stored procedure:<\/p>\n\n\n
function<\/span> findAuthorsByBirthDate<\/span>($startDate, $endDate)<\/span>\n<\/span>{\n    $authors = [];\n\n    try<\/span> {\n        $stmt = $this<\/span>->conn->prepare(\"EXEC GetAuthorsByBirthDate :startDate, :endDate\"<\/span>);\n        $stmt->bindParam(':startDate'<\/span>, $startDate);\n        $stmt->bindParam(':endDate'<\/span>, $endDate);\n\n        $stmt->execute();\n\n        while<\/span> ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {\n            $authors[] = new<\/span> Author(\n                $row['FirstName'<\/span>],\n                $row['LastName'<\/span>],\n                $row['BirthDate'<\/span>],\n                $row['AuthorID'<\/span>]\n            );\n        }\n    } catch<\/span> (PDOException $e) {\n        throw<\/span> new<\/span> Exception<\/span>(\"Error fetching authors: \"<\/span> . $e->getMessage());\n    }\n\n    return<\/span> $authors;\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
Step 1. Define a public method findAuthorsByBirthDate<\/code> that finds authors with the birth dates between start and end dates:<\/p>\n\n\n
function<\/span> findAuthorsByBirthDate<\/span>($startDate, $endDate)<\/span><\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Initialize an array to store the Author<\/code> objects created from the data of the Authors<\/code> table:<\/p>\n\n\n
$authors = [];<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Create a prepared statement that executes a call to the GetAuthorsByBirthDate<\/code> stored procedure.<\/p>\n\n\n
$stmt = $this<\/span>->conn->prepare(\"EXEC GetAuthorsByBirthDate :startDate, :endDate\"<\/span>);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The :startDate<\/code> and :endDate<\/code> are placeholders for query parameters, which help prevent SQL injections.<\/p>\n\n\n\n
Step 4. Bind the values for the :startDate<\/code> and :endDate<\/code> parameters:<\/p>\n\n\n
$stmt->bindParam(':startDate'<\/span>, $startDate);\n$stmt->bindParam(':endDate'<\/span>, $endDate);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 5. Execute the prepared statement by calling the execute() method:<\/p>\n\n\n
$stmt->execute();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 6. Fetch rows, create an Author for each row, and add it to the $authors array:<\/p>\n\n\n
while<\/span> ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {\n   $authors[] = new<\/span> Author(\n                $row['FirstName'<\/span>],\n                $row['LastName'<\/span>],\n                $row['BirthDate'<\/span>],\n                $row['AuthorID'<\/span>]\n            );\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 7. Return the $authors array:<\/p>\n\n\n
return<\/span> $authors<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Running the app #<\/a><\/h2>\n\n\n\n
Modify the index.php<\/code> file to call the findAuthorsByBirthDate<\/code> method:<\/p>\n\n\n
<?php<\/span>\n\nrequire_once<\/span> 'authordb.php'<\/span>;\n\n\/\/ Connect to the SQL Server<\/span>\n$conn = require_once<\/span> 'connect.php'<\/span>;\n\n$authorDB = new<\/span> AuthorDB($conn);\n\n\/\/ Find authors between birth dates<\/span>\n$authors = $authorDB->findAuthorsByBirthDate('1980-01-01'<\/span>, '1986-12-31'<\/span>);\n\nforeach<\/span> ($authors as<\/span> $author) {\n    echo<\/span> $author->getFirstName() . ' '<\/span> .\n         $author->getLastName() . ' : '<\/span> .\n         $author->getBirthDate() . '<br>'<\/span>;\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Donna Bailey : 1980<\/span>-11<\/span>-20<\/span>\nLisa Savage : 1981<\/span>-04<\/span>-13<\/span>\nDavid Hunter : 1983<\/span>-08<\/span>-01<\/span>\nKaren Simmons : 1986<\/span>-06<\/span>-19<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, load the authordb.php<\/code> file to use the AuthorDB<\/code> class.<\/p>\n\n\n
require_once<\/span> 'authordb.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
$conn = require_once<\/span> 'connect.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, create a new AuthorDB<\/code> object:<\/p>\n\n\n
$authorDB = new<\/span> AuthorDB($conn);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, call the findAuthorsByBirthDate()<\/code> method to retrieve authors whose birth dates are between '1980-01-01'<\/code> and '1986-12-31'<\/code>:<\/p>\n\n\n
$authors = $authorDB->findAuthorsByBirthDate('1980-01-01'<\/span>, '1986-12-31'<\/span>);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, display author’s information:<\/p>\n\n\n
foreach<\/span> ($authors as<\/span> $author) {\n    echo<\/span> $author->getFirstName() . ' '<\/span> .\n         $author->getLastName() . ' : '<\/span> .\n         $author->getBirthDate() . '<br>'<\/span>;\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Execute the EXEC<\/code> statement to call a stored procedure from SQL Server in PHP.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, connect to the SQL Server<\/a> and assign the returned PDO object to the $conn<\/code> variable:<\/p>\n\n\n