Simple CRUD application in core PHP

PHP is a server-side language, widely used in web applications. If you are thinking about giving it a try, you can start with a simple CRUD application in core PHP. CRUD (Create, Update, Read, Delete) covers almost all the operations you might need while developing a real-world website/project.

So in this article, we will create a form, and on form submit we will add form data in Database. And then we will display those data on a page with edit and delete button.

Setup

Before we hit the code part lets see what are prerequisites, so you need an apache server and MySQL server or XAMPP which have both. So I am assuming you have installed XAMPP on your machine if not you can follow these articles to install – WindowsUbuntumacOS. and start your XAMPP server.

Now let’s create a folder inside the htdocs directory named CorePHPCRUD. You can go with any name you want. In that folder, we will be creating subsequent PHP files.

Database setup

Open PHPMyAdmin by visiting http://localhost/phpmyadmin.

Create a database with the name ‘CorePHPCRUD_DB‘. Next, add the following query to create a Table with columns in the CorePHPCRUD_DB database.

Once you created a database, in PHPMyAdmin click on SQL Tab and paste the following query to create a table with some columns. Or you can create a table using PHPMyAdmin GUI.

CREATE TABLE users (
    id int NOT NULL AUTO_INCREMENT,
    username varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

Code time

So, We have done the setup so far. Let’s create a connect our project with the created database. So to do that create a file named connection.php in the folder we just created and add the following code.

$server = 'localhost';
$username = 'root';
$password = '';
$dbName = 'CorePHPCRUD';

$conn = mysqli_connect($server,$username,$password,$dbName);
if($conn){
echo "connection suucessful";
} else{
    echo "error";
}

So to build a connection between database we need server name, server username, server password, and database name. As we are using local server XAMPP. So you can see what are our server details I have assigned them in associated variables. To make a connection we have used mysqli_connect() function. which accepts all four parameters to connect with the database. I have kept the result of connection in $conn variable and then checking if the connection is true then print connection successful.

To check that open your browser and type the following URL and you will see if your connection with the database is successful or not.

http://localhost/CorePHPCRUD/connection.php

So once connected successfully, comment( // ) the success message because we need only $conn variable for all further operations.

create.php ( C of CRUD)

Create a file with the name of create.php. In this file we will create form with username and password. Add the following code in it.

<!DOCTYPE html>
<html>
<body>

  <h2>HTML Forms</h2>

  <form action="submit.php" method="post">
    <label for="username">username:</label><br>
    <input type="text" id="username" name="username"><br>
    <label for="password">Password:</label><br>
    <input type="text" id="password" name="password"><br><br>
    <input type="submit" value="Submit">
  </form>
</body>

</html>

Notice form tag, It is required to use action and method attributes.

action : after click on submit where form data should go.

method : In which format data should go GET or POST .

submit.php

Now in create.php form has submit.php in action. So create a file submit.php and add the following code.

<?php
include 'connection.php';

 $username = $_REQUEST['username'];
 $password = $_REQUEST['password'];
 $sql = "INSERT INTO `users` (`username`,`password`) VALUES
  ('$username','$password')";
    $result = mysqli_query($conn,$sql);
    if($result){
        echo "record added";
    }else{
        echo  "something wrong";
    }

Here you might have noticed we are using connection.php. Yes, we need $conn variable every time we perform a query to the database because $conn holds database connection instance.

Next, we have used $_REQUEST to access our form data. Then in $sql variable holds our insert query. Next, we are using mysli_query($conn,$sql), It accepts a two-parameter connection instance and SQL query then it executes with those parameters. If everything is good you will pass the if condition and data will be added to the database.

read.php ( R of CRUD)

Now then add a few data into the database using the form. So once we have data in the database table then we are ready to display them in HTML table format. So create a file read.php and add the following code.

<?php
include 'connection.php';
$sql = "SELECT * FROM `users` ";
$result = mysqli_query($conn, $sql);
if ($result) {
    $data = mysqli_fetch_all($result, MYSQLI_ASSOC);
?>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Password</th>
            <th>Action</th>
        </tr>
        <?php
        $count = count($data);
        for ($i = 0; $i < $count; $i++) { ?>
            <tr>
                <td><?php echo $data[$i]['id']; ?> </td>
                <td><?php echo $data[$i]['username']; ?> </td>
                <td><?php echo $data[$i]['password']; ?> </td>
                <td>
                    <a href="edit.php?id=<?php echo $data[$i]['id']; ?>">
                        Edit
                    </a>
                    <a href="delete.php?id=<?php echo $data[$i]['id']; ?>">
                        Delete
                    </a>
                </td>
            </tr>
        <?php
        }
        ?>

    </table>
<?php } ?>

So you see there in $data variable we are fetching all data from the users table. Then we create a table and we are putting tr and td in a for loop, this loop will continue until all rows from our table are finished.

In action column we are passing two anchor links with href edit.php?id=someid and delete.php?id=someid.

edit.php

We saw this link in read.php in each row, so whenever that link gets clicked it will come on edit.php with someId. Using this ID we will retrieve the matching record from the table. Create edit.php file and add the following code.

<?php include 'connection.php';
$id = $_REQUEST['id'];
$sql = "SELECT * FROM `users` WHERE id='$id'";
$result = mysqli_query($conn, $sql);
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
?>
<!DOCTYPE html>
<html>

<body>

    <h2>Edit</h2>
    <form action="update.php" method="post">

        <input type="hidden" name="id" value="<?php echo $data['id'] ?>" />

        <label for="username">username:</label><br>
        <input type="text" id="username" name="username" value="<?php echo $data['username'] ?>"><br>
        <label for="password">Password:</label><br>
        <input type="text" id="password" name="password" value="<?php echo $data['password'] ?>"><br><br>
        <input type="submit" value="Submit">
    </form>
</body>

</html>

So we are putting someID using $_REQUEST in $id variable. and using that $id we are fetching a particular record from the users table. Fetched data from table users we are keeping in $data variable. Then we have the same as create.php but this time you are seeing value with some data in prefilled. One other thing is noticeable is we have added one more input below form tag which hidden and having id as a value. So this Id will be passed to update.php with other details.

update.php (U of CRUD)

On submitting of edit form data we receiving all data on update.php. Create update.php and add the following code.

<?php
include 'connection.php';
$id = $_REQUEST['id'];
$username = $_REQUEST['username'];
$password = $_REQUEST['password'];
$sql = "UPDATE `users` SET `username`='$username',`password`='$password' 
         WHERE id='$id'";
$result = mysqli_query($conn, $sql);
if ($result) {
    echo "record updated";
} else {
    echo  "something wrong";
}

This one is pretty straightforward we are updating username and password where id is equal to the passed hidden id in edit.php.

delete.php (D of CRUD)

So we are at last part of operations. In our read.php we have delete.php with someid link. On click of that link we will recive that id on this page delete.php. Lets create delete.php and add the following code.

<?php
include 'connection.php';
$id = $_REQUEST['id'];
$sql = "DELETE FROM `users`WHERE id='$id'";
$result = mysqli_query($conn, $sql);
if ($result) {
    echo "record deleted";
} else {
    echo  "something wrong";
}

So this one doesn’t need an explanation I guess we are just performing a delete query based on the id.

So that’s how you can create simple CRUD application in core PHP. I have added quite a lot of explanations for better understanding. If you still have some doubts, let me know in the comment box.

I am adding Github repository of this CRUD application , if you are stuck somewhere take a look at proper code with files here. Let me know what you think about the CRUD application in core PHP.

See also

Leave a Reply

Your email address will not be published. Required fields are marked *