Creating a CRUD Application with PHP, MySQL, and Lithe

This step-by-step guide will teach you how to build a CRUD application using PHP, MySQL, and the Lithe framework. We will cover everything from setting up the database to implementing controllers to manage CRUD operations.

Environment Setup

Before you begin, make sure you have Composer installed on your machine. Composer is an essential tool for managing dependencies in PHP projects. If you don’t have Composer, you can download and install it from the official Composer website.

Creating a New Lithe Project

To create a new project with the Lithe framework, open your terminal and run the following command:

composer create-project lithephp/lithephp crud-app

This will create a directory named crud-app, where all the Lithe project files will be installed. After installation, navigate to the project directory:

cd crud-app

Configuring the Database

Now, you need to configure the database connection. Locate the .env file in the root of your project. This file stores the environment variables that Lithe will use during execution.

In the .env file, set your database connection variables as shown in the example below:

DB_CONNECTION_METHOD=mysqli
DB_CONNECTION=mysql
DB_HOST=localhost
DB_NAME=lithe
DB_USERNAME=root
DB_PASSWORD=
DB_SHOULD_INITIATE=true

Variable Details

• DB_CONNECTION_METHOD: Connection method (in this case, mysqli).
• DB_CONNECTION: Type of database (e.g., mysql).
• DB_HOST: Database server address (typically localhost for local development).
• DB_NAME: Name of the database to be used (e.g., lithe).
• DB_USERNAME: Username to access the database (default root for many local setups).
• DB_PASSWORD: Password for the database user (leave blank if there is none).
• DB_SHOULD_INITIATE: Defines whether the database should be automatically initialized when the application starts.

After configuring the .env file, you will be ready to proceed with creating the CRUD application.

With these variables, Lithe connects automatically to the database. This is done by the configuration file in src/database/config/database.php, which contains the following method:

use Lithe\Database\Manager as DB;

return DB::initialize();

"After initializing the connection using the static method initialize() from the Manager class, the configured connection will be accessible through the static method connection. This allows for efficient and secure execution of database queries and operations."

Creating Migrations

After configuring the database, the next step is to create the database structure. For this, we use the make:migration command of Lithe. Run the following command to create a migration:

php line make:migration CreateUsersTable

This will create a migration file inside the src/database/migrations/ directory, with a name in the format YYYY_MM_DD_HHMMSS_CreateUsersTable.php.

The created migration will have the following structure:

return new class
{
    /**
     * Executes the migrations.
     *
     * @param mysqli $db
     * @return void
     */
    public function up(mysqli $db): void
    {
        $query = "
            CREATE TABLE IF NOT EXISTS users (
                id INT(11) AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        ";
        $db->query($query);
    }

    /**
     * Reverts the migrations.
     *
     * @param mysqli $db
     * @return void
     */
    public function down(mysqli $db): void
    {
        $query = "DROP TABLE IF EXISTS users";
        $db->query($query);
    }
};

After defining the migration structure, run the following command to apply the migrations and create the table in the database:

php line migrate

Creating a Model

To generate a model that interacts with the users table, run the following command:

php line make:model User

This will create a file in src/models, which will serve as the base for manipulating user data. The generated model will look like this:

namespace App\Models;

use Lithe\Database\Manager as DB;

class User
{
    // Model logic goes here
}

CRUD Methods in the Model

Add static methods to perform CRUD operations using mysqli.

Create

To insert a new user, add this method:

public static function create(array $data): bool
{
    $stmt = DB::connection()->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->bind_param('ss', $data['name'], $data['email']);
    return $stmt->execute();
}

Read

To retrieve users, implement the following methods:

public static function all(): array
{
    return DB::connection()->query("SELECT * FROM users")->fetch_all(MYSQLI_ASSOC);
}

public static function find(int $id): ?array
{
    $stmt = DB::connection()->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->bind_param('i', $id);
    $stmt->execute();
    return $stmt->get_result()->fetch_assoc() ?: null;
}

Update

To update an existing user:

public static function update(int $id, array $data): bool
{
    $stmt = DB::connection()->prepare("UPDATE users SET name = ?, email = ? WHERE id = ?");
    $stmt->bind_param('ssi', $data['name'], $data['email'], $id);
    return $stmt->execute();
}

Delete

And to delete a user:

public static function delete(int $id): bool
{
    $stmt = DB::connection()->prepare("DELETE FROM users WHERE id = ?");
    $stmt->bind_param('i', $id);
    return $stmt->execute();
}

Basic Controllers

To quickly generate a new controller, run the line make:controller command. All generated controllers are stored in the src/http/controllers directory:

php line make:controller UserController

A controller can have any number of public methods that will respond to HTTP requests. Here’s a basic implementation for the users table:

namespace App\Http\Controllers;

use App\Models\User;
use Lithe\Http\Request;
use Lithe\Http\Response;

class UserController
{
    /**
     * Displays the profile of a specific user.
     */
    public function show(Request $req, Response $res)
    {
        $id = $req->param('id');

        return $res->view('user.profile', [
            'user' => User::find($id)
        ]);
    }

    /**
     * Creates a new user.
     */
    public function store(Request $req, Response $res)
    {
        $data = (array) $req->body(); // Get all data from the request
        User::create($data);

        return $res->redirect('/users'); // Redirects to the list of users
    }

    /**
     * Updates an existing user.
     */
    public function update(Request $req, Response $res)
    {
        $id = $req->param('id');
        $data = (array) $req->body(); // Get all data from the request
        User::update($id, $data);

        return $res->redirect('/users'); // Redirects to the list of users
    }

    /**
     * Deletes a user.
     */
    public function delete(Request $req, Response $res)
    {
        $id = $req->param('id');
        User::delete($id);

        return $res->redirect('/users'); // Redirects to the list of users
    }
}

Adding Views

To display user information, create view files in the src/views directory. An example file could be user/profile.php, where you will build the user data display interface.

Starting the Application

To begin developing with Lithe, you need to create a new instance of the Lithe\App class in the src/App.php file and define the routes.

use App\Http\Controllers\UserController;

$app = new \Lithe\App;

$app->get('/users/:id', [UserController::class, 'show']);
$app->post('/users', [UserController::class, 'store']);
$app->put('/users/:id', [UserController::class, 'update']);
$app->delete('/users/:id', [UserController::class, 'delete']);

Running the Application

After defining the routes, it is essential to call the listen() method to start the server. You can do this in the same src/App.php file:

$app->listen();

And to run the application, use the command:

php line serve

This initializes a local server that you can access at http://localhost:8000.

Now that your CRUD application is configured and running, you will be able to access the user routes directly in your browser. To view the list of users or interact with the functions you implemented, simply navigate to:

http://localhost:8000/users

This route will allow you to view all registered users and perform operations like creating, updating, or deleting records as per the functionalities you implemented in the controllers.

Conclusion

With these steps, you now have a functional CRUD application using PHP, MySQL, and the Lithe framework. You can expand the application by adding more features, such as validation and authentication, depending on your project needs.

Continue exploring the capabilities of Lithe to improve your application. For more information, visit the official Lithe documentation. Happy coding!