CRUD - Create(Insert), Retrieve(View), Update(Edit) and Delete data using php and mysqli

Posted in: PHP by musiitwa joseph 2015-05-03 at 04:45:17

image for CRUD - Create(Insert), Retrieve(View), Update(Edit) and Delete data using php and mysqli In this, tutorial we are going to deal with a simple form for students that has two fields one for names and the other for marks. We shall insert into the database, retrieve data from the database into a table with different queries, edit student's name and marks or even delete. In the database two more fields are added that is id which auto increment and time_stamp to help store the time and date when marks where added.

Having created a database using phpmyadmin or command line, to do anything productive on it, you need to have inserted data into it. Six files are used: connect.php, index.php, view.php, edit.php, delete.php and styles.css

Database structure


You may run the code below a sql command or you can user php after making a connection to the database passing the statements into mysqli_query() function.

CREATE DATABASE `demos`;

CREATE TABLE IF NOT EXISTS `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`marks` int(11) NOT NULL,
`time_stamp` int(14) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
);

connect.php


Php files are connected to the database through the connect.php file.

define("DB_SERVER", "localhost");
define("DB_USER", "root");
define("DB_PASS", "");
define("DB_NAME", "demos");

$db = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

if (mysqli_connect_errno()) {
echo "Connection failed ";
die();
}

index.php


The index.php page includes the connect.php page, it is used to insert data in to the database. Any page named index loads first when a directory or folder is loaded in the browser.

The insert statement is of the form:
INSERT [INTO] table_name [(column1, column2, column3, ...)] VALUES (value1, value2, value3);
The data must be well validated before inserting into the database, some of the functions used to make safe are mysqli_real_escape_string(), trime() and strip_tags().


<html lang="en">
<head>
<title>CRUD</title>
<link type="text/css" rel="stylesheet" href="styles.css">
</head>
<body>
<div style="text-align:center"><h2>CRUD</h2></div>

<div class="container">
<a href="index.php">Add Student</a> | <a href="view.php">View Students</a>
<div class="form-section">
<?php
// including database connection on the index page
include("connect.php");

function post_DB($input){
//trim() function strips whitespace from the start and end of a string and returns the string
//strip_tags() function removes any characters that are tags like

//mysql_real_escape_string() function escapes characters like ' into '
global $db; //to access the variable within the form
return mysqli_real_escape_string($db, strip_tags(trim(@$_POST[$input])));
}

$error = array();//for holding error during validation

//if there are some variables posted to page
if($_SERVER['REQUEST_METHOD'] == "POST"){
$name = post_DB('name');
$marks = post_DB('marks');

//validation
if($name == ""){
$error[] = "Enter Name";
}
if($marks == ""){
$error[] = "Enter Marks";
}

if(count($error) == 0){//when error is in the array

//concatenate name and marks onto the query
$sql = "INSERT INTO students(id, name, marks, time_stamp) VALUES(NULL, '".$name."', '".$marks."', ".time().")";
//time() returns an integer but contain date and time

//inserting into the database
$insert = mysqli_query($db, $sql);
if($insert){
echo '<div class="success">Successfully added</div>';
}else{
echo '<div class="fail">Error !!!'.mysqli_error($db).'</div>';
}

//refresh page
echo '<meta http-equiv="refresh" content="3"/>';

}else{
echo '<div class="fail">';
foreach($error as $i){
echo $i.'
';
}
echo '</div>';
}
}
?>
<form action="" method="post">
<h3>Record students' marks</h3>
<label>Name</label>
<!-- @ is called the error suppression operator preventing the undefined variable from showing in the form fields -->
<input type="" name="name" value="<?php echo @$name; ?>"/>


<label>Marks</label>
<input type="" name="marks" value="<?php echo @$marks; ?>"/>


<input type="submit" name="submit" value="Add"/>
</form>
</div>
</div>
</body>
</html>


view.php


This is for retrieving data from the database. A select sql statement is used, retrieves data from a database by selecting rows that match specified criteria from a table. A simple select select mostly used is of the form: SELECT * FROM table_name; This uses a wild operator(*) which matches all columns from the table.

Its the view.php page that has the links for edit and delete. Any record to delete or edit must be uniquely identified, this is done by adding a primary key for each record on the url directing to delete or edit.
i.e. echo '<a href="delete.php?id='.$row['id'].'">Delete</a>';
Since deleting totally removes the record, we can make the user to first confirm that he/she really wants to delete
echo '<a href="delete.php?id='.$row['id'].'" onclick="return confirm('Do You really want to delete '.$row['name'].'')">Delete</a>';

<?php
// including database connection on the index page
include("connect.php");

// * is called the wild operator, it matches all columns from the table.
$sql = "select * from students";

//selecting or retrieving data from the database
$select = mysqli_query($db, $sql);

/*
The header part of the table is required once so it does not go into the while
and also closing tag of the table which is after the while
*/
echo '<table width="100%" border=1 cellspacing="0" cellpadding="2">';
echo '<tr>';
echo '<th>Name</th>';
echo '<th width="80px">Marks</th>';
echo '<th width="200px">Date</th>';
echo '<th width="100px">Action</th>';
echo '</tr>';

/*
Repeating part of the table. Continuously gets records from the database
according to the query
*/
while($row = mysqli_fetch_assoc($select)){
echo '<tr>';
echo '<td>'.$row['name'].'</td>';
echo '<td>'.$row['marks'].'</td>';
echo '<td>'.date('Y-m-d h:i:s A', $row['time_stamp']).'</td>';
echo '<td><a href="delete.php?id='.$row['id'].'" onclick="return confirm('Do You really want to delete '.$row['name'].'')">Delete</a> <a href="edit.php?id='.$row['id'].'">Edit</a></td>';
echo '</tr>';
}
/*
in order to preform a delete and edit, the primary key has to be added to the url which will be accessed using GET or REQUEST
*/
echo '</table>';
?>

edit.php



<?php
// including database connection on the index page
include("connect.php");
if(isset($_GET['id'])){
$id = $_GET['id'];
$sql = "select * from students where id = '$id'";
$select = mysqli_query($db, $sql);
//since one record is to be returned, there is no need of using the while loop
$row = mysqli_fetch_assoc($select);
$name = $row['name'];
$marks = $row['marks'];

function post_DB($input){
//trim() function strips whitespace from the start and end of a string and returns the string
//strip_tags() function removes any characters that are tags like

//mysql_real_escape_string() function escapes characters like ' into '
global $db; //to access the variable within the form
return mysqli_real_escape_string($db, strip_tags(trim(@$_POST[$input])));
}

$error = array();//for holding error during validation

//if there are some variables posted to page
if($_SERVER['REQUEST_METHOD'] == "POST"){
$name = post_DB('name');
$marks = post_DB('marks');

//validation
if($name == ""){
$error[] = "Enter Name";
}
if($marks == ""){
$error[] = "Enter Marks";
}elseif($marks >100){
$error[] = "Marks <= 100";
}

if(count($error) == 0){//when error is in the array


$sql = "UPDATE students SET name='$name', marks='$marks' WHERE id='$id'";

//updating/editing the database
$update = mysqli_query($db, $sql);
if($update){
if(mysqli_affected_rows($db) > 0){
echo '<div class="success">Successfully Updated</div>';
}else{
echo '<div class="fail">No Changes made</div>';
}

}else{
echo '<div class="fail">Error !!!'.mysqli_error($db).'</div>';
}

//redirect to view page
echo '<meta http-equiv="refresh" content="3;view.php"/>';

}else{
echo '<div class="fail">';
foreach($error as $i){
echo $i.'
';
}
echo '</div>';
}
}
?>

delete.php



<?php
ob_start();
include_once "connect.php";
if(isset($_GET['id'])){
$id = $_GET['id'];
$sql="DELETE FROM students WHERE id='$id'";
$delete = mysqli_query($db, $sql);
if($delete){
header("Location:view.php");
}else{
echo 'error !!!'.mysqli_error($db);
}
}else{
header("Location:index.php");
}

ob_flush();
?>

styles.css



.container{
width:100%;
max-width:700px;
margin:auto;
}
.form-section{
margin:20px auto;
border-radius:5px;
box-shadow:0 0 8px #000;
background-color:#F7F7F7;
padding:15px;
}
.form-section input{
display:block;
width:100%;
font-size:15px;
padding:5px;
}
.fail{
background-color:#F99E8E;
padding:15px;
margin:10px;
font-size:12px;
}
.success{
background-color:#B7F4A8;
padding:15px;
margin:10px;
}
Tags:
css
html
mysqli