PHP Data Select, View, Insert, update Into MySQL Database with confirmation

In this tutorial, I will guide you to how to select, view, insert, update data in MySQL with Confirmation from the server.

Overview

To continue on this, you will need 6 PHP files mentioned below.
Here, Data.inc.php file contains the necessary code for the confirmation on the queries that are running on MySQL.

  • Add.php
  • Delete.php
  • Index.php
  • Update.php
  • Config.php
  • Data.inc.php

Database Schema

This Code contains the database table creation for SQL. Execute the code in your database to create  tables  for the project.

CREATE TABLE IF NOT EXISTS `crudpdo` (
`id_pdo` int(11) NOT NULL,
`nm_pdo` varchar(45) NOT NULL,
`gd_pdo` varchar(20) NOT NULL,
`tl_pdo` varchar(25) NOT NULL,
`ar_pdo` text NOT NULL
ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `crudpdo`
ADD PRIMARY KEY (`id_pdo`);

ALTER TABLE `crudpdo`
MODIFY `id_pdo` int(11) NOT NULL AUTO_INCREMENT;

Add.php

This PHP file contains code that is necessary to get data from the user (HTML Form) as well as the Server-side code to collect the input and send it to the database handling logic.

When the submit button is clicked, it will send the data to the server using POST method. Then, server will assign the data to local variables which is then Passed to PDO Data handling class which will return  true if data inserted successfully

<?php
include_once ‘includes/config.php’;

$database = new Config();
$db = $database->getConnection();

include_once ‘includes/data.inc.php’;
$product = new Data($db);

?>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”utf-8″>
<meta http-equiv=”X-UA-Compatible” content=”IE=edge”>
<meta name=”viewport” content=”width=device-width, initial-scale=1″>
<title>Tutorial-06</title>

<!– Bootstrap –>
    <link href=”css/bootstrap.min.css” rel=”stylesheet”>
<script src=”https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js”></script>
<script src=”https://oss.maxcdn.com/respond/1.4.2/respond.min.js”></script>

</head>
<body>
<p><br/></p>
<div class=”container”>
<p>
<a class=”btn btn-primary” href=”index.php” role=”button”>Back</a>
</p><br/>
<?php
    if ($_POST) {

$product->name = $_POST[‘name’];
$product->gender = $_POST[‘gender’];
$product->contactNum = $_POST[‘contact’];
$product->address = $_POST[‘address’];

if ($product->create()) {
?>
            <div class=”alert alert-success alert-dismissible” role=”alert”>
<button type=”button” class=”close” data-dismiss=”alert” aria-label=”Close”><span
aria-hidden=”true”>×</span></button>
<strong>Success!</strong>  <a href=”index.php”>View Data</a>.
</div>
<?php
        else {
?>
            <div class=”alert alert-danger alert-dismissible” role=”alert”>
<button type=”button” class=”close” data-dismiss=”alert” aria-label=”Close”><span
aria-hidden=”true”>×</span></button>
<strong>Fail!</strong>
</div>
<?php
        }
}
?>
    <form method=”post”>
<div class=”form-group”>
<label for=”nm”>Name</label>
<input type=”text” class=”form-control” id=”nm” name=”name”>
</div>
<div class=”form-group”>
<label for=”gd”>Gender</label>
<input type=”text” class=”form-control” id=”gd” name=”gender”>
</div>
<div class=”form-group”>
<label for=”tl”>Phone</label>
<input type=”text” class=”form-control” id=”tl” name=”contact”>
</div>
<div class=”form-group”>
<label for=”ar”>Address</label>
<textarea class=”form-control” rows=”3″ id=”ar” name=”address”></textarea>
</div>
<button type=”submit” class=”btn btn-success”>Submit</button>
</form>
</div>

<script src=”js/jquery.min.js”></script>
<script src=”js/bootstrap.min.js”></script>
</body>
</html>

Delete.php

This file also contains the Server-side code needed to make a Delete request on the data.

When the user id is sent to the server in the URL, it will extract that id and send to the PDO query in order to delete the data.

<?php
// check if value was posted
// include database and object file
include_once ‘includes/config.php’;
include_once ‘includes/data.inc.php’;

// get database connection
$database = new Config();
$db = $database->getConnection();

// prepare product object
$product = new Data($db);

// set product id to be deleted
$product->id = isset($_GET[‘id’]) ? $_GET[‘id’] : die(‘Need Product ID’);

// delete the product
if($product->delete()){
echo “<script>location.href=’index.php'</script>”;
}

// if unable to delete the product
else{
echo “<script>alert(‘Failed to Deleted Data’)</script>”;

}
?>

Index.php

This file renders the HTML table containing all the data in the database as well as it allows a user to edit or delete data.

This page issue a read all data request to database using PDO queries and it will send back user with all the available data in the database. The data will be used to populate the form.

<?php
$page = isset($_GET[‘page’]) ? $_GET[‘page’] : 1;

$records_per_page = 5;

$from_record_num = ($records_per_page * $page) – $records_per_page;

include_once ‘includes/config.php’;
include_once ‘includes/data.inc.php’;

$database = new Config();
$db = $database->getConnection();

$product = new Data($db);

$stmt = $product->readAll($page, $from_record_num, $records_per_page);
$num = $stmt->rowCount();

?>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”utf-8″>
<meta http-equiv=”X-UA-Compatible” content=”IE=edge”>
<meta name=”viewport” content=”width=device-width, initial-scale=1″>
<title>Tutorial-06</title>

<link href=”css/bootstrap.min.css” rel=”stylesheet”>

<script src=”https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js”></script>
<script src=”https://oss.maxcdn.com/respond/1.4.2/respond.min.js”></script>

</head>
<body>
<p><br/></p>
<div class=”container”>
<p>
<a class=”btn btn-primary” href=”add.php” role=”button”>Add Data</a>
</p>
<?php
if($num>0){
?>
   <table class=”table table-bordered table-hover table-striped”>
<caption>Personal Data Table</caption>
<thead>
<tr>
<th>#</th>
<th>Name</th>
<th>Gender</th>
<th>Phone</th>
<th>Address</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
extract($row);
?>
<tr>
<?php echo “<td>{$id_pdo}</td>” ?>
   <?php echo “<td>{$nm_pdo}</td>” ?>
   <?php echo “<td>{$gd_pdo}</td>” ?>
   <?php echo “<td>{$tl_pdo}</td>” ?>
   <?php echo “<td>{$ar_pdo}</td>” ?>
   <?php echo “<td width=’100px’>
<a class=’btn btn-warning btn-sm’ href=’update.php?id={$id_pdo}’ role=’button’><span class=’glyphicon glyphicon-pencil’ aria-hidden=’true’></span></a>
<a class=’btn btn-danger btn-sm’ href=’delete.php?id={$id_pdo}’ role=’button’><span class=’glyphicon glyphicon-trash’ aria-hidden=’true’></span></a>
</td>” ?>
</tr>
<?php
}
?>
   </tbody>
</table>
<?php
$page_dom = “index.php”;
include_once ‘includes/pagination.inc.php’;
}
else{
?>
<div class=”alert alert-warning alert-dismissible” role=”alert”>
<button type=”button” class=”close” data-dismiss=”alert” aria-label=”Close”><span aria-hidden=”true”>×</span></button>
<strong>Warning!</strong> Data is still empty
</div>
<?php
}
?>
    </div>
<script src=”js/jquery.min.js”></script>
<script src=”js/bootstrap.min.js”></script>
</body>
</html>

Update.php

This file contains the HTML code that renders the form which is used to update the entries as well as the Server-side code which grab the input from the form along with the code which uses object of Data.inc.php class to perform the update query.

<?php
include_once ‘includes/config.php’;

$id = isset($_GET[‘id’]) ? $_GET[‘id’] : die(‘Need Product ID’);

$database = new Config();
$db = $database->getConnection();

include_once ‘includes/data.inc.php’;
$product = new Data($db);

$product->id = $id;
$product->readOne();
?>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”utf-8″>
<meta http-equiv=”X-UA-Compatible” content=”IE=edge”>
<meta name=”viewport” content=”width=device-width, initial-scale=1″>
<title>Tutorial-06</title>
<link href=”css/bootstrap.min.css” rel=”stylesheet”>

<script src=”https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js”></script>
<script src=”https://oss.maxcdn.com/respond/1.4.2/respond.min.js”></script>

</head>
<body>
<p><br/></p>
<div class=”container”>
<p>
<a class=”btn btn-primary” href=”index.php” role=”button”>Back</a>
</p><br/>
<?php
if($_POST){

$product->name = $_POST[‘name’];
$product->gender = $_POST[‘gender’];
$product->contactNum = $_POST[‘contact’];
$product->address = $_POST[‘address’];

if($product->update()){
?>
<script>window.location.href=’index.php'</script>
<?php
   }else{
?>
<div class=”alert alert-danger alert-dismissible” role=”alert”>
<button type=”button” class=”close” data-dismiss=”alert” aria-label=”Close”><span aria-hidden=”true”>×</span></button>
<strong>Fail!</strong>
</div>
<?php
   }
}
?>
<form method=”post”>
<div class=”form-group”>
<label for=”nm”>Name</label>
<input type=”text” class=”form-control” id=”nm” name=”name” value=’<?php echo $product->name; ?>‘>
</div>
<div class=”form-group”>
<label for=”gd”>Gender</label>
<input type=”text” class=”form-control” id=”gd” name=”gender” value=’<?php echo $product->gender; ?>‘>
</div>
<div class=”form-group”>
<label for=”tl”>Phone</label>
<input type=”text” class=”form-control” id=”tl” name=”contact” value=’<?php echo $product->contactNum; ?>‘>
</div>
<div class=”form-group”>
<label for=”ar”>Address</label>
<textarea class=”form-control” rows=”3″ id=”ar” name=”address”><?php echo $product->address; ?></textarea>
</div>
<button type=”submit” class=”btn btn-success”>Submit</button>
</form>
</div>

<script src=”js/jquery.min.js”></script>
<script src=”js/bootstrap.min.js”></script>

</body>
</html>

Config.php

This file contains the database connection configuration and also the PDO object creation using the same MySQL configuration.

<?php

class Config
{

// specify your own database credentials
private $host = “localhost”;
private $db_name = “biodata”;
private $username = “root”;
private $password = “”;
public $conn;

// get the database connection
public function getConnection()
{

$this->conn = null;

try {
$this->conn = new PDO(“mysql:host=” . $this->host . “;dbname=” . $this->db_name, $this->username, $this->password);
catch (PDOException $exception) {
echo “Connection error: ” . $exception->getMessage();
}

return $this->conn;
}
}

?>

Data.inc.php

This file contains the Server-side logic for all the CRUD operations that are performed by other files. It uses confirmation from the MySQL PDO in order to verify that changes are successfully propagated towards the database.

<?php

class Data
{

// database connection and table name
private $conn;
private $table_name = “crudpdo”;

// object properties
public $id;
public $name;
public $gender;
public $contactNum;
public $address;

public function __construct($db)
{
$this->conn = $db;
}

// create product
function create()
{

//write query
$query = “INSERT INTO ” . $this->table_name . ” values(”,?,?,?,?)”;

$stmt = $this->conn->prepare($query);

$stmt->bindParam(1, $this->name);
$stmt->bindParam(2, $this->gender);
$stmt->bindParam(3, $this->contactNum);
$stmt->bindParam(4, $this->address);

if ($stmt->execute()) {
return true;
else {
return false;
}

}

// read products
function readAll($page, $from_record_num, $records_per_page)
{

$query = “SELECT * FROM  ” . $this->table_name . ” ORDER BY nm_pdo ASC LIMIT {$from_record_num}, {$records_per_page}”;

$stmt = $this->conn->prepare($query);

if($stmt->execute()){
return $stmt;
}
else{
return false;
}

}

// used for paging products
public function countAll()
{

$query = “SELECT id_pdo FROM ” . $this->table_name . “”;

$stmt = $this->conn->prepare($query);

if($stmt->execute()) {

$num = $stmt->rowCount();

return $num;
}else{
return false;
}
}

// used when filling up the update product form
function readOne()
{

$query = “SELECT * FROM  ” . $this->table_name . ” WHERE id_pdo = ? LIMIT 0,1″;

$stmt = $this->conn->prepare($query);
$stmt->bindParam(1, $this->id);

if($stmt->execute()) {

$row = $stmt->fetch(PDO::FETCH_ASSOC);

$this->name = $row[‘nm_pdo’];
$this->gender = $row[‘gd_pdo’];
$this->contactNum = $row[‘tl_pdo’];
$this->address = $row[‘ar_pdo’];
}
else{
return false;
}
}

// update the product
function update()
{

$query = “UPDATE
” . $this->table_name . ”
SET
nm_pdo = :nm,
gd_pdo = :gd,
tl_pdo = :tl,
ar_pdo = :ar
WHERE
id_pdo = :id”;

$stmt = $this->conn->prepare($query);

$stmt->bindParam(‘:nm’, $this->name);
$stmt->bindParam(‘:gd’, $this->gender);
$stmt->bindParam(‘:tl’, $this->contactNum);
$stmt->bindParam(‘:ar’, $this->address);
$stmt->bindParam(‘:id’, $this->id);

// execute the query
if ($stmt->execute()) {
return true;
else {
return false;
}
}

// delete the product
function delete()
{

$query = “DELETE FROM ” . $this->table_name . ” WHERE id_pdo = ?”;

$stmt = $this->conn->prepare($query);
$stmt->bindParam(1, $this->id);

if ($result = $stmt->execute()) {
return true;
else {
return false;
}
}
}

?>

In this file, the validation that is done with the execute(); keyword will return true if the transaction is complete and if there is a problem, it will return false with an available PDO Exception.

Let me know your experience on this project.

You can download the complete source code here,

Don’t forget to share the post in your community with your friends. I really appreciate it.

Download the complete source code.

Grab the the free working demo Intantly!

You have Successfully Suscribed!

2 thoughts on “PHP Data Select, View, Insert, update Into MySQL Database with confirmation

  1. hi i am trying to do is using joins post data from php to database. please help me in that I have database and i am able to post the data with post method from php to db and in sql query i am getting joins using joins query but i want using joins post data from php to db.

    1. Hello Mohan,

      Are you trying to fetch the data using join or inserting post data into two separate table? If you are trying to insert data in two separate table, you can set one table as primary and another as foreign key constraint.

Leave a Reply

Your email address will not be published. Required fields are marked *