How to insert, update, delete and view data in PHP (MySQLi Procedural) ?
In this post, we create 7 different files for CRUD Operations.
Database creation query
CREATE DATABASE blogger
Database table creation query
CREATE TABLE rangit (
id int(10) NOT NULL,
name varchar(20) NOT NULL,
email varchar(30) NOT NULL,
contact varchar(13) NOT NULL
)
Index:-
1. Connection file (connection.php)
2. Form file (form_data.php)
3. Insert data file (insert_data.php)
4. View all data file (view_data.php)
5. Delete data file (delete_data.php)
6. Update data file (update_data.php)
7. Update query file (update_query_data.php)
PHP is a very useful programming language for dynamic websites. In PHP, we can insert, update, delete and view data into a database (MySQL). In this post, we use MySQLi (i stands for improved) Procedural programming.
1. Connection file
<?php # Database Name = blogger # Database Table Name = rangit $server_name = "localhost"; $user_name = "root"; $password = ""; $database_name = "blogger"; # Connection to database $con = mysqli_connect($server_name, $user_name, $password, $database_name); if(!$con){ die("Not Connected, Error : " . mysqli_connect_error()); } ?> <html> <head> <!-- Bootstrap File --> <link rel="stylesheet" href="bootstrap/bootstrap.min.css"> <!-- Font Awesome File --> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css"> </head> </html>
Explanation
In this file, the first line
# Database Name = blogger shows the database name and
# Database Table Name = rangit shows the table name of the database.
After that, we declare four variable names,
$server_name = "localhost" because, we use xampp server,
$user_name = "root" default username,
$password = "" empty password and last is
$database_name = "blogger".
After all variable declarations, we use
mysqli_connect() to connect to the database. At last, we use downloaded bootstrap for styling the buttons and text boxes, and live font awesome CDN(Content Delivery Network) for font awesome icons.
2. Form file
<?php include "connection.php"; ?> <html> <body class="pt-5"> <div align="center"> <a href="view_data.php" class="btn btn-success"><i class="fa fa-eye"></i> View All Data</a> <h2>Form to insert data</h2> <form action="insert_data.php" method="post"> <table> <tr> <td>Name</td> <td><input type="text" name="name" class="form-control"></td> </tr> <tr> <td>Email</td> <td><input type="text" name="email" class="form-control"></td> </tr> <tr> <td>Contact</td> <td><input type="text" name="contact" class="form-control"></td> </tr> <tr> <td><input type="submit" value="Insert" class="btn btn-success"></td> <td><input type="reset" value="Clear" class="btn btn-danger"></td> </tr> </table> </form> </div> </body> </html>
Explanation
In this file, we create a simple HTML form. This file is used to insert data into the database. In this file, we include the connection.php file for connection to the database. In the HTML part, we create three text boxes and two buttons.
In form tag give action attribute to
insert_data.php file. In all the text boxes, give the
name attribute value same as database field name.
3. Insert data file
<?php>
include "connection.php";
$name = $_REQUEST['name'];
$email = $_REQUEST['email'];
$contact = $_REQUEST['contact'];
$insert_query = "INSERT INTO `rangit`(`name`, `email`, `contact`) VALUES ('". $name ."', '". $email ."', '". $contact ."')";
if(mysqli_query($con, $insert_query)){
echo '<script>
alert("Record inserted.");
window.location.href="view_data.php";
</script>';
} else {
echo "Record not inserted, Error : " . $insert_query . "<br>" . mysqli_error($con);
}
?>Explanation
In this file, first we get text box value from forms text box by using
$_REQUEST superglobal variable like,
$name = $_REQUEST['name'];
The
$insert_query variable is used to set insert query. If the query is true and the data is inserted into database than the javascript code give an alert and redirect to
view_data.php file.
We use javascript redirect code because give a alert. Instead of javascript code we can use PHP
header() function to redirect.
4. View all data file
<?php include "connection.php"; $empty_table = ""; ?> <html> <body> <div align="center" class="table table-border pt-5"> <!-- View all data in table --> <a href="form_data.php" class="btn btn-success"><i class="fa fa-plus-square"></i> Add new data</a> <h2>View all data in table</h2> <table> <tr> <th>Id</th> <th>Name</th> <th>Email</th> <th>Contact</th> <th class="text-center">Action</th> </tr> <?php $select_query = "SELECT * FROM `rangit`"; $view_data = mysqli_query($con, $select_query); if(mysqli_num_rows($view_data) > 0){ while($row = mysqli_fetch_assoc($view_data)){ ?> <tr> <td><?php echo $row['id']; ?></td> <td><?php echo $row['name']; ?></td> <td><?php echo $row['email']; ?></td> <td><?php echo $row['contact']; ?></td> <td> <a href="update_data.php?id=<?php echo $row['id']; ?>" class="btn btn-primary"><i class="fa fa-pencil-square"></i> Edit</a> <a href="delete_data.php?id=<?php echo $row['id']; ?>" class="btn btn-danger"><i class="fa fa-minus-square"></i> Delete</a> </td> </tr> <?php } } else { $empty_table = "No data."; } ?> <tr> <td colspan=5 align="center"><?php echo $empty_table; ?></td> </tr> </table> </div> </body> </html>
Explanation
This file shows all inserted data with an update and delete button. We declare an empty variable $empty_table to show the text(No data.) when the table is empty.
In HTML, we create a table that shows all the data in tabular format. In the table, we use the SELECT query to select all data. The mysqli_num_rows() checks whether the table is empty or not.
In while() loop use the mysqli_fetch_assoc() function to fetch all records and echo all the values into the table. If the user clicks on the Edit button the browser redirects to the update_data.php file and if the user clicks on the Delete button the browser redirects to the delete_data.php file.
5. Delete data file
<?php include "connection.php"; $id = $_REQUEST['id']; $delete_query = "DELETE FROM rangit WHERE id='$id'"; if(mysqli_query($con, $delete_query)){ echo '<script> alert("Record deleted."); window.location.href="view_data.php"; </script>'; } else { echo "Record not deleted, Error : " . mysqli_error($con); } ?>
Explanation
In this file first, we get the id. We use the DELETE query with the WHERE clause to delete records. Use mysqli_query() function to fire the DELETE query. If the DELETE query is not fired then the error message is displayed in the mysqli_error() function.
6. Update data file
<?php include "connection.php"; $id = $_REQUEST['id']; $select_id_query = "SELECT * FROM `rangit` WHERE id = $id"; $row = mysqli_query($con, $select_id_query); $record = mysqli_fetch_assoc($row); ?> <html> <body class="pt-5"> <div align="center"> <h2>Update Data</h2> <form action="update_query_data.php?id=<?php echo $record['id']; ?>" method="post"> <table> <tr> <td>Name</td> <td><input type="text" name="name" value="<?php echo $record['name']; ?>" class="form-control"></td> </tr> <tr> <td>Email</td> <td><input type="text" name="email" value="<?php echo $record['email']; ?>" class="form-control"></td> </tr> <tr> <td>Contact</td> <td><input type="text" name="contact" value="<?php echo $record['contact']; ?>" class="form-control"></td> </tr> <tr> <td><input type="submit" value="Edit" class="btn btn-primary"></td> <td><a href="view_data.php" class="btn btn-danger">Cancel</a></td> </tr> </table> </form> </div> </body> </html>
Explanation
In this file first, we fire the SELECT query with the WHERE clause. We use mysqli_fetch_assoc() function.
In the HTML part, we use the value attribute to all text boxes and set echo $record['name']; When the user clicks on Submit button the value is updated.
7. Update query file
<?php include "connection.php"; $id = $_REQUEST['id']; $name = $_REQUEST['name']; $email = $_REQUEST['email']; $contact = $_REQUEST['contact']; $update_query = "UPDATE `rangit` SET `name`='$name', `email`='$email', `contact`='$contact' WHERE `id`='$id'"; if(mysqli_query($con, $update_query)){ echo '<script> alert("Record updated."); window.location.href="view_data.php"; </script>'; } else { echo "Record not updated, Error : " . mysqli_error($con); } ?>
Explanation
In this file first, we get all text box values and fire the UPDATE query using the mysqli_query() function. If the error has occurred then the mysqli_error() function shows that error.
Final Output
The above image shows a button (Add new data) at the top, that is used to add new data and redirects to the form_data.php file to insert new data into the table.
In the above image, If the user clicks on the Edit button the button redirects to the form_data.php file with all data of the text box and if the user clicks on the Delete button the data is deleted from the database and also from the table.
Conclusion
In final words, these PHP CRUD operations are done by using MySQLi Procedural functions. You can also be done PHP CRUD operations by using PHP Object-Oriented Programming.
Tags:-
Published On:-Blogger Rangit
Written By:-Rangit Ramani
Comments
Post a Comment