Blog » PHP » insert update select and delete using php mysqli

insert update select and delete using php mysqli

In this article will show you how to create a simple insert,update,select and delete script using PHP and MySQLi database. you can get idea to how to function it's in php mysqli.

creating database, table name "product"


CREATE TABLE IF NOT EXISTS `product` (
`id` int(55) NOT NULL,
 `title` varchar(100) NOT NULL,
 `description` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;


creating database connection config.php


<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "demo_product";
$con = new mysqli($servername, $username, $password, $database);
if($con->connect_error){
die("connection failed<br/>");
}
?>


insert.php file


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Insert update delete and select</title>
</head>
<body>
<style>
table, th, td{
	border: 1px solid #eee;
	border-collapse:collapse;
	margin-top:4px;
	
}
th, td{
	padding:7px;
	text-align:left;
	
}
input{
	border: 1px solid #ccc;
}
.btn{
	border: 1px solid #ccc;
}
</style>

<?php 
include("config.php");
if($_SERVER['REQUEST_METHOD']== "POST"){
$title = $_POST['title'];
$description = $_POST['description'];
//create a prepared statement
$stmt = $con->prepare("insert into product (title,description) values ( ?, ?)");
//bind parameters
$stmt->bind_param("ss", $title,$description);
//execute command
if($stmt->execute()){
header("location: insert.php");
}else{
echo "insert failed<br/>";
}
}
?>

<form action="" method="post" enctype="multipart/form-data" name="form" id="form">
<label>Title</label><br />
<input style="padding:5px;" type="text"name="title"required /><br />
<label>Description</label><br />
<input style="padding:5px;" type="text"name="description"required />
<button type="submit" style="padding:5px;" class="btn">Insert</button>
</form>

<table>
<tr>
<th scope="col">Title</th>
<th scope="col">Description</th>
<th scope="col" colspan="2">Options</th>
</tr>

<?php
include("config.php");
if ($stmt = $con->prepare("SELECT * FROM product order by id desc")){
$stmt->execute();
}
$result = $stmt->get_result();
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
?>

<tr>
<td><?php echo $row['title'];?></td>
<td><?php echo $row['description'];?></td> 
<td><a href="edit.php?editid=<?php echo $row['id'];?>">Edit</a> | 
<a href="delete.php?delete=<?php echo $row['id'];?>">delete</a></td> 
</tr>
<?php 
}}else{
echo"not found";
}
?>
</table>
</body>
</html>


edit.php file


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Edit products</title>
</head>
<body>
<style>
table, th, td{
	border: 1px solid #eee;
	border-collapse:collapse;
	margin-top:4px;
	
}
th, td{
	padding:7px;
	text-align:left;
	
}
input{
	border: 1px solid #ccc;
}
.btn{
	border: 1px solid #ccc;
}
</style>

<?php
include("config.php");
if(isset($_GET['editid'])){
$id=$_GET['editid'];
$stmt = $con->prepare("SELECT title,description FROM product WHERE id=? ");
$stmt->bind_param('s', $id);
$stmt->execute();
$stmt->bind_result($p_title, $p_description);
if($stmt->fetch()){
$stmt->close();
if($_SERVER['REQUEST_METHOD'] == "POST"){
$title=$_POST['title'];
$description=$_POST['description'];
$stmt = $con->prepare("UPDATE product SET title=?, description=? WHERE id=?"); 
$stmt->bind_param("sss", $title, $description, $id);	
if($stmt->execute()){
header("location: insert.php");
}else{
echo "Failed to update product<br/>";
}
}
?>

<form action="" method="post" enctype="multipart/form-data" name="form" id="form">
<label>Title</label><br />
<input style="padding:5px;" type="text"name="title"value="<?php echo $p_title; ?>" /><br />
<label>Description</label><br />
<input style="padding:5px;" type="text"name="description"value="<?php echo $p_description; ?>" />
<button type="submit" style="padding:5px; width:53px;" class="btn">Update</button>
</form>
<?php 
}
}
?>

<table>
<tr>
<th scope="col">Title</th>
<th scope="col">Description</th>
<th scope="col" colspan="2">Options</th>
</tr>

<?php
include("config.php");
if ($stmt = $con->prepare("SELECT * FROM product order by id desc")){
$stmt->execute();
}
$result = $stmt->get_result();
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
?>

<tr>
<td><?php echo $row['title'];?></td>
<td><?php echo $row['description'];?></td> 
<td><a href="edit.php?editid=<?php echo $row['id'];?>">Edit</a> | 
<a href="delete.php?delete=<?php echo $row['id'];?>">delete</a></td> 
</tr><?php }}?>
</table>

</body>
</html>


delete.php file


<?php
include("config.php");
if (isset($_GET['delete'])) {
$id = $_GET['delete'];
}
if($stmt = $con->prepare("delete from product where id=?")){
$stmt->bind_param("s", $id);
$stmt->execute();	
if($stmt){
header("location: insert.php");
}else{
echo "Failed to delete product<br/>";
}
}
?>


You can download complete source code below link.

ARTICLE POSTED BY : Admin

Hi, I'am k.g uththara. I'm a professional web developer. I write blog posts in my free time. if you want to contact me, send a message on contact page, thanks.