PHP CRUD for Beginners Part-I
29/03/2019 . 7 minutes, 28 seconds to read . Posted by Admin#MVCArchitecture #Model,View,Controller #htmlemail #codeigniteremail #XMLHandling #best-practices
OverView
In the first Part of the Tutorial, we will perform two basics operation of CRUD( Create, Retrieve) using the modern approach PDO(PHP Document Object). PDO is a lightweight PHP extension that provides a data-access abstraction layer.
Requirements
1) XAMPP/WAMP server tool
2) PHP Editor like (Vs Code)
3) PHP Version 5.0 or higher
App Structure
In part 1, we have the following files
- add.php– used for creating a new record. It contains an HTML form where the user can enter details for a new record.
- save.php-used to insert a new record. It contains PHP script to insert a record in the table.
Table Structure
To create the table first we need to create a database. Open localhost/ in your browser or click the given link. Create new Database name it "php_crud"
Make a table "user" in the database and follow the same structure given below in the image.
Insert Record
Make a form and add input name them same as attributes in the database and add action "save.php" and method "POST" to POST values from add.php to save.php
add.php
<!DOCTYPE HTML>
<html>
<head>
<title>PHP CRUD Tutorial</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
<div class="container">
<div class="page-header">
<h1>Create User</h1>
</div>
<form action="save.php" method="post">
<table class='table table-hover table-responsive table-bordered'>
<tr>
<td> First Name</td>
<td><input type='text' name='first_name' class='form-control' /></td>
</tr>
<tr>
<td>Last Name</td>
<td><input type='text' name='last_name' class='form-control' /></td>
</tr>
<tr>
<td>Email</td>
<td><input type='email' name='email' class='form-control' /></td>
</tr>
<tr>
<td></td>
<td>
<input type='submit' value='Save' class='btn btn-primary' />
<a href='index.php' class='btn btn-danger'>Back to User List</a>
</td>
</tr>
</table>
</form>
</div>
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</body>
</html>File Output
Save Record
In this file, we have a PHP script which has the code to get values to form add.php using POST request and insert the data into the table. It includes step by step code
- Database Connection: to connect with the database we need server authentication including (server name, database name, server user name, and server user password) to initialize our connection. In this tutorial, I have used the PDO class to establish a connection with the database. The $con variable contains server authentication details we will use it later to request a connection. The setAttribute is used to validate connection status.
- Get values: As I mentioned above that the form is posted to save.php using POST request/method. We need to get values using POST request and store them in variables.
- Query: in this step, we write insert query. PDO extension has different syntax for writing queries it uses dump parameters in the query, these dump parameters will be bind with value but a bit later after preparing the query. The $con variable has the server authentications details we will prepare the query to be executed on the server by $stmt=$con->prepare($query) the $query variable contain insert query. after preparing the query to bind the values to parameters using $stmt->bindParam which contain query and connection authentications to the server. The execute function is used to run the query on the server and it returns true or false. we have successfully added a record in the database and we will get a message "user added successfully" on the top of the screen.
save.php
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<?php
$hostname='localhost';
$db_name='php_crud';
$username='root';
$password='';
try {
$con = new PDO("mysql:host=$hostname;dbname=$db_name",$username,$password);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
$first_name=$_POST['first_name'];
$last_name=$_POST['last_name'];
$email=$_POST['email'];
$password="123456789";
$status=1;
$query="INSERT INTO user SET first_name=:name, last_name=:lastname, email=:email, password=:password, status=:status";
$stmt=$con->prepare($query);
$stmt->bindParam(':name', $first_name);
$stmt->bindParam(':lastname', $last_name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':password', $password);
$stmt->bindParam(':status', $status);
$res=$stmt->execute();
if($res)
{
echo "<div class='alert alert-success'>User Added SuccessFully.</div>";
echo "<a class='btn btn-md btn-danger' href='index.php'>Go Back </a>";
}
else{
echo "<div class='alert alert-danger'>User Added Failed.</div>";
echo "<a class='btn btn-md btn-danger' href='index.php'>Go Back </a>";
}
?>
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>File Output
View All Records
The file has a PHP script that is used to retrieve all records from the database. It contains a connection and select query. Using the foreach loop we have displayed all records in the table. It uses some additional methods
- rowCount: is the built-in method used to count the number of rows fetched from database.it returns an integer value.
- FetchAll: method is used to convert the retrieved rows into the array.
index.php
<!DOCTYPE HTML>
<html>
<head>
<title>PHP CRUD Tutorial</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
<div class="container">
<div class="page-header">
<h1>Create User</h1>
</div>
<?php
$hostname = 'localhost';
$db_name = 'php_crud';
$username = 'root';
$password = '';
try {
$con = new PDO("mysql:host=$hostname;dbname=$db_name",$username,$password);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "<div class='alert alert-success'>Connection Established Sucessfully.</div>";
}
catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
$query = "SELECT * from user";
$stmt = $con->prepare($query);
$stmt->execute();
?>
<br />
<a href='add.php' class='btn btn-primary m-b-1em'>Create New User</a>
<br />
<table class='table table-hover table-responsive table-bordered'>
<br />
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Action</th>
</tr>
</thead>
<?php
if($stmt->rowCount()>0){
$result=$stmt->fetchAll();
?>
<tbody>
<?php $count=1;
foreach($result as $user){ ?>
<tr>
<td> <?= $count++ ?> </td>
<td> <?= $user['first_name'] ?> </td>
<td> <?= $user['last_name'] ?> </td>
<td> <?= $user['email'] ?> </td>
<td>
<a href='edit.php?uid=<?= $user['id'] ?>' class='btn btn-primary m-r-1em'>Edit</a>
<a href="delete.php?uid=<?= $user['id'] ?>" onclick='return confirm("Do you want to delete")'
class='btn btn-danger'>Delete</a>
</td>
</tr>
<?php } ?>
</tbody>
<?php }
else{
echo "<div class='alert alert-danger'>No records found.</div>";
}
?>
</table>
</div>
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</body>
</html>File Output
IMPORTANT POINTS
- PDO extension works with PHP 5.0 or higher version.
- If you want to run this code on PHP lower version,you need to Install PDO extension.
- Crud App is not completed yet,we will perform remaning two CRUD operations (Update,Delete) later in Part-II of this tutorial.
- email attribute is unique in the "user" table,always enter unique email.
Hope you have learned CRUD in PHP
Thanks