PHP MySQL Select, Insert, Update, Delete

PHP MySQL Select, Insert, Update, Delete

Instructor-svgAl-Mamun Sarkar
Apr 18 , 2020
arrow_iconPrevious
Next arrow_icon
  • Share On:
  • fb
  • twitter
  • pinterest
  • instagram

এই lesson এ আমার দেখব কিভাবে PHP দিয়ে MySQL Database থেকে Data Select করতে হয়, Data Insert করতে হয়, Data Update করতে হয়, Data Delete করতে হয়। 

 

Connect.php

$conn = new mysqli('localhost', 'root', '', 'school');

 

Index.php

<?php

require 'connect.php';

$sql = 'SELECT * FROM students';
$result = $conn->query($sql);
session_start();
?>


<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title> Students </title>
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

</head>
<body>

<div class="container">
	
	<div class="row clearfix">
		<div class="col-md-2">
			
		</div>
		<div class="col-md-8">
			
			<?php if( isset($_SESSION['msg']) ) { ?>
				<br>
				<div class="alert alert-success" role="alert">
				  <?php echo $_SESSION['msg']; ?>
				</div>
			<?php  session_destroy(); } ?>

			<h1> Student list </h1>

			<a href="insert.php" class="btn btn-primary"> Add Student </a>
			<br><br>
			<table class="table table-bordered table-sm">
				<thead>
					<tr>
						<th>ID</th>
						<th>Name</th>
						<th>Roll</th>
						<th>Reg</th>
						<th>Date of Birth</th>
						<th>Action</th>
					</tr>
				</thead>
				<tbody>
				
				<?php while ($row = $result->fetch_assoc()) { ?>
					<tr>
						<td><?php echo $row['id']; ?></td>
						<td><?php echo $row['name']; ?></td>
						<td><?php echo $row['roll']; ?></td>
						<td><?php echo $row['reg']; ?></td>
						<td><?php echo $row['dob']; ?></td>
						<td>
							<a href="show.php?id=<?php echo $row['id']; ?>" class="btn btn-success btn-sm"> View </a>
							<a href="edit.php?id=<?php echo $row['id']; ?>" class="btn btn-primary btn-sm"> Edit </a>
							<a href="delete.php?id=<?php echo $row['id']; ?>" onclick="return confirm('Are You Sure?')" class="btn btn-danger btn-sm"> Delete </a>
						</td>
					</tr>
				<?php } ?>

				</tbody>
				

			</table>


</div>
</div>
</div>
	
</body>
</html>

 

Insert.php


<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title> Students </title>
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

</head>
<body>

<div class="container">
	<div class="row clearfix">
		<div class="col-md-3"></div>
		<div class="col-md-6">
			<h1> Add new student </h1>
			
			<form action="store.php" method="post">
			  
			  <div class="form-group">
			    <label for="name">Name</label>
			    <input type="text" class="form-control" name="name" placeholder="Enter Name">
			  </div>

			  <div class="form-group">
			    <label for="roll">Roll</label>
			    <input type="text" class="form-control" name="roll" placeholder="Enter Roll">
			  </div>

			  <div class="form-group">
			    <label for="roll">Reg</label>
			    <input type="text" class="form-control" name="reg" placeholder="Enter reg">
			  </div>

			  <div class="form-group">
			    <label for="roll">Date of Birth</label>
			    <input type="date" class="form-control" name="dob" placeholder="Enter Date of Birth">
			  </div>

			  <button type="submit" class="btn btn-primary">Submit</button>
			</form>


		</div>
	</div>
</div>
	
</body>
</html>

Store.php

$name = $_POST['name'];
$roll = $_POST['roll'];
$reg = $_POST['reg'];
$dob = $_POST['dob'];

require 'connect.php';

$sql = "INSERT INTO students
		VALUES(NULL, '$name', $roll, $reg, '$dob')";

if ( $conn->query($sql) ) {
	session_start();
	$_SESSION['msg'] = 'Student Added Successfully';
	header("Location: index.php");
} else {
	echo "Not inserted";
}

 

edit.php

<?php
	$id = $_GET['id'];

	require 'connect.php';

	$sql = "SELECT * FROM students WHERE id = $id";
	$result = $conn->query($sql);
	$student = $result->fetch_assoc();
?>

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title> Students </title>
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

</head>
<body>

<div class="container">
	<div class="row clearfix">
		<div class="col-md-3"></div>
		<div class="col-md-6">
			<h1> Update Student Information </h1>
			
			<form action="update.php?id=<?php echo $student['id']; ?> " method="post">
			  
			  <div class="form-group">
			    <label for="name">Name</label>
			    <input type="text" value="<?php echo $student['name']; ?>" class="form-control" name="name" placeholder="Enter Name">
			  </div>

			  <div class="form-group">
			    <label for="roll">Roll</label>
			    <input type="text"  value="<?php echo $student['roll']; ?>" class="form-control" name="roll" placeholder="Enter Roll">
			  </div>

			  <div class="form-group">
			    <label for="reg">Reg</label>
			    <input type="text"  value="<?php echo $student['reg']; ?>" class="form-control" name="reg" placeholder="Enter reg">
			  </div>

			  <div class="form-group">
			    <label for="dob">Date of Birth</label>
			    <input type="date" value="<?php echo $student['dob']; ?>" class="form-control" name="dob" placeholder="Enter Date of Birth">
			  </div>

			  <button type="submit" class="btn btn-primary">Submit</button>
			</form>


		</div>
	</div>
</div>
	
</body>
</html>

Update.php

$id = $_GET['id'];

$name = $_POST['name'];
$roll = $_POST['roll'];
$reg = $_POST['reg'];
$dob = $_POST['dob'];

require 'connect.php';

$sql = "UPDATE students
		SET name = '$name', roll = $roll, reg = $reg, dob = '$dob'
		WHERE id = $id ";

if ( $conn->query($sql) ) {
	session_start();
	$_SESSION['msg'] = 'Update Successfully.';
	header("Location: show.php?id=" . $id);
}

 

Show.php

<?php
$id = $_GET['id'];

require 'connect.php';

$sql = "SELECT * FROM students WHERE id = $id";
$result = $conn->query($sql);
$student = $result->fetch_assoc();
session_start();
?>


<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title> Students </title>
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

</head>
<body>

<div class="container">
	<div class="row clearfix">
		<div class="col-md-3"></div>
		<div class="col-md-6">

			<?php if( isset($_SESSION['msg']) ) { ?>
				<br>
				<div class="alert alert-success" role="alert">
				  <?php echo $_SESSION['msg']; ?>
				</div>
			<?php  session_destroy(); } ?>

			<a href="index.php" class="btn">Home</a>

			<h1> Student Information </h1>

			<a href="edit.php?id=<?php echo $student['id']; ?>" class="btn btn-primary btn-sm"> Edit </a>
			<br><br>
			<table class="table table-bordered table-sm">
				<tr>
					<th>Name</th>
					<td><?php echo $student['name']; ?></td>
				</tr>
				<tr>
					<th>Roll</th>
					<td><?php echo $student['roll']; ?></td>
				</tr>
				<tr>
					<th>Reg</th>
					<td><?php echo $student['reg']; ?></td>
				</tr>
				<tr>
					<th>Date of Birth</th>
					<td><?php echo $student['dob']; ?></td>
				</tr>
			</table>


		</div>
	</div>
</div>
	
</body>
</html>

 

delete.php

$id = $_GET['id'];

require 'connect.php';

$sql = "DELETE FROM students WHERE id = $id";


if ( $conn->query($sql) ) {
	session_start();
	$_SESSION['msg'] = 'Deleted Successfully.';
	header("Location: index.php");
}