Call SQL Stored Procedure with Parameters through PHP

Hi, Lets consider a simple scenario like Username & Password of a basic form should call a stored procedure by passing this textboxes(username & password) values and then output is dispalyed.

Stored Procedure:

CREATE procedure sp_Employee1
(
@username VARCHAR(200),
@password VARCHAR(200)
)
AS
begin
if (select COUNT(*) from Employees where UserID=@username) > 0
select ‘success’ as statusmsg
else
select ‘failure’ as statusmsg
end

Description: This sp will check username from employee data and if it is valid then display success message, else failure message.

PHP File:

<html>
<form method=”post” action=”testsql2.php? run=1″>
<input type=”text” id=”un” name=”un”value=””>
<input type=”password” id=”ps” name=”ps” value=””>
<input type=”submit” value=”Submit” onsubmit=””></form>
</html>
<?php
header(“Cache-Control: no-cache, no-store, must-revalidate, post-check=0, pre-check=0”);
header(“Pragma: no-cache”);
$v1 = isset($_REQUEST[‘run’]) ? $_REQUEST[‘run’] : 0 ;
if($v1==1)
{

$a = $_POST[“un”];
$b = $_POST[“ps”];
}

$serverName = “serverName\instanceName”;

$connectionInfo = array(
‘Database’ => ‘Database’,
‘UID’ => ‘userid’,
‘PWD’ => ‘pwd’);
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
echo “Connection established.<br />”;
}else{
echo “Connection could not be established.<br />”;
die( print_r( sqlsrv_errors(), true));
}

$query = “sp_Employee1 ‘$a’,’$b'”;
$params = array();
$result1 = sqlsrv_query($conn, $query, $params);
while ($result2 = sqlsrv_fetch_array($result1)){

echo $result2[statusmsg].”<br/>”;
}

Output: Show Success/Failure message based on your input(if emp username existed in database, it show success else failure).

Enjoy 🙂