Welcome readers! We are back with another interesting article. We will discuss how to insert data in the database which is the first part of PHP CRUD. In CRUD, C stands for "create" (Insert Operation). This article's main focus is to explain Create or Insert part of the CRUD in detail. First of all, we will create a new "book" table in our library database. Then, we will create an HTML form to take input from the user. We will also apply server-side validation on the form. Finally, we will submit the form to a pure PHP page to execute the insert query and perform the insert operation.
Creating a "book" table in the database:
- Open "localhost/phpmyadmin" in any browser to create your table.
- Create a new table "book" in our library database that we created in our database connectivity article.
- Click on "new" under the database name "library".
- Name the table as "book" and add "5" columns.
- Name the first column as "id" and set its length to 20. Also, check the auto-increment checkbox. This column will act as the primary key of the table.
- Name the second column as "user_id" and set its length to 20. It will be the foreign key of the user table. We need the foreign key to keep the record of the user uploading the book.
- Name the rest of the columns as title, author, description, and date. Set their lengths as 200, 200, 1500, and 200.
- Click on the Save button and your "book" table is created now. But it does not have any data yet.
Note: All the column names and the lengths of the columns are adjusted according to our project requirements.
Now let's create the HTML form to take input from the user.
Creating HTML form:
- Create an "addbook.php" file in your project. This file will mainly contain our HTML code.
- Create one more file "addbook.inc.php" inside the includes folder. I prefer to keep all pure PHP files in a separate folder and I usually name the folder as "includes".
- At the very start, write PHP code to avoid unauthorized access to the page. The user must be logged in to access this page.
<?php session_start(); if(!isset($_SESSION['id']) && !isset($_SESSION['email']) ){ header("location: login.php"); } ?>
- Include bootstrap 5 into your code to style the HTML form easily.
- Create bootstrap navigation to show the log-out button and name to the user
- create a bootstrap grid with a single row and a single column.
- In the column, create your form to take input from the user.
- Create two text fields for the book title and author name. Also, create one text area for the description of the book.
- Create a submit button to submit the form to the "addbook.inc.php" file.
- Also, set the method attribute to POST and set the action of the form to "includes/addbook.inc.php".
- Add some style to apply blue and white gradient color to the navbar and HTML form container.
<style type="text/css"> .navbar{ background: -webkit-linear-gradient(to right, #5bc0de, #fff); background: linear-gradient(to right, #5bc0de, #fff); } .col-6{ margin-top: 5%; background: -webkit-linear-gradient(to right, #5bc0de, #fff); background: linear-gradient(to right, #5bc0de, #fff); } </style>
Now see the complete working code of all the above-stated steps at once.
<?php session_start(); if(!isset($_SESSION['id']) && !isset($_SESSION['email']) ){ header("location: login.php"); } ?> <!doctype html> <html lang="en"> <head> <!-- Required meta tags --> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- Bootstrap CSS --> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous"> <title>Add Book</title> <style type="text/css"> .navbar{ background: -webkit-linear-gradient(to right, #5bc0de, #fff); background: linear-gradient(to right, #5bc0de, #fff); } .col-6{ margin-top: 5%; background: -webkit-linear-gradient(to right, #5bc0de, #fff); background: linear-gradient(to right, #5bc0de, #fff); } </style> </head> <body> <!--Starts Here NavBar--> <nav class="navbar navbar-expand-lg navbar-light"> <div class="container-fluid"> <a class="navbar-brand" href="#"><h3>My Library</h3></a> <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNavAltMarkup" aria-controls="navbarNavAltMarkup" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="collapse navbar-collapse" id="navbarNavAltMarkup"> <div class="navbar-nav ms-auto mb-2 mb-lg-0 "> <a class="nav-link" href="#"><?php echo "Hello! ".$_SESSION['name'] ?></a> <a class="nav-link" href="includes/logout.inc.php">Log out</a> </div> </div> </div> </nav> <!--Addbook form container--> <div class="container-fluid "> <div class="row justify-content-center"> <div class="col-6 text-center mb-5 py-5 px-5"> <h1 class="text-center mb-5 text-dark">Add books and Spread Knowledge</h1> <form method="POST" action="includes/addbook.inc.php"> <div class="form-group my-3"> <input type="text" class="form-control" placeholder="Enter Title" name="title"> </div> <div class="form-group my-3"> <input type="text" class="form-control" placeholder="Enter Author" name="Author"> </div> <div class="form-group my-3"> <textarea class="form-control" rows="5" name="desc" placeholder="Enter Description" style="resize: none;"></textarea> </div> <button type="submit" name="submit" class="btn my-3 fs-5 px-4" style="background:#068a9c">Submit</button> <a href="#" class="btn btn-danger text-dark my-3 fs-5 px-4">Go Back</a> </form> <!--Real Errors will be printed in the last paart of article--> <p class='alert alert-danger'>
<!--temporary alert --> Warning! Description is too short.Min 20 words required.
</p>
</div>
</div>
</div>
<!--Starts Here Footer-->
<footer class=" text-center text-dark border-top">
<!-- Copyright -->
<div class="text-center p-3">
© 2020 Copyright:
<a href="www.Programmopedia.com">programmopedia.com</a>
</div>
<!-- Copyright -->
</footer>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
</body>
</html>Now see the result of the "addbook.php" file.
Applying server-side validation on the form:
- Open the "addbook.inc.php" file to write PHP code. Read our server-side validation article to learn it in detail.
- Using the if statement and PHP isset() function, check whether the form is submitted properly.
- Save the data retrieved in the $_POST associative array into PHP variables.
- Apply validation on the form data to check if there is an empty field using the PHP empty() function. Use PHP header() function to generate error through $_GET variable if any of the fields are empty.
- Apply another validation on the description field. Don't allow the user to submit the form if the description is less than 20 words. Use PHP str_word_count() function to apply this validation.
Now we are done with the server-side validation. A working code example is given below.
<?php session_start(); if(isset($_POST['submit'])){ $title = mysqli_real_escape_string($conn, $_POST["title"]); $Author = mysqli_real_escape_string($conn, $_POST["Author"]); $desc = mysqli_real_escape_string($conn, $_POST["desc"]); $date = mysqli_real_escape_string($conn, date("l jS \of F Y h:i:s A")); $user_id = $_SESSION["id"]; //Server side validation// if(empty($title) && empty($Author) && empty($desc) ){ header("location: ../addbook.php?error=AllInputsEmpty"); exit(); } if(empty($title)){ header("location: ../addbook.php?error=EmptyTitle"); exit(); } if(empty($Author)){ header("location: ../addbook.php?error=EmptyAuthor"); exit(); } if(empty($desc)){ header("location: ../addbook.php?error=EmptyDesc"); exit(); } if(str_word_count($desc)<20){ header("location: ../addbook.php?error=InvalidDesc"); exit(); } ?>
Inserting data into books table:
- Include the database connectivity file "db.inc.php". Read our database connectivity article to learn about this file.
- Write SQL insert query to insert data into database table.
- Execute the query using PHP mysqli_query() function which takes $conn(connection variable) and $query (SQL query) as parameters.
- Check if the query is successfully executed using the if statement.
- If not executed, use the die() function to print an error message. You can also use the myslqi_error() function to print the description of the error.
- If the query is successfully executed, use the header() function to redirect the user to the "addbook.php" page.
<?phprequire "db.inc.php";
$query ="INSERT INTO `book`(`user_id`, `title`, `Author`,`description`,`date`) VALUES ($user_id,'$title','$Author','$desc','$date')"; if(mysqli_query($conn, $query)){ header("location: ../addbook.php?error=success"); }else{ die("error while executing the query". mysqli_error($conn)); } }else{ die("error while submiting the form"); } mysqli_close($conn); ?>
We have successfully inserted data into the "book" table. Let's see the complete working code below.
<?php require "db.inc.php"; session_start(); if(isset($_POST['submit'])){ $title = mysqli_real_escape_string($conn, $_POST["title"]); $Author = mysqli_real_escape_string($conn, $_POST["Author"]); $desc = mysqli_real_escape_string($conn, $_POST["desc"]); $date = mysqli_real_escape_string($conn, date("l jS \of F Y h:i:s A")); $user_id = $_SESSION["id"]; //Server side validation// if(empty($title) && empty($Author) && empty($desc) ){ header("location: ../addbook.php?error=AllInputsEmpty"); exit(); } if(empty($title)){ header("location: ../addbook.php?error=EmptyTitle"); exit(); } if(empty($Author)){ header("location: ../addbook.php?error=EmptyAuthor"); exit(); } if(empty($desc)){ header("location: ../addbook.php?error=EmptyDesc"); exit(); } if(str_word_count($desc)<20){ header("location: ../addbook.php?error=InvalidDesc"); exit(); } //Server side validation ends here //executing query to insert data $query ="INSERT INTO `book`(`user_id`, `title`, `Author`,`description`,`date`) VALUES ($user_id,'$title','$Author','$desc','$date')"; if(mysqli_query($conn, $query)){ header("location: ../addbook.php?error=success"); }else{ die("error while executing the query". mysqli_error($conn)); } }else{ die("error while submiting the form"); } mysqli_close($conn); ?>
Now Let's see the result of the "addbook.inc.php" file in the below-shared screenshot.
Here is the result. The data is inserted into the database successfully. Now let's print proper alerts on "addbook.php".
Printing alerts to users:
- Open the "addbook.php" file again to show proper alert messages to users.
- Remove the temporary alert message.
- Write PHP script under the form tag to display alerts to users.
- Check if the $_GET["error"] variable is set using if statement and PHP isset function.
- Now check for all the errors and print proper alert messages using nested if statement.
Check out the PHP code to print alerts messages to users.
<?php if(isset($_GET["error"])){ if($_GET["error"]=="AllInputsEmpty"){ echo " <p class='alert alert-danger'> Warning! All input Fields are Empty. </p> "; } if($_GET["error"]=="EmptyTitle"){ echo " <p class='alert alert-danger'> Warning! Title can not be empty. </p> "; } if($_GET["error"]=="EmptyAuthor"){ echo " <p class='alert alert-danger'> Warning! Author name can not be empty. </p> "; } if($_GET["error"]=="EmptyDesc"){ echo " <p class='alert alert-danger'> Warning! Description can not be empty. </p> "; } if($_GET["error"]=="InvalidDesc"){ echo " <p class='alert alert-danger'> Warning! Description is too short.Min 20 words required. </p> "; }
} ?>
Finally, we have successfully inserted data into the database, applied proper validations, and also printed proper alert messages to users. Now let's end the article here and see you in the next one with more useful information. Thanks for reading and supporting us.
0 Comments