Lithe

Posted on Oct 15 • Edited on Nov 4

Creating a CRUD Application with PHP, MySQL, and Lithe

#php #mysql #lithe #webdev

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!

Top comments (4)

Nikita N • Oct 17

Thank you for this tutorial. It's refreshing to see a new option besides Laravel and Symfony.

How do you think is it a good practice to have a global variable DB_CONNECTION in the model?

namespace App\Models;

class User
{
    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();
    }
}

Lithe • Oct 17 • Edited

Thank you for your feedback! Regarding the use of a global variable like DB_CONNECTION in the model, it's not considered a best practice, as it can hinder maintainability and testability. In the upcoming 1.1 version of Lithe, we plan to encapsulate this logic within a new method called connection in the Lithe\Database\Manager class. This will allow models to obtain the connection in a more structured and flexible way, as shown in the example below:

namespace App\Models;

use Lithe\Database\Manager as DB;

class User
{
    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();
    }
}

This change will improve the organization and clarity of the code!

Nikita N • Oct 17

Thank you for your reply. It's great to hear that you're committed to following best practices.

It seems that in your example we can see another global variable - DB.
Maybe it would be possible to inject $db dependency in the model class?

But in this case we can't use static methods in the model:

class User
{
    public function __construct(
        private SomeDbInterface $db
    )

    public function create(array data): bool
    {
        $this->db->prepare("...")
        return $this->execute();
    }
}

Lithe • Oct 17 • Edited

Lithe can connect to any ORM or database driver, so separating this responsibility to the Manager is a good idea. This allows models to focus on business logic while the Manager handles connections. Thus, we ensure better separation of responsibilities, making maintenance and flexibility in the code easier.

Your approach will still be accepted and added. Feel free to contribute to Lithe at github.com/lithephp.

DEV Community