Next and previous links in PHP, MySQL

If you see a WordPress site, you will notice that at the bottom of the blog listing page, there will be links to next and previous pages.

They are useful to move the user to next or previous page.

If user is at the last page, then the next button will not be displayed.

Similarly, if user is at the first page, then the previous button will be hidden.

We are achieve this in PHP and MySQL. Following is the code to do that:

<?php

// Create a new PDO connection to the MySQL database
$pdo = new PDO("mysql:host=localhost; dbname=test", "root", "root", [
    PDO::ATTR_PERSISTENT => true // Enables persistent connection for better performance
]);

// Define the number of records to show per page
$per_page = 2;

// Get the current page number from the query string, default to 1 if not set
$page = $_GET["page"] ?? 1;

// Calculate the offset for the SQL query
$skip = ($page - 1) * $per_page;

// Fetch the users for the current page, ordered by ID in descending order
$sql = "SELECT * FROM users ORDER BY id DESC LIMIT $skip, $per_page";
$stmt = $pdo->prepare($sql); // Prepare the SQL statement
$stmt->execute([]); // Execute the query
$users = $stmt->fetchAll(PDO::FETCH_OBJ); // Fetch all results as objects

// Query to get the total number of users in the database
$sql = "SELECT COUNT(*) AS total_count FROM users";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_OBJ);
$total_count = $row->total_count ?? 0; // Get the total count or default to 0

// Calculate the total number of pages required
$total_pages = ceil($total_count / $per_page);

// Determine if there are more pages
$has_more_pages = ($page * $per_page) < $total_count;

// Initialize previous and next page variables
$previous_page = $next_page = null;

// Determine the previous page number
if ($page > 1)
{
    $previous_page = $page - 1;
}

// Determine the next page number
if ($page < $total_pages)
{
    $next_page = $page + 1;
}

?>

<!-- Loop through and display each user -->
<?php foreach ($users as $user): ?>
    <p><?php echo $user->name; ?></p>
<?php endforeach; ?>

<!-- Pagination controls -->
<?php if ($next_page > 0): ?>
    <a href="?page=<?php echo $next_page; ?>">Previous</a>
<?php endif; ?>

<?php if ($previous_page > 0): ?>
    <a href="?page=<?php echo $previous_page; ?>">Next</a>
<?php endif; ?>

Comments has been added with each line for explanation.

Even if you are working in any other language like Node.js and MongoDB, you can still achieve this because now you have the algorithm to do that.

We implemented this technique in one of our project: Multi-purpose platform in Node.js and MongoDB

From there, you will get the idea how you can render next and previous links if you are working in Node.js and MongoDB as backend and React as frontend.

Free tool to write API documentation – PHP, MySQL

I was looking for a tool that allows me to write API documentation so I can provide it to the frontend developers and also for myself. I checked many but didn’t find any that fits to all of my requirements. So I decided to create one of my own. Creating my own tool gives me flexibility to modify it as much as I want.

I also wanted it to share it with other developers who might be having problem in finding the tool to write documentation for their API. So I uploaded it on Github and it is available for anyone for free.

How to write API documentation

A tool is created in PHP and MySQL that allows developers to write API documentation, and this tool is available for free. You can create multiple sections to group the APIs based on modules. For example, user authentication, user posts, comments, replies can be separate sections.

To write each API, you need to tell:

  • The section where it goes.
  • The name of the endpoint. It can be the URL of API.
  • The method, it can be either GET or POST. But since you will have the code, you can add more methods as per your needs.
  • Add a little description about the API, for example what this API does.
  • Headers:
    • You need to tell the key of header, whether it is required or optional. And a little description about the header, for example, it’s possible values.
  • Parameters:
    • Parameters are usually send in the URL. You can define them along with their key, and value and whether they are optional or not.
  • Arguments:
    • For defining the arguments, you need to specify it’s type too. Whether it can be an integer, a string, boolean value or a file object.
  • Example request body. You can write the CURL code inside it to give an example.
  • Status codes and their responses.

I wrote a complete documentation of a project using this tool. You can check that from here.

Download

Assign shirt number to new cricketer – PHP PDO MySQL

Suppose you have a database where data of all players of any sports team is stored. Every player has a name and a shirt number. We will create a program that will generate a new number for new player. We will make sure the new number is not already assigned to any player. We will keep generating new numbers until a unique number is found.

First, create a table “players” in your database by running the following SQL query.

CREATE TABLE IF NOT EXISTS players2 (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    shirt_number INTEGER(11) NOT NULL
);

Then, create a file named “assign-shirt-number-to-new-player.php” and write the following code in it:

// connect with database
$pdo = new PDO("mysql:host=localhost; dbname=test;", "root", "", [
    PDO::ATTR_PERSISTENT => true
]);

This will make a persistent connection with the database. We are using PHP PDO because it has built-in support for SQL injection. After that, you need to fetch all the players already saved in the database.

// fetch all the players from the database
$statement = $pdo->prepare("SELECT * FROM players");
$statement->execute([]);
$rows = $statement->fetchAll(PDO::FETCH_OBJ);

Then we will set the range of numbers in which we want to generate a new number.

// set number of range for shirt numbers
$number_range = 10;

This will generate numbers between 0 and 10. After that, we will create a recursive function that will keep generating a new number unitl a unique shirt number is found.

// recursive function to return the new shirt number
function get_code()
{
    // define global variables to access them inside function
    global $rows, $number_range;

    // if all the numbers are taken, then return -1
    if (count($rows) >= $number_range)
        return -1;

    // generate a random number
    $code = rand(0, $number_range);

    // check if it already exists in the database
    foreach ($rows as $row)
    {
        if ($row->shirt_number == $code) // if number already exists, then start recursion i.e. generate another number
        return get_code();
    }

    // if number not already exists, then return it
    return $code;
}

We need to call this function once to start checking for new unique shirt number.

// initial call to recursive function
$code = get_code();

There is a possibility that all the numbers from 0 to 10 are already taken, in this case, we will display an error message.

// if all numbers are taken, display an error
if ($code == -1)
{
    echo "<p>All numbers are taken.</p>";
    exit;
}

If everything worked fine, we will have a new unique shirt number in our $code variable. We can simply insert it in our database.

// name of new player
$name = "Adnan";

// if shirt number not taken, then insert in database
$statement = $pdo->prepare("INSERT INTO players (name, shirt_number) VALUES (:name, :code)");
$statement->execute([
    ":name" => $name,
    ":code" => $code
]);

// display the new shirt number
echo "<p>New code: " . $code . "</p>";

Complete code

<?php

// connect with database
$pdo = new PDO("mysql:host=localhost; dbname=test;", "root", "root", [
    PDO::ATTR_PERSISTENT => true
]);

// fetch all the players from the database
$statement = $pdo->prepare("SELECT * FROM players");
$statement->execute([]);
$rows = $statement->fetchAll(PDO::FETCH_OBJ);

// set number of range for shirt numbers
$number_range = 10;

// recursive function to return the new shirt number
function get_code()
{
    // define global variables to access them inside function
    global $rows, $number_range;

    // if all the numbers are taken, then return -1
    if (count($rows) >= $number_range)
        return -1;

    // generate a random number
    $code = rand(0, $number_range);

    // check if it already exists in the database
    foreach ($rows as $row)
    {
        if ($row->shirt_number == $code) // if number already exists, then start recursion i.e. generate another number
        return get_code();
    }

    // if number not already exists, then return it
    return $code;
}

// initial call to recursive function
$code = get_code();

// if all numbers are taken, display an error
if ($code == -1)
{
    echo "<p>All numbers are taken.</p>";
    exit;
}

// name of new player
$name = "Adnan";

// if shirt number not taken, then insert in database
$statement = $pdo->prepare("INSERT INTO players (name, shirt_number) VALUES (:name, :code)");
$statement->execute([
    ":name" => $name,
    ":code" => $code
]);

// display the new shirt number
echo "<p>New code: " . $code . "</p>";

FTP manager in PHP, MySQL, Laravel

I created an online free FTP manager in PHP that allows developers to work on their projects from anywhere. I created this tool in PHP and MySQL using Laravel framework. The frontend is designed in Bootstrap and React JS.

Let’s discuss each feature and I will also show you how I built this.

What you will learn:

  • Connect with FTP.
  • List files from FTP directory.
  • Fetch file content.
  • Edit FTP file.
  • Create a new file.
  • Create a new folder.
  • Upload files.
  • Download files.
  • Rename file.
  • Delete file.
  • Delete folder.

We created a PHP class named “FTP” in a file called “FTP.php”. All our functions will go in that class.

1. Connect with FTP

The first step is to connect with FTP server. Following code will create a function that will connect with your FTP server (we are not calling it yet).

class FTP
{
    private $conn_id = null;
    private $server = "";
    private $username = "";
    private $password = "";
    
    private function do_connect()
    {
        try
        {
            // Establishing connection
            $this->conn_id = ftp_connect($this->server);

            if (!$this->conn_id)
            {
                echo json_encode([
                    "status" => "error",
                    "message" => "Could not connect to " . $this->server
                ]);
                exit();
            }

            // Login with username and password
            $login_result = ftp_login($this->conn_id, $this->username, $this->password);

            if (!$login_result)
            {
                echo json_encode([
                    "status" => "error",
                    "message" => "Wrong password for '" . $this->username . "'."
                ]);
                exit();
            }

            // Enable passive mode for better compatibility
            ftp_pasv($this->conn_id, true);
        }
        catch (\Exception $exp)
        {
            echo json_encode([
                "status" => "error",
                "message" => "Could not connect to " . $this->server
            ]);
            exit();
        }
    }
}

Comments has been added with each line for explanation. Make sure to enter your correct FTP server address, FTP account’s username and its password. In the next step, we will call this function to connect with FTP server.

2. List files from FTP directory

The next step is to list all files from FTP directory. Create the following function in your FTP class.

public function fetch_files()
{
    $this->do_connect();
    $path = "/directory-name";

    // Get file listing
    $file_list = ftp_nlist($this->conn_id, $path);

    if ($file_list === false)
    {
        echo json_encode([
            "status" => "error",
            "message" => "Error retrieving file list."
        ]);
        exit();
    }

    $files = [];
    foreach ($file_list as $file)
    {
        $obj = [
            "file" => $file,
            "size" => ftp_size($this->conn_id, $file)
        ];

        if ($obj["file"] != "." && $obj["file"] != "..")
        {
            array_push($files, $obj);
        }
    }

    // Close the connection
    ftp_close($this->conn_id);

    echo json_encode([
        "status" => "success",
        "message" => "Data has been fetched.",
        "files" => $files
    ]);
    exit();
}
  • This function will first connect with the FTP server.
  • Then it will fetch all the files from $path variable.
    • If the directory does not exists or if it does not have read permission, then it will return an error.
  • All files from that directory will be put in an array along with the size of the file in bytes.
    • If it is a directory, then it’s size will be -1.
  • Finally, it returns the array with the response.

3. Fetch file content

Reading the content of FTP file is necessary because in order to edit the file, we need to first view the file. Following function will fetch the content of file and return it in response.

public function fetch_content()
{
    $this->do_connect();
    $file = "/directory-name/file-name.php";

    // Local path for downloading the file
    $local_file = tempnam(sys_get_temp_dir(), 'ftp_download');

    // Download the file from FTP server to local file
    if (!ftp_get($this->conn_id, $local_file, $file, FTP_BINARY))
    {
        echo json_encode([
            "status" => "error",
            "message" => "Failed to read '" . $file . "'"
        ]);
        exit();
    }

    // Read contents of the local file
    $file_content = file_get_contents($local_file);
    
    // Display or process the file content as needed
    if ($file_content === false)
    {
        echo json_encode([
            "status" => "error",
            "message" => "Failed to read file content."
        ]);
        exit();
    }

    // Clean up: Delete the temporary local file
    unlink($local_file);

    echo json_encode([
        "status" => "success",
        "message" => "Data has been fetched.",
        "content" => $file_content
    ]);
    exit();
}
  • It will first download the file in a temporary directory to your server.
  • If the file does not exists, then it will return an error.
  • It will return an error if it fails to read the FTP file.
    • Usually it is because if the $path variable is a directory or an image or document file.
  • After the file’s content is fetched, we will remove the temporary file from our server.

4. Edit FTP file

After the file’s content is successfully fetched, the next step is to update the file. You are working in FTP, editing the code is what you will be doing most of the time.

Updating the content of file requires 2 steps:

  1. Saving the file in temporary folder.
  2. Uploading the temporary file on FTP server.
public function update_content()
{
    $this->do_connect();

    $file = "/directory-name/file-name.php";
    $content = "console.log(\"Hello world\")";

    // Local path for downloading the file
    $local_file = tempnam(sys_get_temp_dir(), 'ftp_download');

    // Write the new content to a local temporary file
    file_put_contents($local_file, $content);

    if (!ftp_put($this->conn_id, $remote_file, $local_file, FTP_ASCII))
    {
        echo json_encode([
            "status" => "error",
            "message" => "There was a problem updating '" . $remote_file . "'"
        ]);
        exit();
    }

    unlink($local_file);

    // Close the FTP connection
    ftp_close($this->conn_id);

    echo json_encode([
        "status" => "success",
        "message" => "File has been updated."
    ]);
    exit();
}

5. Create a new file

Creating a new file in FTP in PHP is as simple as updating the file. We just need to set the content of file as empty string. So we will be uploading an empty file to FTP server.

public function create_file()
{
    $this->do_connect();
    
    $path = "/directory-name";
    $name = "file-name.php";

    // Local file content to be uploaded
    $file_content = "";

    // Local path for downloading the file
    $local_file = tempnam(sys_get_temp_dir(), 'ftp_download');

    // Write the content to a temporary local file
    file_put_contents($local_file, $file_content);

    // Remote file path where the file will be created
    $remote_file = $path . "/" . $name; // Replace with the desired remote file path

    // Upload the local file to the FTP server
    if (!ftp_put($this->conn_id, $remote_file, $local_file, FTP_ASCII))
    {
        echo json_encode([
            "status" => "error",
            "message" => "There was a problem while creating '" . $name . "'"
        ]);
        exit();
    }

    unlink($local_file);

    // Close the FTP connection
    ftp_close($this->conn_id);

    echo json_encode([
        "status" => "success",
        "message" => "Successfully created '" . $name . "'"
    ]);
    exit();
}
  • Create a temporary empty file on local directory.
  • Upload it to FTP.
  • Remove the file from local directory.

6. Create a new folder

Creating a folder is must easier than creating a file on FTP server.

public function create_folder()
{
    $this->do_connect();

    $path = "/directory-name";
    $name = "folder-name";

    $folder = $path . "/" . $name;

    if (!ftp_mkdir($this->conn_id, $folder))
    {
        echo json_encode([
            "status" => "error",
            "message" => "There was a problem while creating '" . $name . "'"
        ]);
        exit();
    }

    echo json_encode([
        "status" => "success",
        "message" => "Successfully created '" . $name . "'"
    ]);
    exit();
}

Note: It will return an error if the directory already exists.

7. Upload files

To upload the file from local computer to FTP server, you need to first save it in your local server. Then upload the file to FTP server. After it is successfully uploaded, you can remove the file from local server.

public function upload()
{
    $this->do_connect();

    $path = "/directory-name";
    $file_name = basename($_FILES["file"]["name"]);
    $target_file = "uploads/" . $file_name;

    if (!move_uploaded_file($_FILES["file"]["tmp_name"], $target_file))
    {
        echo json_encode([
            "status" => "error",
            "message" => "The file " . htmlspecialchars(basename( $_FILES["file"]["name"])) . " has been uploaded."
        ]);
        exit();
    }

    if (!ftp_put($this->conn_id, $file_name, $target_file, FTP_ASCII))
    {
        echo json_encode([
            "status" => "error",
            "message" => "There was a problem updating '" . $file_name . "'"
        ]);
        exit();
    }

    // Close the FTP connection
    ftp_close($this->conn_id);

    echo json_encode([
        "status" => "success",
        "message" => "File(s) has been uploaded."
    ]);
    exit();
}

If you are working in Laravel, your upload function should be like this:

public function upload()
{
    $this->do_connect();
    $path = "/directory-name";

    $files = request()->file("files");
    foreach ($files as $file)
    {
        $remote_file = $file->getClientOriginalName();

        $file_path = "ftp/" . $file->getClientOriginalName();
        $file->storeAs("/private", $file_path);

        if (!ftp_put($this->conn_id, $remote_file, storage_path("app/private/" . $file_path), FTP_ASCII))
        {
            return response()->json([
                "status" => "error",
                "message" => "There was a problem updating '" . $remote_file . "'"
            ]);
        }

        Storage::delete("private/" . $file_path);
    }

    // Close the FTP connection
    ftp_close($this->conn_id);

    return response()->json([
        "status" => "success",
        "message" => "File(s) has been uploaded."
    ]);
}

8. Download files

Downloading the file to local computer requires 2 steps.

  1. First is to download the file on local server from FTP server.
  2. Then download it to local computer from local server.

Download file from FTP to local server

Following code will be used to download the file on local server.

public function download()
{
    $this->do_connect();

    $path = "/directory-name";
    $file = "file-name.php";

    // Local path for downloading the file
    $local_file = tempnam(sys_get_temp_dir(), 'ftp_download');

    $remote_file = $path . "/" . $file;

    // Download the file from FTP server to local file
    if (!ftp_get($this->conn_id, $local_file, $remote_file, FTP_BINARY))
    {
        echo json_encode([
            "status" => "error",
            "message" => "Failed to download '" . $remote_file . "'"
        ]);
        exit();
    }

    // Create a unique download URL for the file
    $download_url = url('download.php?file=' . urlencode($local_file) . '&name=' . urlencode($file));

    // Close the FTP connection
    ftp_close($this->conn_id);

    echo json_encode([
        "status" => "success",
        "message" => "File has been downloaded.",
        "download_url" => $download_url
    ]);
    exit();
}

It will also return the “download_url” variable. That will be link to file from where you can download the file.

Download from server to local computer

Create a new file named “download.php” in your server and write the following code in it:

if (isset($_GET['file']) && isset($_GET['name']))
{
    $temp_file = $_GET['file'];
    $file_name = $_GET['name'];

    if (file_exists($temp_file))
    {
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $file_name . '"');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($temp_file));

        // Read the file and output it to the browser
        readfile($temp_file);

        // Delete the temporary file
        unlink($temp_file);
        exit();
    }

    echo "File does not exist.";
    exit();
}

echo "Invalid request.";
exit();

9. Rename file

Renaming the file on FTP using PHP is very easy. You just have to provide 3 things:

  1. Folder name
  2. Old file name
  3. New file name
public function rename()
{
    $this->do_connect();

    $path = "/directory-name";
    $name = "new-file-name.php";
    $file = "old-file-name.php";

    // File to be renamed on the FTP server
    $remote_file = $path . "/" . $file;
    $remote_new_file = $path . "/" . $name;

    // Try to rename the file or folder
    if (!ftp_rename($this->conn_id, $remote_file, $remote_new_file))
    {
        echo json_encode([
            "status" => "error",
            "message" => "There was a problem renaming " . $file . " to " . $name
        ]);
        exit();
    }

    echo json_encode([
        "status" => "success",
        "message" => "Successfully renamed " . $file . " to " . $name
    ]);
    exit();
}

10. Delete file

Deleting a file from FTP in PHP is very simply. You just need to enter the full path of the file and call the PHP built-in ftp_delete function. Pass the FTP connection ID and file path as arguments.

public function delete_file()
{
    $this->do_connect();
    $path = "/directory-name/file-name.php";

    // Delete the file on the FTP server
    if (!ftp_delete($this->conn_id, $path))
    {
        echo json_encode([
            "status" => "error",
            "message" => "There was a problem while deleting " . $path
        ]);
        exit();
    }

    echo json_encode([
        "status" => "success",
        "message" => "Successfully deleted " . $path
    ]);
    exit();
}

11. Delete folder

Deleting a folder is NOT as simple as deleting a single file. There are 2 steps involved in deleting a folder from FTP in PHP.

  1. Recursively delete all files and folders inside that folder.
  2. Delete the folder itself.

So first we will create a function that will initialize the call to recursive function.

public function delete_folder()
{
    // Set maximum execution time to unlimited (0 means no limit)
    set_time_limit(0);

    $path = "/directory-path/folder-name";

    $this->do_connect();
    $this->ftp_delete_dir($path);

    echo json_encode([
        "status" => "success",
        "message" => "Folder has been deleted."
    ]);
    exit();
}

Our recursive function ftp_delete_dir will look like this:

// Function to recursively delete a directory
private function ftp_delete_dir($path)
{
    // Get the list of files in the directory
    $files = ftp_nlist($this->conn_id, $path);

    // Loop through each file
    foreach ($files as $file)
    {
        $file = basename($file);

        if ($file == '.' || $file == '..')
        {
            continue;
        }

        $file_path = "$path/$file";

        // If it's a directory, delete recursively
        if (@ftp_chdir($this->conn_id, $file_path))
        {
            ftp_chdir($this->conn_id, '..');
            ftpDelete($this->conn_id, $file_path);
        }
        else
        {
            // If it's a file, delete it
            ftp_delete($this->conn_id, $file_path);
        }
    }

    // Finally, remove the directory itself
    ftp_rmdir($this->conn_id, $path);
}

It does the following things:

  1. Loops through all the files inside that folder.
  2. Skip the “.” and “..” folders (they are just pointing towards current and parent directory respectively).
  3. If the file is a directory, start the recursion.
  4. Else delete the file.
  5. Finally at line #33, we are deleting the current directory itself.

So these are all the features we have in FTP manager I created in PHP and MySQL using Laravel framework. If you have any quetions regarding FTP or if you are having any problem, feel free to contact me.

Real Estate website in PHP, MySQL, Laravel

Real estate business that requires a website for their business, we are providing a free website for them in PHP and MySQL (Laravel framework).

Screenshots

Features

Following are the key features in this project. More features can be added on-demand.

Admin panel

There is a dedicated admin panel from where admin can manage all properties, set business email, phone and address. He can also manage the banners on home page. Administrator can also see all the messages received from “contact us” form from use side.

Properties

In this free real estate website, you can mention the properties available. From admin panel, you can add, edit or delete any property you want. You just need to enter the property name and its price. You can also upload an image of the property. For more information, you can write the detail on the “description” box.

Apart from these, you can also mention the following attributes of the property:

  • Area of property.
  • Number of floors.
  • Number of rooms.
  • Number of bedrooms.
  • Number of bathrooms.
  • Parking space.
  • And payment process (Cash, Bank, Cheque etc.).

Home banners

You can upload banners for home page. When user lands on your real estate website, he will see those banners right infront of him. You can upload as many banners as you want, but the ideal number is 3. You can delete any banner whenever you want.

Business information

To update the business information like phone, email and address. You don’t need to go into source code and update these on all places. On admin panel, you can goto “settings” and enter their values and they will be updated on all places in the website. You can also set the name of website from settings page.

Social network

Same as you can update your business information, you can enter your social media links from admin panel. Right now you can set the following social network links:

  • Facebook
  • Twitter
  • Instagram
  • LinkedIn

But more can be added on-demand.

Contact us

On user-side, we have a “contact us” form from where user can enter his name and email and send a message. You can see all user’s sent messages on admin panel. You can see their name and email, so if you want to contact them back, you can do easily.

Installation

  1. Create a database “real_estate” in your phpMyAdmin.
  2. Goto config/database.php.
  3. Set username and password for MySQL.

Run the following commands at root directory:

  1. COMPOSER_MEMORY_LIMIT=-1 composer update
  2. php artisan storage:link
  3. php artisan key:generate
  4. php artisan migrate
  5. name=”Admin” email=”admin@gmail.com” password=”admin” php artisan db:seed –class=DatabaseSeeder
  6. php artisan serve

Project will start running at: http://127.0.0.1:8000

Password-less authentication in PHP and MySQL

Previously we did password less authentication in Node JS and Mongo DB, in this article we will do it in PHP and MySQL.

Suppose you are trying to login to someone’s computer. You might open an incognito window in order to prevent the password from being saved in the browser. But what if he has installed a keylogger in his computer ? He can track your keystrokes and will know your email and password.

You can try typing your password from virtual keyboard, but what if he has also installed a screen recroding software that quitely records the screen ?

So the website developers must provide a way to secure their users from such vulnerabilities.

The best way is to allow users to login without entering their password. In-fact, there will be no password. User just have to enter his email address. We will send him a verification code. He needs to enter that code to verify.

That code will be auto-generated and will not be used again once user is logged-in. So even if some keylogging or screen recording software knows the verification code, it will be of no use. Because the code will not work next time someone tries with that user’s email address.

Create a Table

First I am going to create users table. If you already have one, you just need to add another column code in it.

<?php

$db_name = "test";
$username = "root";
$password = "";

$conn = new PDO("mysql:host=localhost;dbname=" . $db_name, $username, $password);

$sql = "CREATE TABLE IF NOT EXISTS users(
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email TEXT NULL,
    code TEXT NULL
)";

$result = $conn->prepare($sql);
$result->execute();

I am using PDO to prevent SQL injection. If you want to know more about PHP PDO, you can check our guide here. If you refresh the page now and check your phpMyAdmin, you will have users table created in your database.

users-table-password-less-authentication-php
users-table-password-less-authentication-php

Login Form

Next we need to create a form to ask for user’s email address. It will also have a submit button.

<form method="POST" action="index.php">
    <p>
        <input type="email" name="email" placeholder="Enter email" />
    </p>

    <input type="submit" value="Login" />
</form>

This will create an input field and a submit button.

Send email in PHP

Then we need to generate a random code and send an email with that verification code. For sending email, we will be using a library PHPMailer. You can download it and extract the folder in your project’s root directory.

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

require 'PHPMailer/src/Exception.php';
require 'PHPMailer/src/PHPMailer.php';
require 'PHPMailer/src/SMTP.php';

function send_mail($to, $subject, $body)
{
    //Create an instance; passing `true` enables exceptions
    $mail = new PHPMailer(true);

    try {
        //Server settings
        $mail->SMTPDebug = 0;                      //Enable verbose debug output
        $mail->isSMTP();                                            //Send using SMTP
        $mail->Host       = 'mail.adnan-tech.com';                     //Set the SMTP server to send through
        $mail->SMTPAuth   = true;                                   //Enable SMTP authentication
        $mail->Username   = 'support@adnan-tech.com';                     //SMTP username
        $mail->Password   = '';                               //SMTP password
        $mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS;            //Enable implicit TLS encryption
        $mail->Port       = 465;                                    //TCP port to connect to; use 587 if you have set `SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS`

        //Recipients
        $mail->setFrom('support@adnan-tech.com', 'Adnan Afzal');
        $mail->addAddress($to);               //Name is optional

        //Content
        $mail->isHTML(true);                                  //Set email format to HTML
        $mail->Subject = $subject;
        $mail->Body    = $body;
        $mail->AltBody = $body;

        $mail->send();
        // echo 'Message has been sent';
    } catch (Exception $e) {
        // echo "Message could not be sent. Mailer Error: {$mail->ErrorInfo}";
    }
}

if ($_SERVER["REQUEST_METHOD"] == "POST")
{
    $email = $_POST["email"];

    $str = "qwertyuiopasdfghjklzxcvbnm1234567890";
    $code = "";

    for ($a = 1; $a <= 6; $a++)
    {
        $code .= $str[rand(0, strlen($str) - 1)];
    }

    $subject = "Login";
    $body = "Your verification code is " . $code;

    $sql = "SELECT * FROM users WHERE email = :email";
    $result = $conn->prepare($sql);
    $result->execute([
        ":email" => $email
    ]);
    $user = $result->fetch();

    if ($user == null)
    {
        $sql = "INSERT INTO users(email, code) VALUES (:email, :code)";
        $result = $conn->prepare($sql);
        $result->execute([
            ":email" => $email,
            ":code" => $code
        ]);

        send_mail($email, $subject, $body);

        header("Location: verify.php?email=" . $email);
        exit();
    }

    $sql = "UPDATE users SET code = :code WHERE email = :email";
    $result = $conn->prepare($sql);
    $result->execute([
        ":email" => $email,
        ":code" => $code
    ]);

    send_mail($email, $subject, $body);

    header("Location: verify.php?email=" . $email);
    exit();
}

Here, first I am generating a random 6 character code. Then I am setting the body of the email in a $body variable.

Then I am checking if the user already exists in the database. If not, then insert a new row in database. Sends an email and redirect the user to the verification page.

If user already exists, then I am simply updating his code column. Sends an email and redirect to a new page to verify the code.

Note: You need to enter your correct SMTP credentials (email and password) in order to send the email.

Verify the Code

Create a new file verify.php and inside it, first get the email from the URL. Then create a form with an hidden input field for email. One input field for user to enter the code sent at email, and a submit button.

<?php

    $db_name = "test";
    $username = "root";
    $password = "";

    $conn = new PDO("mysql:host=localhost;dbname=" . $db_name, $username, $password);
    $email = $_GET["email"] ?? "";

?>

<form method="POST" action="verify.php">
    <input type="hidden" name="email" value="<?php echo $email; ?>" />

    <p>
        <input type="text" name="code" placeholder="Enter your code here..." required />
    </p>

    <input type="submit" value="Login" />
</form>

We are creating a hidden input field so that it will be sent along with form. Now when the form submits, we need to check if user has provided correct code.

if ($_SERVER["REQUEST_METHOD"] == "POST")
{
    $email = $_POST["email"] ?? "";
    $code = $_POST["code"] ?? "";

    $sql = "SELECT * FROM users WHERE email = :email AND code = :code";
    $result = $conn->prepare($sql);
    $result->execute([
        ":email" => $email,
        ":code" => $code
    ]);
    $user = $result->fetch();

    if ($user == null)
    {
        die("Invalid code.");
    }

    $sql = "UPDATE users SET code = NULL WHERE id = :id";
    $result = $conn->prepare($sql);
    $result->execute([
        ":id" => $user["id"]
    ]);

    die("Logged-in");
}

If the user provides an in-valid code, then we are simply displaying him an error message. If he has provided the correct code, then we are setting his code column to NULL so it won’t be used again.

You can also try sending an AJAX and pass the code value as null, it still won’t be logged-in.

// for testing only

const ajax = new XMLHttpRequest()
ajax.open("POST", "verify.php", true)

const formData = new FormData()
formData.append("email", "support@adnan-tech.com")
formData.append("code", null)
ajax.send(formData)

So that’s it. That’s how you can add password-less authentication system in your application using PHP and MySQL. If you face any problem in following this, kindly do let me know.

Laravel Soft Delete – Create a Trash Can using PHP and MySQL

Laravel soft delete allows you to enable some models that will not be permanently deleted by just calling the delete() method. Instead, you need to call the forceDelete() method in order to permanently delete the data.

The deleted models will not be fetched from Eloquent or Query Builder queries. Instead, you need to call a separate function in order to fetch the records along with deleted ones, we will get on that later in this article. Laravel soft delete has several advantages:

  • First, it works like your PC’s recycle bin. If you accidentally deleted from record from your admin panel, it will not be deleted permanently at once.
  • Second, you can display all deleted data on a separate page.
  • You have a backup of your deleted data, so in the future, if you need some old data, you can always get it from recycle bin or trash can.
  • Can display all data to the user and highlight the data that is deleted.
  • Always be able to restore the accidentally deleted records.

Let’s get started

So let’s get started by creating a simple Laravel soft delete module. For the sake of this article, we will be implementing a trash can in our user’s model.

1. Creating a column in the user’s table

First, you need to run the following command to create a migration for soft deleting:

php artisan make:migration add_column_soft_delete_in_users_table

Then you need to open your migration file and add the following line in the Schema::table function inside up() method:

$table->softDeletes();

Then you need to run the migration using the following command:

php artisan migrate

Open your phpMyAdmin and you will see a new column at the end of the user’s table named deleted_at. Its default value will be NULL and it will have a current date & time value once you call the delete() method.

2. Laravel Soft Delete Eloquent Model

Open your user model usually in App\Models\User.php and update it as the following:

use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use HasFactory, Notifiable, SoftDeletes;

    ///////////
}

After that, whenever you call the delete() method on any user object, you will see that deleted_at the field will be updated to the current date & time of that user.

3. Display all Users with Pagination

Let’s say that you have an admin panel. We will be displaying all users (non-deleted) on one page. So first create a route for this in your routes/web.php file:

use App\Http\Controllers\UserController;

Route::group([
    "prefix" => "users"
], function () {
    Route::get("/", [UserController::class, "index"]);

    // [other routes goes here]
});

We have created a group because we will be adding more routes to it. For example, route to delete a user, route to display all deleted users, route to restore a user, and route to permanently delete a user. Run the following command if you do not have the UserController:

php artisan make:controller UserController

Now, create an index() method in your UserController class. Following is what your UserController should look like:

use App\Models\User;

public function index()
{
    $users = User::orderBy("id", "desc")->paginate();
    $trashed = User::onlyTrashed()->count();

    return view("users/index", [
        "users" => $users,
        "trashed" => $trashed
    ]);
}

Create a folder named users and inside this folder, create a file in resources/views/users/index.blade.php. This file should have the following code:

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/css/bootstrap.min.css" />
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/js/bootstrap.min.js"></script>

<a href="{{ url('/users/trashed') }}">
    Trash ({{ $trashed }})
</a>

<table class="table table-bordered">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
        </tr>
    </thead>

    <tbody>
        @foreach ($users as $user)
            <tr>
                <td>{{ $user->id }}</td>
                <td>{{ $user->name }}</td>
                <td>{{ $user->email }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

{{ $users->appends(request()->except("page"))->links() }}

Refresh the page now and you will see a list of all users. Now we need to create a button to delete a user.

4. Delete a User

To delete a user, we will first a heading in our <thead> tag:

<th>Actions</th>

Then in <tbody>, we need to create a form which when submit will first ask for confirmation. If confirmed, then it will mark the user as deleted.

<td>
	<form method="POST" action="{{ url('/users/delete') }}" onsubmit="return confirm('Are you sure you want to delete this user ?');">
		{{ csrf_field() }}
		<input type="hidden" name="id" value="{{ $user->id }}" required>
		<button type="submit" class="btn btn-danger">
			Delete
		</button>
	</form>
</td>

Refresh the page and you will see a delete button along with each user. Then create a route on your web.php file:

Route::post("/delete", [UserController::class, "destroy"]);

The above lines go in the [other routes goes here] section of web.php. Then create a method in your UserController to mark the user as deleted:

public function destroy()
{
    $user = User::find(request()->id);
    if ($user == null)
    {
        abort(404);
    }

    $user->delete();
    return redirect()->back();
}

You can delete the user in one line too, like this: User::destroy(request()->id); but fetching the user first has some advantages:

  • You can perform any other sub-functions before deleting a user.
  • Or check if the user exists or not, and display the proper messages accordingly.

Try deleting a user now, you will see that will no longer be displayed in your Bootstrap table. But you can still see the record in your database using phpMyAdmin. However, you will notice that the deleted_at the field is updated and now has the UTC date and time value when the delete operation was performed.

5. Show All Deleted Users

At 3rd step, we create an anchor tag that displays all the trashed user’s counts. Now is the time to create a route for it. To create a route on your routes/web file.

Route::get("/trashed", [UserController::class, "trashed_users"]);

Then create the following method in your UserController:

public function trashed_users()
{
    $trashed = User::onlyTrashed()->orderBy("id", "desc")->paginate();

    return view("users/trashed", [
        "trashed" => $trashed
    ]);
}

After that, create a file resources/views/users/trashed.blade.php. It will have the following code:

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/css/bootstrap.min.css" />
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/js/bootstrap.min.js"></script>

<table class="table table-bordered">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Deleted at</th>
            <th>Actions</th>
        </tr>
    </thead>

    <tbody>
        @foreach ($trashed as $user)
            <tr>
                <td>{{ $user->id }}</td>
                <td>{{ $user->name }}</td>
                <td>{{ $user->email }}</td>
                <td>{{ $user->deleted_at }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

{{ $trashed->appends(request()->except("page"))->links() }}

Refresh the page now and you will see all deleted users in a table. You will also be able to view the date and time when they were deleted.

6. Restore the Deleted Records

First, create another <td> tag at the end of <tbody> tag. In this table cell, we will create a button that will ask for confirmation. And when confirmed, will restore the user.

<td>
    <form method="POST"
        action="{{ url('/users/restore') }}"
        onsubmit="return confirm('Are you sure you want to restore this user ?');">

        {{ csrf_field() }}

        <input type="hidden" name="id" value="{{ $user->id }}" required />
        <button type="submit" class="btn btn-success">
            Restore
        </button>
    </form>
</td>

Then create a route in your web/routes.php that will handle this request.

Route::post("/restore", [UserController::class, "do_restore"]);

After that, create the following method in your UserController:

public function do_restore()
{
    $user = User::withTrashed()->find(request()->id);
    if ($user == null)
    {
        abort(404);
    }

    $user->restore();
    return redirect()->back();
}

Refresh the page now and you will see a “Restore” button along with each delete user. On clicking that, you will be asked for confirmation. If confirmed, you will no longer see that record in the trash can. But you will start seeing that record in all users table.

If you open your database using phpMyAdmin, you will see that user will again have the deleted_at column value as NULL.

7. Permanently Delete Records

Where you are displaying all soft-deleted records in your trash can, you are already displaying a button to restore the record. Now is the time to display another button that will permanently delete the record from the database. Create a button along with the “Restore” button in your trash can:

<form method="POST"
    action="{{ url('/users/delete-permanently') }}"
    onsubmit="return confirm('Are you sure you want to permanently delete this user ?');">

    {{ csrf_field() }}

    <input type="hidden" name="id" value="{{ $user->id }}" required />
    <button type="submit" class="btn btn-danger">
        Delete
    </button>
</form>

Then, you need to create a route that will handle this request.

Route::post("/delete-permanently", [UserController::class, "delete_permanently"]);

Then you need to create a method in your UserController that will permanently delete that record from the database.

public function delete_permanently()
{
    $user = User::withTrashed()->find(request()->id);
    if ($user == null)
    {
        abort(404);
    }

    $user->forceDelete();
    return redirect()->back();
}

Refresh the page now and click on the delete button from the trash can. You will be asked for confirmation. Once confirmed, you will no longer see that record in the trash can nor in all users table. If you check the database, you will see that, that user’s row has been permanently deleted from the database.

Get our social networking site project developed in Laravel:

Social Networking Site in Laravel – Node JS, Socket IO

So that’s how you can implement a complete Laravel soft delete module in your web application.

[wpdm_package id=’1545′]

Save and Download BLOB images – PHP and MySQL

In this article, we will show you, how you can save BLOB images in a MySQL database using PHP. You will also be able to download the BLOB images as well. To upload the file to the server and save its path in the MySQL database, you can follow our this tutorial.

Table structure:

Run the following code in your index.php file to create the required table that will hold all saved BLOB images:

<?php
	$conn = mysqli_connect("localhost", "root", "", "blob");
	$sql = "CREATE TABLE IF NOT EXISTS `images` (
		`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
        `image` LONGBLOB NOT NULL,
		`name` TEXT NOT NULL,
		`type` VARCHAR (11) NOT NULL
	)";
	mysqli_query($conn, $sql);
?>

Laravel developers can do it in the following way:

use Illuminate\Support\Facades\DB;

Schema::create('images', function (Blueprint $table) {
    $table->id();
    $table->longText("name")->nullable();
    $table->longText("type")->nullable();
    // $table->binary("image");
    // OR
    // $table->longText("image")->charset('binary');
    $table->timestamps();
});

DB::statement("ALTER TABLE images ADD image LONGBLOB DEFAULT NULL AFTER id");

Run your file and you will see a new table created in your phpMyAdmin.

Save BLOB images in MySQL:

First, you need to create an input field from which you can upload an image.

<form method="POST" action="upload.php" enctype="multipart/form-data">
	<p>
		<label>Upload Image</label>
		<input type="file" name="image" accept="image/*" required />
	</p>
	<input type="submit" value="Upload" />
</form>

Create a file named upload.php and write the following code in it:

<?php
	// assume you have a database named 'blob'
	$conn = mysqli_connect("localhost", "root", "", "blob");
	$image = $_FILES["image"];
	$info = getimagesize($image["tmp_name"]);
    if(!$info)
	{
		die("File is not an image");
	}
	$name = $image["name"];
    $type = $image["type"];
	$blob = addslashes(file_get_contents($image["tmp_name"]));

    // if you are developing in Laravel
    // $blob = file_get_contents($file->getRealPath());

	$sql = "INSERT INTO `images` (`image`, `name`, `type`) VALUES ('" . $blob . "', '" . $name . "' , '" . $type . "')";
	mysqli_query($conn, $sql) or die(mysqli_error($conn));
    echo "File has been uploaded.";
?>

Run the code now and you will be displayed an input field and a submit button. Select an image file and hit the submit button, if all goes well, you will see a success message. Then you can check in your database, a new row will be created, the image will be saved as a blob file and the name of the image file will be saved in name column.

Show all Saved BLOB images:

To show all saved BLOB files, you need to run the following code:

<?php
	$conn = mysqli_connect("localhost", "root", "", "blob");
	$sql = "SELECT id, name FROM `images` ORDER BY `id` DESC";
	$result = mysqli_query($conn, $sql);
	
	while ($row = mysqli_fetch_object($result))
	{
?>
	<p>
		<a href="download.php?id=<?php echo $row->id; ?>" target="_blank">
		    <?php echo $row->name; ?>
        </a>
	</p>
<?php } ?>

This will display all saved files in descending order (latest to oldest) in paragraphs. It will also display an anchor tag which when clicked will download the file.

Download BLOB images from MySQL:

To download the Blob file, simply create a file named download.php and write the following code in it:

<?php
	$conn = mysqli_connect("localhost", "root", "", "blob");
	$sql = "SELECT * FROM `images` WHERE `id` = " . $_GET["id"];
	$result = mysqli_query($conn, $sql);
	if (mysqli_num_rows($result) == 0)
	{
		die("File does not exists.");
	}
	$row = mysqli_fetch_object($result);
    header("Content-type: " . $row->type);
	echo $row->image;
?>

To convert the BLOB to base64 in PHP, do the following:

$base64 = "data:" . $row->type . ";base64," . base64_encode($row->image);
// output = data:image/jpeg;base64,9j/4AAQSkZJRgABAQAAAQABA...

Go ahead and click the file name from the list, you will see that file opened in a browser in a new tab. You can simply press Ctrl + S to download the file in your system. Using this method, you can also save your files from direct access from the URL. Since the files are not physically saved anywhere on the server, they will not be directly accessible from the URL.

And since the file can only be accessed via PHP function, you can put additional conditions like allowing download files to premium users only, etc.

[wpdm_package id=’1541′]

Premium projects bundle

Buy the 14 premium projects bundle for $1000. The bundle contains projects in the following technologies:

  • Node JS and Mongo DB
  • PHP & MySQL, Laravel
  • Vue JS
  • Android apps

Project’s list

  1. Single page chat application – Vue JS, Node JS, Mongo DB
  2. E-commerce single page application – Vue JS, Node JS, Mongo DB
  3. Chat app – Android, Web admin panel
  4. Email marketing tool – Laravel, Vue JS
  5. Financial ledger – Vue JS, Node JS, Mongo DB
  6. Image sharing web app – Node JS, Mongo DB / MySQL
  7. Movie ticket booking site – PHP & MySQL, MVC
  8. Realtime blog in Node JS and Mongo DB
  9. File transfer web app – Node JS + Mongo DB
  10. Realtime customer support chat widget
  11. Video streaming website – Node JS, Mongo DB
  12. Picture competition web app – Node JS, Mongo DB
  13. Questionnaire – Node JS, Mongo DB
  14. Blog website – Laravel, Google Adsense approved

You can buy each project separately too as well. Besides getting all the pro features of all 14 projects, we also provide additional services to them.

Secure payment

We allow users to make payments easily and securely using their bank accounts. You can contact us here and we will send you an invoice to make the payment.

Source code

Complete source code is included in all the projects. You will enjoy the pro version of each project.

Support

If you encounter any problem in installing the project or deployment, our technical support team is here. You can schedule a meeting with them and they will be able to assist you over AnyDesk or TeamViewer.

Use as a college project

If you are a student and are looking to get ready-to-go projects to learn about code and how real projects work, this will be beneficial for you. Also, Node JS projects are good learning points for students in advanced programming.

Customize as you want

Once you download the projects, you can customize them as per your needs. You can change the color theme, add new features to it, etc.

Get help in deployment

Once projects are set up in your local host, we will be here if you needed any help in deployment to the live server. For Node JS projects, we will assist you in deployment to Heroku. And for Mongo DB projects, we will help you with a deployment to mongodb.com.

Price: $1000

Out TrustPilot reviews

TrustPilot-reviews
TrustPilot-reviews

CRUD in Vue JS and PHP

In this tutorial, we are going to perform a complete CRUD (create, read, update, delete) operation using Vue JS and PHP. We will be using Vue JS for the frontend and PHP for backend processing. We will also be using PDO prepared statements for preventing SQL injection.

First, you need to download Vue JS from here and Bootstrap from here. You will also need to download jQuery from here as well. After that, you need to copy-paste the JS file from Vue JS in your project. You also need to copy-paste the CSS and JS files from Bootstrap too.

After downloading and placing in your project, you need to include these files as well.

<script src="vue.min.js"></script>

<link rel="stylesheet" type="text/css" href="css/bootstrap.css" />
<script src="js/jquery-3.3.1.min.js"></script>
<script src="js/bootstrap.js"></script>

Create

First thing is that you need to insert the data into the database. For the sake of this tutorial, we have created a simple table in our MySQL database. The table name is users and it has the following columns:

  1. id (auto increment)
  2. name
  3. email
  4. password

Then you need to create a form from which you can enter the details to save in the database.

<div id="myApp">
	<div class="container">
		<h1 class="text-center">Create</h1>

		<div class="row">
			<div class="offset-md-3 col-md-6">
				<form method="POST" action="create.php" v-on:submit.prevent="doCreate">
					<div class="form-group">
						<label>Name</label>
						<input type="text" name="name" class="form-control" />
					</div>

					<div class="form-group">
						<label>Email</label>
						<input type="email" name="email" class="form-control" />
					</div>

					<div class="form-group">
						<label>Password</label>
						<input type="password" name="password" class="form-control" />
					</div>

					<input type="submit" value="Create User" class="btn btn-primary" />
				</form>
			</div>
		</div>
	</div>
</div>

After that, we need to create a Vue JS app and a method that will call an AJAX request to the server.

<script>
	// initialize Vue JS
	const myApp = new Vue({
		el: "#myApp",
		methods: {

			doCreate: function () {
				const self = this;
				const form = event.target;

				const ajax = new XMLHttpRequest();
				ajax.open("POST", form.getAttribute("action"), true);

				ajax.onreadystatechange = function () {
					if (this.readyState == 4) {
						if (this.status == 200) {
							console.log(this.responseText);
						}
					}
				};

				const formData = new FormData(form);
				ajax.send(formData);
			}
		},
	});
</script>

Now create a file named create.php that will handle the request.

<?php

// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");

// prepare insert statement
$sql = "INSERT INTO users (name, email, password) VALUES (:name, :email, :password)";
$result = $conn->prepare($sql);

// execute the query
$result->execute([
	":name" => $_POST["name"],
	":email" => $_POST["email"],
	// encrypt password in hash
	":password" => password_hash($_POST["password"], PASSWORD_DEFAULT),
]);

echo "Done";

Refresh the page now and enter new user details and hit submit. Then go to your phpMyAdmin and refresh the users table and you will see a new row inserted in the database.

Read

Now the data is being inserted in the database but you should all the inserted data when the page loads. To create an HTML table:

<h1 class="text-center">Read</h1>

<table class="table">
	<tr>
		<th>ID</th>
		<th>Name</th>
		<th>Email</th>
	</tr>

	<tr v-for="(user, index) in users">
		<td v-text="user.id"></td>
		<td v-text="user.name"></td>
		<td v-text="user.email"></td>
	</tr>
</table>

In your Vue JS instance, create a data object, and inside that object create a users array.

data: {
	users: []
},

And when the Vue JS instance is mounted, we need to call a method to call an AJAX request to get the data.

// call an AJAX to fetch data when Vue JS is mounted
mounted: function () {
	this.getData();
}

After that, create a method in your methods object in the Vue JS instance:

// get all users from database
getData: function () {
	const self = this;

	const ajax = new XMLHttpRequest();
	ajax.open("POST", "read.php", true);

	ajax.onreadystatechange = function () {
		if (this.readyState == 4) {
			if (this.status == 200) {
				const users = JSON.parse(this.responseText);
				self.users = users;
			}
		}
	};

	const formData = new FormData();
	ajax.send(formData);
},

This will send an AJAX request but we need to create a server file that will handle this request. So create a file named read.php and the following will be the code of this file:

<?php

// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");

// get all users from database sorted by latest first
$sql = "SELECT * FROM users ORDER BY id DESC";
$result = $conn->prepare($sql);
$result->execute([]);
$data = $result->fetchAll();

// send all records fetched back to AJAX
echo json_encode($data);

Refresh the page now and you will be able to view all the records added to the database. But if you insert the new record again, you again have to refresh the page to see this new entry. However, newly inserted records should automatically be prepended at the top of the table.

So you need to modify your create.php and first return the newly inserted record from the database.

// get the latest record inserted
$sql = "SELECT * FROM users WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute(array(
    ":id" => $conn->lastInsertId()
));
$data = $result->fetch();

// send the newly inserted record back to AJAX
echo json_encode($data);

Then in your Vue JS instance inside the doCreate method when the response is successfully received, prepend the new user in the users array.

const user = JSON.parse(this.responseText);

// prepend in local array
self.users.unshift(user);

Refresh the page now and try to insert a new user again. Now you will see that it will be prepended at the top automatically.

Update

To update the user we first must show a button to edit the user. To create a new column in your table:

<th>Actions</th>

And inside the v-for loop create a button for edit.

<td>
	<button type="button" v-bind:data-id="user.id" v-on:click="showEditUserModal" class="btn btn-primary">Edit</button>
</td>

Then you need to create a method in the methods object of your Vue JS instance that will be called when this button is clicked.

showEditUserModal: function () {
	const id = event.target.getAttribute("data-id");
	
	// get user from local array and save in current object
	for (var a = 0; a < this.users.length; a++) {
		if (this.users[a].id == id) {
			this.user = this.users[a];
			break;
		}
	}

	$("#editUserModal").modal("show");
},

Then you need to create another variable in your data object that will hold the information of the selected user.

user: null

The above-created function will display a Bootstrap modal to edit the user. Now we need to create that model in our HTML.

<!-- Modal -->
<div class="modal" id="editUserModal">
	<div class="modal-dialog" role="document">
		<div class="modal-content">
			<div class="modal-header">
				<h5 class="modal-title">Edit User</h5>
				<button type="button" class="close" data-dismiss="modal" aria-label="Close">
					<span aria-hidden="true">&times;</span>
				</button>
			</div>
			
			<div class="modal-body">
				<form method="POST" action="update.php" v-on:submit.prevent="doUpdate" id="form-edit-user" v-if="user != null">
					<input type="hidden" name="id" v-bind:value="user.id" />

					<div class="form-group">
						<label>Name</label>
						<input type="text" name="name" v-bind:value="user.name" class="form-control" />
					</div>

					<div class="form-group">
						<label>Email</label>
						<input type="email" name="email" v-bind:value="user.email" class="form-control" />
					</div>
				</form>
			</div>

			<div class="modal-footer">
				<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
				<button type="submit" name="submit" class="btn btn-primary" form="form-edit-user">Save changes</button>
			</div>
		</div>
	</div>
</div>

When the edit form is submitted, it called a Javascript function to call an AJAX request to the server. So we need to create that method in our Vue JS instance methods object.

// update the user
doUpdate: function () {
	const self = this;
	const form = event.target;

	const ajax = new XMLHttpRequest();
	ajax.open("POST", form.getAttribute("action"), true);

	ajax.onreadystatechange = function () {
		if (this.readyState == 4) {
			if (this.status == 200) {

				const user = JSON.parse(this.responseText);

				// update in local array
				// get index from local array
				var index = -1;
				for (var a = 0; a < self.users.length; a++) {
					if (self.users[a].id == user.id) {
						index = a;
						break;
					}
				}

				// create temporary array
				const tempUsers = self.users;

				// update in local temporary array
				tempUsers[index] = user;

				// update the local array by removing all old elements and inserting the updated users
				self.users = [];
				self.users = tempUsers;
			}
		}
	};

	const formData = new FormData(form);
	ajax.send(formData);

	// hide the modal
	$("#editUserModal").modal("hide");
},

Now we need to create a new file called update.php that will handle this AJAX request and will update the data in database.

<?php

// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");

// update user name and email using his unique ID
$sql = "UPDATE users SET name = :name, email = :email WHERE id = :id";
$result = $conn->prepare($sql);

$result->execute([
	":name" => $_POST["name"],
	":email" => $_POST["email"],
	":id" => $_POST["id"],
]);

// get the updated record
$sql = "SELECT * FROM users WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute(array(
    ":id" => $_POST["id"]
));
$data = $result->fetch();

// send the updated record back to AJAX
echo json_encode($data);

Refresh the page now, and you will see an “Edit” button with each row. On click, you will see a modal to update the data. Once submitted, the data will be updated in the database, the bootstrap modal will be closed and you will also see the data updated in the HTML table too.

Delete

To complete the CRUD operation in Vue JS and PHP. The final step is to create a “Delete” button. In front of each edit button, we need to create another button that will delete the user from the database and from the local array as well.

<form method="POST" action="delete.php" v-on:submit.prevent="doDelete" style="display: contents;">
	<input type="hidden" name="id" v-bind:value="user.id" />
	<input type="submit" name="submit" class="btn btn-danger" value="Delete" />
</form>

Then we need to create a method in Vue JS that will call an AJAX request to delete the user.

// delete user
doDelete: function () {
	const self = this;
	const form = event.target;

	const ajax = new XMLHttpRequest();
	ajax.open("POST", form.getAttribute("action"), true);

	ajax.onreadystatechange = function () {
		if (this.readyState == 4) {
			if (this.status == 200) {
				
				// remove from local array
				for (var a = 0; a < self.users.length; a++) {
					if (self.users[a].id == form.id.value) {
						self.users.splice(a, 1);
						break;
					}
				}
			}
		}
	};

	const formData = new FormData(form);
	ajax.send(formData);
},

Lastly, we need to create a file named delete.php that will handle this request and will actually delete the user from the database.

<?php

// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");

// delete the user from database
$sql = "DELETE FROM users WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute(array(
    ":id" => $_POST["id"]
));

// send the response back to AJAX
echo "Done";

Refresh the page now and you will see a “Delete” button too with each row. On clicking, will delete that record from the database, and also it will delete the HTML table row as well.

Congratulations! You just completed your CRUD operation in Vue JS and PHP.

Download CRUD in Vue JS and PHP source code:

[wpdm_package id=’1511′]

Create a single page application in MEVN stack

Free tutorial