CRUD Operation in MySQL Using PHP - Insert Data

CRUD Operation in MySQL Using PHP - Insert Data

In this article, you will mainly learn about crud operation which will be done with the help of PHP. But this article will mainly tell you only about creating or saving data.

PHP Crud Operation

Learning CRUD operation is important in web development because every web application has these principles and basic functions. CRUD means:
  • Create
  • Read
  • Update
  • Delete
If you want to learn web independent, you must implement and apply CRUD operation. Because in any website or app data has to be stored, updated, deleted, and defaulted. For example:

Create: You are projecting new data (such as registering a new laptop).
Read: You view static data (such as viewing a landlord's profile).
Update: You are sharing some data (such as updating a landlord's address).
Delete: Some of your data (such as who created the account) gets deleted.

If you know CRUD operation, then you can get any simple or complex web delivery because every delivery has these shops. Learning CRUD operation helps you to work with dynamic websites and resumes.

So, this is a base that will help you to move forward in your website journey.

And let's see the step of saving (creating) data using PHP.

Step of saving (creating) data
  1. Create form:- To save data, first of all, a form is required in which the user enters data or the entered data is saved in the database main.
  2. Create database:- After this, we have to create a database in which we create a table or add some columns to the table. Suppose a form has "first name", "last name", "email", and "profile photo". So just like there are four fields in the form, similarly, we create columns in the table with the names "firstname", "lastname", "email", "profile_photo".
  3. Create connection:- In this step, a connection to the database is created using PHP code.
  4. Write code:- After creating the connection, code is written to save the data.
Get Source Code

1. Form Code

                   
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Rays Coding :- PHP Crud Operation</title>
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-GLhlTQ8iRABdZLl6O3oVMWSktQOp6b7In1Zl3/Jr59b6EGGoI1aFkw7cmDA6j6gD" crossorigin="anonymous">
        <link rel="stylesheet" href="../css/style.css">
    </head>
    <body>
        <div class="container mt-5">
            <div class="row">
                <div class="col-12">
                    <div class="card">
                        <div class="card-header bg-dark">
                            <h4 class="text-center text-light">Rays Coding : PHP CRUD Operation</h4>
                        </div>
                        <div class="card-body">
                            <form action="../insert/insert.php" method="post" enctype="multipart/form-data">
                                <div class="row">
                                    <div class="col-md-6">
                                        <div class="mb-3">
                                            <label for="username" class="form-label">Name</label>
                                            <input type="text" class="form-control" id="username" name="user_name" placeholder="Enter User Name" autocomplete="off">
                                        </div>
                                    </div>
                                    <div class="col-md-6">
                                        <div class="mb-3">
                                            <label for="useremail" class="form-label">Email</label>
                                            <input type="email" class="form-control" id="useremail" name="user_email" placeholder="Enter Email Address" autocomplete="off">
                                        </div>
                                    </div>
                                </div>
                                <div class="row">
                                    <div class="col-md-6">
                                        <div class="mb-3">
                                            <label for="usercontact" class="form-label">Mobile Number</label>
                                            <input type="text" class="form-control" id="usercontact" name="user_contact" placeholder="Enter Contact Number" autocomplete="off">
                                        </div>
                                    </div>
                                    <div class="col-md-6">
                                        <div class="mb-3">
                                            <label for="userprofile" class="form-label">Profile</label>
                                            <input type="file" class="form-control" id="userprofile" name="user_profile" autocomplete="off">
                                        </div>
                                    </div>
                                </div>
                                <div class="row">
                                    <div class="col-md-6">
                                        <div class="mb-3">
                                            <label for="userpassword" class="form-label">Create Password</label>
                                            <input type="password" class="form-control" id="userpassword" name="user_password" placeholder="Create Password" autocomplete="off">
                                        </div>
                                    </div>
                                    <div class="col-md-6">
                                        <div class="mb-3">
                                            <label for="userpassword" class="form-label">Confirm Password</label>
                                            <input type="password" class="form-control" id="userpassword" name="confirm_user_password" placeholder="Re-Enter Password" autocomplete="off">
                                        </div>
                                    </div>
                                </div>
                                <div class="row">
                                    <div class="col-md-6"></div>
                                    <div class="col-md-6">
                                        <button type="submit" name="submit_btn" class="btn bg-dark text-light submit_button"><b>Submit</b></button>
                                    </div>
                                </div>
                            </form>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </body>
    </html>
                           

This is the code of the HTML form in which data has to be input for PHP CRUD operation. In this form, you will find fields for user name, email, mobile number, profile photo, password, and confirm password. The action of the form is ../insert/insert.php, which will submit the data and send it to the backend PHP script for processing.

PHP Crud Operation

There are a total of 5 fields in the form:
  1. Name: This is a text field where the user will enter his name.
  2. Email: Email field where the user can enter his email id.
  3. Mobile number: This is also a text field where the user will enter his contact number.
  4. Profile: This is a file upload field in which the user can upload his profile picture.
  5. Password and Confirm Password: These are both password fields, one for the user to create his password and the other to confirm the password.

2. Create database

               
  CREATE TABLE `simple_table` (
    `id` int(5) NOT NULL,
    `name` varchar(100) DEFAULT NULL,
    `email` varchar(200) DEFAULT NULL,
    `contact` varchar(100) DEFAULT NULL,
    `profile` varchar(255) DEFAULT NULL,
    `password` varchar(100) DEFAULT NULL,
    `confirm_password` varchar(100) DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NOT NULL DEFAULT current_timestamp()
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
               

This code creates a table in which basic user details will be stored. Its name is simple_table. The table has some columns:
  1. id: This will be a unique number for every user, which will be automatically incremented.
  2. name, email, contact, profile, password, Confirm_password: These are all text-type fields, in which the user's name, email, contact number, profile details, and password will be stored.
  3. created_at and updated_at: These two fields will automatically record when the data is inserted or updated.
PHP Crud Operation

Its engine is InnoDB, which stores data in a secure way and CHARSET is UTF8mb4, which can handle any language or special characters (like emojis).
This is just a simple table, in which you can store basic information related to the user!

3. Create a connection with the database

                       
    <?php
        $host = 'localhost';
        $username = 'root';
        $password = '';
        $database = 'crud_operation';
        $conn   = mysqli_connect($host, $username, $password);
        mysqli_select_db($conn,$database );
    ?>
                     

This code is in PHP and its main purpose is to connect to the MySQL database. Now let's understand it in simple language.
  1. Connection Setup: First of all, some variables are defined in the code which contains the information required to connect to the database. These variables are:
    1. $host = 'localhost';: It indicates that our database is on the local machine, i.e. the server is on our own computer.
    2. $username = 'root';: It is the name of the user who will access the database. 'root' is the default user.
    3. $password = '';: It is the password given for the 'root' user. No password is given in this case.
    4. $database = 'crud_operation';: It is the name of the database in which we will be working.
  2. Database Connection: After that, the mysqli_connect() function is used in which the above parameters are passed. Meaning, this function tries to connect your PHP script to the MySQL server. If the connection is successful then you get a valid connection which is stored in the $conn variable.
  3. Database Selection: Then the mysqli_select_db() function is used which specifies which database to use. This function passes the $conn variable (which represents the connection) and then selects the database 'crud_operation' in which you can perform CRUD (Create, Read, Update, Delete) operations.
In this code, a connection to the database is created in a basic way in which you can execute further queries.

mysqli_connect() Function:

You use the mysqli_connect() function to connect your PHP script to a MySQL database. That is, if you want to store data on your website or application, you must connect to the database. This function connects your PHP code to the MySQL server.

How it works:

You have to give this function some important information, such as:
  1. host (where the database is running, usually localhost if your database is on your own computer),
  2. username (the user who will access the database, such as root),
  3. password (the user's password, if any),
  4. database name (the one you want to use).
If this connection completes successfully, you get a connection object that you can use to run further queries.

mysqli_select_db:

mysqli_select_db() is a PHP function used to select a MySQL database. When you connect to a MySQL server, you first need to select a specific database in which you can run queries. This function takes two parameters: first, the connection resource returned from mysqli_connect(), and second, the name of the database you want to select. If the database is successfully selected, the function returns true; if an error occurs, it returns false.

4. Write code to save the data

                       
    <?php
        require_once('../connection/conn.php');
        if(isset($_POST['submit_btn']))
        {
            $name = $_POST['user_name'];
            $email= $_POST['user_email'];
            $contact = $_POST['user_contact'];
            $profile = $_FILES['user_profile']['name'];
            $profile_temp = $_FILES['user_profile']['tmp_name'];
            $profile_location = '../profiles/'.$profile;
            $password = $_POST['user_password'];
            $confirm_password = $_POST['confirm_user_password'];
            if($password == $confirm_password)
            {
                $insert_data = "INSERT INTO `simple_table`(`name`, `email`, `contact`, `profile`, `password`, `confirm_password`) VALUES ('$name','$email','$contact','$profile','$password','$confirm_password')";
                move_uploaded_file($profile_temp, $profile_location);
                $insert_query = mysqli_query($conn,$insert_data);
               
                if($insert_query)
                {
                    header('location:../index/index.php');
                }
                else
                {
                    echo "<script>alert('Data Not Insert.'); window.history.back();</script>";
                }
            }
            else
            {
                echo "<script>alert('Password Are Not Match'); window.history.back();</script>";
            }
        }
    ?>
                           

This PHP code handles a form submission process in which the user's name, email, contact number, profile image, and password are stored. First, the code includes the conn.php file that contains the database connection settings. When the user submits the form (via the submit_button), the data from the form is retrieved via $_POST and $_FILES.

If the user's password and the confirmed password match, the code creates a SQL query that inserts the form data into a database table called simple_table. Next, the profile image is uploaded to the specified location (../profiles/). If the data is inserted, the user is redirected to the homepage (index.php). If the insert operation fails, a warning message is displayed.

If the passwords do not match, a warning message saying "Passwords do not match" is displayed and the user is redirected back to the previous page.

move_uploaded_file() PHP function

move_uploaded_file is a PHP function that moves an uploaded file to a new location. When a user uploads a file, this function is used to move that file from a temporary location to a specific folder or directory. Its syntax is something like this:

                               
    move_uploaded_file($profile_temp, $profile_location);
                 

Here $temporary_file is the file that was uploaded to the server, and $destination is the location where you want to move the file.

mysqli_query() PHP function

mysqli_query() is a function used in PHP to fetch data from MySQL database or execute SQL queries. It is used when you need to run a query (such as SELECT, INSERT, UPDATE, DELETE) in the database. This function runs the SQL query and returns the result.

If you run a SELECT query, this function will return the result that you need to use for fetching. If you run an INSERT, UPDATE, or DELETE query, this function returns the number of affected rows, i.e. how many rows have been updated or deleted.

Post a Comment

Previous Post Next Post

Recent in Technology