close
CRUD operations are used for the data manipulation in the database. It is very useful for data management in web applications. The CRUD (Create, Read, Update, and Delete) operations can be implemented easily with PHP. We have already shared the tutorial to perform create (insert), read (select), update, and delete operations in PHP CRUD Operations using with MySQL. In this tutorial, we will create PHP application to perform CRUD operations with MS SQL server.
We will connect with the Microsoft SQL Server from PHP script to select, insert, update, and delete data. This means the SQL server will be used as a database for this CRUD application. You can manipulate data in the SQL server from a PHP application with CRUD operations. We will use the PDO_SQLSRV driver of the Microsoft Drivers to access the SQL Server from PHP script.
We will implement the following functionality in this PHP CRUD with SQL server script.
Before getting started to create a CRUD application with MSSQL and PHP, take a look at the file structure.
php_crud_with_mssql_server/ ├── index.php ├── addEdit.php ├── userAction.php ├── dbConfig.php ├── bootstrap/ │ └── bootstrap.min.css ├── css/ │ └── style.css └── images/
A Microsoft driver is required to connect the SQL server from PHP. There is a driver named PDO_SQLSRV available in Microsoft Drivers for PHP. You need to install the Microsoft SQL Server PDO Driver (PDO_SQLSRV) in your PHP server and enable it in the PHP configuration file (php.ini). For a step-by-step guide see How to Install and Enable Microsoft SQL Server PDO Driver (PDO_SQLSRV) in PHP
To store the data a table is required in the MS SQL database. The following SQL creates a Members table with some basic fields in the MS SQL database server.
CREATE TABLE [dbo].[Members](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[Created] [datetime] NULL
) ON [PRIMARY]
The dbConfig.php file is used to specify the SQL server credentials and create a connection with the MS SQL server using the PDO class.
$serverName – Name of the SQL server$dbUsername – Database username$dbPassword – Database user password$dbName – Database name<?php
// SQL server configuration
$serverName = "localhost\SQLEXPRESS";
$dbUsername = "";
$dbPassword = "";
$dbName = "codexworld";
// Create database connection
try {
$conn = new PDO( "sqlsrv:Server=$serverName;Database=$dbName", $dbUsername, $dbPassword);
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch( PDOException $e ) {
die( "Error connecting to SQL Server: ".$e->getMessage() );
}
The userAction.php file performs the CRUD operations using PHP and SQL database server. The code blocks are executed based on the requested action.
Add/Edit Record:
When the add/edit form is submitted and userSubmit parameter exists in the $_POST method, the pointer is entered in this code block.
MemberID is supplied, update data in the SQL server using the prepare() and execute() methods of the PDO class. Otherwise, insert data in the SQL server using the PDO class methods.Delete Records:
If delete is requested in action_type, remove data from the SQL server based on the id passed in the query string.
<?php
// Start session
if(!session_id()){
session_start();
}
// Include database configuration file
require_once 'dbConfig.php';
// Set default redirect url
$redirectURL = 'index.php';
if(isset($_POST['userSubmit'])){
// Get form fields value
$MemberID = $_POST['MemberID'];
$FirstName = trim(strip_tags($_POST['FirstName']));
$LastName = trim(strip_tags($_POST['LastName']));
$Email = trim(strip_tags($_POST['Email']));
$Country = trim(strip_tags($_POST['Country']));
$id_str = '';
if(!empty($id)){
$id_str = '?id='.$MemberID;
}
// Fields validation
$errorMsg = '';
if(empty($FirstName)){
$errorMsg .= '<p>Please enter your first name.</p>';
}
if(empty($LastName)){
$errorMsg .= '<p>Please enter your last name.</p>';
}
if(empty($Email) || !filter_var($Email, FILTER_VALIDATE_EMAIL)){
$errorMsg .= '<p>Please enter a valid email.</p>';
}
if(empty($Country)){
$errorMsg .= '<p>Please enter country name.</p>';
}
// Submitted form data
$userData = array(
'FirstName' => $FirstName,
'LastName' => $LastName,
'Email' => $Email,
'Country' => $Country
);
// Store the submitted field values in the session
$sessData['userData'] = $userData;
// Process the form data
if(empty($errorMsg)){
if(!empty($MemberID)){
// Update data in SQL server
$sql = "UPDATE Members SET FirstName = ?, LastName = ?, Email = ?, Country = ? WHERE MemberID = ?";
$query = $conn->prepare($sql);
$update = $query->execute(array($FirstName, $LastName, $Email, $Country, $MemberID));
if($update){
$sessData['status']['type'] = 'success';
$sessData['status']['msg'] = 'Member data has been updated successfully.';
// Remove submitted field values from session
unset($sessData['userData']);
}else{
$sessData['status']['type'] = 'error';
$sessData['status']['msg'] = 'Some problem occurred, please try again.';
// Set redirect url
$redirectURL = 'addEdit.php'.$id_str;
}
}else{
// Insert data in SQL server
$sql = "INSERT INTO Members (FirstName, LastName, Email, Country, Created) VALUES (?,?,?,?,?)";
$params = array(
&$FirstName,
&$LastName,
&$Email,
&$Country,
date("Y-m-d H:i:s")
);
$query = $conn->prepare($sql);
$insert = $query->execute($params);
if($insert){
//$MemberID = $conn->lastInsertId();
$sessData['status']['type'] = 'success';
$sessData['status']['msg'] = 'Member data has been added successfully.';
// Remove submitted field values from session
unset($sessData['userData']);
}else{
$sessData['status']['type'] = 'error';
$sessData['status']['msg'] = 'Some problem occurred, please try again.';
// Set redirect url
$redirectURL = 'addEdit.php'.$id_str;
}
}
}else{
$sessData['status']['type'] = 'error';
$sessData['status']['msg'] = '<p>Please fill all the mandatory fields.</p>'.$errorMsg;
// Set redirect url
$redirectURL = 'addEdit.php'.$id_str;
}
// Store status into the session
$_SESSION['sessData'] = $sessData;
}elseif(($_REQUEST['action_type'] == 'delete') && !empty($_GET['id'])){
$MemberID = $_GET['id'];
// Delete data from SQL server
$sql = "DELETE FROM Members WHERE MemberID = ?";
$query = $conn->prepare($sql);
$delete = $query->execute(array($MemberID));
if($delete){
$sessData['status']['type'] = 'success';
$sessData['status']['msg'] = 'Member data has been deleted successfully.';
}else{
$sessData['status']['type'] = 'error';
$sessData['status']['msg'] = 'Some problem occurred, please try again.';
}
// Store status into the session
$_SESSION['sessData'] = $sessData;
}
// Redirect to the respective page
header("Location:".$redirectURL);
exit();
?>
We will use the Bootstrap library to make the data list table, form, buttons, and links look better. If you want to use custom style for these UI elements, it can omit to include.
Include the CSS file of the Bootstrap library.
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">
In the index.php file, the records are retrieved from the SQL server using PHP and listed in a tabular format with Add, Edit, and Delete options.
addEdit.php page to perform the Create operation.addEdit.php page to perform the Update operation.userAction.php file with action_type=delete and id params. In the userAction.php file, the record is deleted from the SQL server based on the unique identifier (MemberID).<?php
// Start session
if(!session_id()){
session_start();
}
// Retrieve session data
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:'';
// Get status message from session
if(!empty($sessData['status']['msg'])){
$statusMsg = $sessData['status']['msg'];
$statusMsgType = $sessData['status']['type'];
unset($_SESSION['sessData']['status']);
}
// Include database configuration file
require_once 'dbConfig.php';
// Fetch the data from SQL server
$sql = "SELECT * FROM Members ORDER BY MemberID DESC";
$query = $conn->prepare($sql);
$query->execute();
$members = $query->fetchAll(PDO::FETCH_ASSOC);
?>
<!-- Display status message -->
<?php if(!empty($statusMsg) && ($statusMsgType == 'success')){ ?>
<div class="col-xs-12">
<div class="alert alert-success"><?php echo $statusMsg; ?></div>
</div>
<?php }elseif(!empty($statusMsg) && ($statusMsgType == 'error')){ ?>
<div class="col-xs-12">
<div class="alert alert-danger"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>
<div class="row">
<div class="col-md-12 head">
<h5>Members</h5>
<!-- Add link -->
<div class="float-right">
<a href="addEdit.php" class="btn btn-success"><i class="plus"></i> New Member</a>
</div>
</div>
<!-- List the members -->
<table class="table table-striped table-bordered">
<thead class="thead-dark">
<tr>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Country</th>
<th>Created</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php if(!empty($members)){ $count = 0; foreach($members as $row){ $count++; ?>
<tr>
<td><?php echo $count; ?></td>
<td><?php echo $row['FirstName']; ?></td>
<td><?php echo $row['LastName']; ?></td>
<td><?php echo $row['Email']; ?></td>
<td><?php echo $row['Country']; ?></td>
<td><?php echo $row['Created']; ?></td>
<td>
<a href="addEdit.php?id=<?php echo $row['MemberID']; ?>" class="btn btn-warning">edit</a>
<a href="userAction.php?action_type=delete&id=<?php echo $row['MemberID']; ?>" class="btn btn-danger" onclick="return confirm('Are you sure to delete?');">delete</a>
</td>
</tr>
<?php } }else{ ?>
<tr><td colspan="7">No member(s) found...</td></tr>
<?php } ?>
</tbody>
</table>
</div>
The addEdit.php handles the create and update form functionality.
userAction.php file to insert/update the record in the SQL server.<?php
// Start session
if(!session_id()){
session_start();
}
// Retrieve session data
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:'';
// Get status message from session
if(!empty($sessData['status']['msg'])){
$statusMsg = $sessData['status']['msg'];
$statusMsgType = $sessData['status']['type'];
unset($_SESSION['sessData']['status']);
}
// Get member data
$memberData = $userData = array();
if(!empty($_GET['id'])){
// Include database configuration file
require_once 'dbConfig.php';
// Fetch data from SQL server by row ID
$sql = "SELECT * FROM Members WHERE MemberID = ".$_GET['id'];
$query = $conn->prepare($sql);
$query->execute();
$memberData = $query->fetch(PDO::FETCH_ASSOC);
}
$userData = !empty($sessData['userData'])?$sessData['userData']:$memberData;
unset($_SESSION['sessData']['userData']);
$actionLabel = !empty($_GET['id'])?'Edit':'Add';
?>
<!-- Display status message -->
<?php if(!empty($statusMsg) && ($statusMsgType == 'success')){ ?>
<div class="col-xs-12">
<div class="alert alert-success"><?php echo $statusMsg; ?></div>
</div>
<?php }elseif(!empty($statusMsg) && ($statusMsgType == 'error')){ ?>
<div class="col-xs-12">
<div class="alert alert-danger"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>
<div class="row">
<div class="col-md-12">
<h2><?php echo $actionLabel; ?> Member</h2>
</div>
<div class="col-md-6">
<form method="post" action="userAction.php">
<div class="form-group">
<label>First Name</label>
<input type="text" class="form-control" name="FirstName" placeholder="Enter your first name" value="<?php echo !empty($userData['FirstName'])?$userData['FirstName']:''; ?>" required="">
</div>
<div class="form-group">
<label>Last Name</label>
<input type="text" class="form-control" name="LastName" placeholder="Enter your last name" value="<?php echo !empty($userData['LastName'])?$userData['LastName']:''; ?>" required="">
</div>
<div class="form-group">
<label>Email</label>
<input type="email" class="form-control" name="Email" placeholder="Enter your email" value="<?php echo !empty($userData['Email'])?$userData['Email']:''; ?>" required="">
</div>
<div class="form-group">
<label>Country</label>
<input type="text" class="form-control" name="Country" placeholder="Enter country name" value="<?php echo !empty($userData['Country'])?$userData['Country']:''; ?>" required="">
</div>
<a href="index.php" class="btn btn-secondary">Back</a>
<input type="hidden" name="MemberID" value="<?php echo !empty($userData['MemberID'])?$userData['MemberID']:''; ?>">
<input type="submit" name="userSubmit" class="btn btn-success" value="Submit">
</form>
</div>
</div>
This example script helps you to perform select/insert/update/delete options in the MS SQL database with PHP. If the existing application already used SQL server and wants to add some functionality with PHP, you don’t need to change the database to MySQL. Use this CRUD script to list, view, add, edit, update, and delete functionality with SQL server using PHP.
Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request
I have purchased the script, and would like to include pagination, eg. Previous 1/2/3/4/5 Next etc, and a search function in the index.php file. Is that something you could help me with?