Getting Started with Medoo - Examples of Use

This article explores Medoo, a lightweight PHP database abstraction library. Its key features include multi-database support (MySQL, MariaDB, Sybase, MS SQL, PostgreSQL, and Oracle), built-in SQL injection prevention via PDO, and an intuitive API. While not revolutionary, its simplicity and ease of use make it a compelling option.

Key Advantages:

• Lightweight and efficient PHP database interaction.
• Supports diverse database systems.
• User-friendly API for simplified database operations.
• Secure against SQL injection vulnerabilities.

Installation (Recommended):

Use Composer for seamless installation and updates: composer require catfan/medoo

Database Connection:

Instantiate Medoo by providing a configuration array:

$db = new medoo([
    'database_type' => 'mysql',
    'database_name' => 'pokemon',
    'server' => 'localhost',
    'username' => 'ash_ketchum',
    'password' => 'pikachu'
]);

Remember to adjust the port if necessary (default is 3306).

Data Retrieval (SELECT):

Medoo's select method facilitates data retrieval. Arguments include the table name, optional join conditions, fields to select, and optional where clauses.

Example: Selecting Kanto trainers:

$trainers = $db->select('trainers', ['id', 'name', 'pokemon_count', 'region'], ['region' => 'Kanto']);

Complex conditions are easily implemented using AND, <code>OR, and relational operators ([>, <code>[, <code>[>=], <code>[, <code>[!=], <code>[]):

$trainers = $db->select('trainers', ['id', 'name', 'trainers_defeated', 'region'], ['AND' => ['trainers_defeated[>]' => 150, 'region' => 'Johto']]);

The get method retrieves a single row:

$most_caught = $db->get('trainers', ['name', 'pokemon_count'], ['ORDER' => 'pokemon_count DESC']);

Raw SQL queries are possible using query and fetchAll(PDO::FETCH_ASSOC):

$trainer_count_per_region = $db->query('SELECT COUNT(name) as trainers, region FROM trainers GROUP BY region')->fetchAll(PDO::FETCH_ASSOC);

Joins are supported using [>, <code>, <code>:

$pokemon_types = $db->select('pokemon', ['[>'=>['type_id'=>'id']], ['pokemon.name', 'types.name(type)']);

Data Manipulation:

• INSERT: The insert method adds new records. It supports single and multiple inserts.

• UPDATE: The update method modifies existing records. It allows for simple updates and mathematical operations ([ ], <code>[-], <code>[*], <code>[/]).

• DELETE: The delete method removes records based on specified conditions.

Aggregate Functions:

Medoo provides count, <code>max, <code>min, <code>avg, and sum for aggregate operations. Conditions can be added as a final argument.

Debugging:

Use error() to check for errors and last_query() to examine the last executed SQL statement.

Conclusion:

Medoo offers a streamlined approach to PHP database interaction, balancing simplicity with functionality. Its lightweight nature and straightforward API make it an attractive choice for many projects. Further details are available in the official Medoo documentation.

The above is the detailed content of Getting Started with Medoo - Examples of Use. For more information, please follow other related articles on the PHP Chinese website!