Getting Started with Medoo – Examples of Use

07:33 php frameworks

In this article I’m going to walk you through Medoo, a lightweight database abstraction library for PHP. Its main features include:

Support for multiple databases – it supports MySQL, MariaDB, Sybase, MS SQL, PostgreSQL, and Oracle.
Secure – prevents SQL injection, it uses PDO.
Easy to use – its API is very intuitive.

While Medoo is nothing revolutionary, and the fact that it sports a very small filesize matters little to few, it’s still an interesting project that went from being outright dismissed to vaguely accepted, as evident in these threads. It’s on its way up, and that’s our reason for taking a look at it.

Installation

Even though the website recommends installing it by downloading a file and including it in your project, you should use Composer to do so.

Connecting to the Database

Creating a new instance of Medoo requires an array to be passed in as an argument. The array should contain the following items:

database_type – the type of database you want to connect to. This can either be one of the following: mysql, mariadb, pgsql, sybase, oracle, mssql, sqlite.
database_name – the name of the database.
server – the name of the server or IP address.
username – the username of the user used for logging in to the database.
password – the password of the user.

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

The default port used by medoo is port 3306. If your database server uses something different, you can also pass in the port and assign the correct port as the value.

Selecting Data

The first thing we’re going to do is select data from the database. You can download the example database here if you want to follow along.
You can select data from a specific table using the select method. This takes up the following arguments:

name of table
join condition (optional)
fields to select
where condition (optional)

As a primer, let’s select some trainers from the database:

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

Medoo returns the results as an array:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Brock
            [pokemon_count] => 7
            [region] => Kanto
        )

    [1] => Array
        (
            [id] => 2
            [name] => Marshal
            [pokemon_count] => 8
            [region] => Unova
        )
...

The query above selects all the trainers. What if we only want to select trainers from a specific region? We can do that by specifying the where condition:

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

The query above only returns the trainers from the Kanto region.
What if we only want to select the trainers who have defeated a specific number of trainers from a specific region? Here’s how we do that:

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

All you have to remember when using the where condition is to specify an array of fields with their corresponding condition as a value for the keyword that you want to use. In this case we want the condition for both to return true so we’re using the AND keyword.
If you need to use a relational operator other than =, you need to specify it after the name of the field and wrap it in brackets []. Here are some examples:

Select trainers who defeated more than 150 opponents:

'trainers_defeated[>]' => 150

Select trainers whose badges_count is not equal to zero:

'badges_count[!]' => 0

Select trainers who defeated trainers within the range of 100 to 300:

'trainers_defeated[<>]' => array(100, 300)

Select trainers whose badges_count is less than 8:

'badges_count[<]' => 8

Going back, what if we want to select the trainers who are from either ‘Kanto’ or ‘Unova’ and have a badges_count of 5 and above or trainers_defeated of 20 and above? Here’s how we do it:

$trainers = $db->select(
    'trainers',
    array('id', 'name', 'trainers_defeated', 'badges_count', 'region'),
    array('AND' => array(
        'region' => array('Kanto', 'Unova'),
        'OR' => array('badges_count[>=]' => 5, 'trainers_defeated[>=]' => 20)
    ))
);

All we had to do was to wrap our conditions inside the AND keyword. For the first condition we passed in an array. In this case, it’ s the name of regions that we want to select. In the second condition, we had to wrap it in OR, this means that the conditions inside it will return results for either of them.
Now we want to know which trainer has the highest number of Pokemon caught. We can do that by using the get method. Unlike the select method, this will only return a single row from the table that we are fetching from.

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

You can also use ORDER with the select method. For example, we want to select all the trainers and order them by the number of Pokemon they caught.

$trainers_most_caught = $db->select(
    'trainers', 
    array('name', 'pokemon_count', 'trainers_defeated'), 
    array('ORDER' => 'pokemon_count DESC')
);

We can also order by multiple fields. Instead of passing in a string for the value of the ORDER, we pass in an array:

$trainers_most_caught = $db->select(
    'trainers', 
    array('name', 'pokemon_count', 'trainers_defeated'), 
    array('ORDER' => array('pokemon_count DESC', 'trainers_defeated DESC'))
);

What if we want to know how many trainers are in each region? Medoo doesn’t have that kind of functionality built into it yet, so we’ll have to use a raw query:

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

Behind the scenes, Medoo uses PDO’s fetch method. This means it also uses the default fetch style used by PDO’s fetch method. So we have to pass in the fetch style as an argument to the fetchAll method. In this case its PDO::FETCH_ASSOC, this means that it will only return the field names and their value. If we leave it blank, it will return both the associative and indexed columns. Here’s the result that we get when we use PDO::FETCH_ASSOC as the fetch style:

Array
(
    [0] => Array
        (
            [trainers] => 2
            [region] => Hoenn
        )

    [1] => Array
        (
            [trainers] => 4
            [region] => Johto
        )

    [2] => Array
        (
            [trainers] => 2
            [region] => Kalos
        )

    [3] => Array
        (
            [trainers] => 1
            [region] => Kanto
        )

    [4] => Array
        (
            [trainers] => 3
            [region] => Unova
        )

)

If we leave it blank, the results will look like this:

Array
(
    [0] => Array
        (
            [trainers] => 2
            [0] => 2
            [region] => Hoenn
            [1] => Hoenn
        )

    [1] => Array
        (
            [trainers] => 4
            [0] => 4
            [region] => Johto
            [1] => Johto
        )

    [2] => Array
        (
            [trainers] => 2
            [0] => 2
            [region] => Kalos
            [1] => Kalos
        )

    [3] => Array
        (
            [trainers] => 1
            [0] => 1
            [region] => Kanto
            [1] => Kanto
        )

    [4] => Array
        (
            [trainers] => 3
            [0] => 3
            [region] => Unova
            [1] => Unova
        )

)

Finally, let’s touch on joins. We can use the following syntax for joins:

> – left join
< – right join
>< – inner join
<> – full join

Here’s an example of using an inner join to select all pokemon and their corresponding type names:

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

Just like with the regular select method, the first argument is the name of the table. In this case its the primary table we want to select data from. The second argument is an array containing the join condition. We supply the join type and the name of table to join as the key. And the value is an array with the field on the primary table as the key and the field on the secondary table as the value. The third argument is the array of fields that you want to return. Its good practice to always specify the name of the table before the field name. Currently medoo doesn’t support table aliases so we’ll have to use the full name of the table for now. If the name of the fields that you want to select are the same, you need to use a column alias. If you don’t, only the last field with the same name that you specified will get selected. In our example above we specified an alias by using this syntax:

table_name.field_name(alias)

Inserting Data

Next, we add some data into the pokemon table. For that, were going to use the Pokemon API as our data source.
First install guzzle with composer require guzzlehttp/guzzle:~5.0
Create a new instance of the guzzle client:

use GuzzleHttp\Client;

$client = new Client();

Next, we select all the trainers and the Pokemon types from the database. We will be using these later on when we insert the data into the pokemon table.

$trainers = $db->select('trainers', array('id', 'pokemon_count'));

$types = $db->select('types', array('id', 'name'));

Next, we make a request to the pokedex resource in the Pokemon API. This returns a JSON string, so we use guzzle’s json method to convert it to an array. The pokedex resource returns an array of all pokemon.

$pokedex_response = $client->get('http://pokeapi.co/api/v1/pokedex/1');
$pokedex_data = $pokedex_response->json();

$pokemon = $pokedex_data['pokemon'];
$total_pokemon = count($pokemon) - 1; //were zero-indexed

Next, we loop through all the trainers, get the number of Pokemon they caught and then create a for loop based on that number. The pokedex resource returns the name of the pokemon and a resource URI in which we can get further information about it. We also need to get the primary type of the pokemon and a list of moves that it can do so we also make a request to the pokemon resource. Once we get the data back, we then need to get the id of the pokemon type. We already have a table that has a list of all the pokemon types so we just determine the id by looping through it, and if the type name returned from the API is the same as one of the type names from the database, we just get its id. After that, we give the pokemon a random level by using mt_rand. Next, we call Medoo’s insert method to insert the Pokemon data in the database. This takes the name of the table as the first argument, then the array of data as the second argument. Finally, we assign some moves to each of the Pokemon. When you call the insert method, it returns the last insert id. We use this last insert id to determine the id of the pokemon that was assigned by the database, and then insert it to the pokemon_moves table along with the name of the move.

foreach($trainers as $trainer){
    $trainer_id = $trainer['id'];
    $pokemon_count = $trainer['pokemon_count'];

    for($x = 0; $x < $pokemon_count; $x++){
        
        $pokemon_id = mt_rand(0, $total_pokemon);
        $pokemon_name = $pokemon[$pokemon_id]['name'];
        $pokemon_resource = $pokemon[$pokemon_id]['resource_uri'];

        $pokemon_response = $client->get('http://pokeapi.co/' . $pokemon_resource);
        $pokemon_data = $pokemon_response->json();

        $pokemon_types = $pokemon_data['types'];

        //pokemon types in the database starts with a capital letter
        $type_name = ucfirst($pokemon_types[0]['name']); 

        $type_id = null;
        foreach($types as $t){ //determine type id
            if($t['name'] == $type_name){
                $type_id = $t['id'];
            }
        }

        $level = mt_rand(1, 100); //give a random level between 1 and 100

        $pokemon_db_id = $db->insert(
            'pokemon',
            array(
                'name' => $pokemon_name,
                'trainer_id' => $trainer_id,
                'type_id' => $type_id,
                'level' => $level 
            )
        );

        //assign some moves
        $pokemon_moves = $pokemon_data['moves'];
        if(!empty($pokemon_moves)){
            $move_count = count($pokemon_moves) - 1;
            $move_limit = 4; //each pokemon can only have 4 moves
            for($z = 0; $z < $move_limit; $z++){
                $move_id = mt_rand(0, $move_count); 
                $move_name = $pokemon_moves[$move_id]['name'];
                
                $db->insert(
                    'pokemon_moves', 
                    array(
                        'pokemon_id' => $pokemon_db_id, 
                        'move_name' => $move_name
                    )
                );
            }
        }
         
    }
}

After running the script above, we should now have some Pokemon along with their moves in our database.
Let’s say trainer Roxie caught 3 new Pokemon: Drapion, Toxicroak and Crobat. How can we insert them all at once into our pokemon table? The insert method also supports multi-inserts, so we can just supply an array containing all of the rows of data that we want to insert.
First, we get the trainer id from the trainers table:

$trainer_id = $db->get('trainers', 'id',  array('name' => 'Roxie'));

Let’s assume that those 3 Pokemon have the same type so we go ahead and get the type id from the database:

$type_id = $db->get('types', 'id', array('name' => 'Poison'));

The pokemon API doesn’t really have a search method, so all we can do is access the pokedex resource directly from the browser:

http://pokeapi.co/api/v1/pokedex/1/

And then look for the resource_uri that we want to get. I already did it so you don’t have to. The ids that we need are: 452, 454 and 169. We then loop through those and get all the necessary data. This time, instead of doing an insert call on every iteration of the loop, we store it in an array. We then call the insert method once and supply the array in which we stored the pokemon data.

$ids = array(452, 454, 169);

$pokemon_caught = array();
foreach($ids as $id){

    $response = $client->get('http://pokeapi.co/api/v1/pokemon/' . $id);
    $data = $response->json();

    $name = $data['name'];

    $pokemon_caught[] = array(
        'name' => $name,
        'trainer_id' => $trainer_id,
        'type_id' => $type_id,
        'level' => mt_rand(1, 100)
    );

}

$db->insert('pokemon', $pokemon_caught);

Updating Data

Now Roxie has 3 more Pokemon, but her trainer data hasn’t been updated yet. We need to add 3 to her current pokemon_count. To do that we call the update method. This takes up the following arguments:

name of table
data
where condition

Here’s how we do an update:

$db->update(
    'trainers', 
    array('pokemon_count[+]' => 3), 
    array('id' => $trainer_id)
);

See what we did there? Medoo comes with a nice utility wherein you can do the following mathematical operations on a specific field that you’re updating:

+ – add a specific value to the current value.
- – subtract a specific value to the current value.
* – multiply a specific value to the current value.
/ – divide a specific value to the current value.

Deleting Data

Now we want to release Drapion because it sucks. We can delete him from the pokemon table using the delete method. This takes up the name of the table as its first argument and an array of conditions as the second argument.

$db->delete('pokemon', array('name' => 'Drapion'));

We can also delete based on 2 conditions. Here we want to delete all Pokemon which are of ‘Normal’ type and are below level 60:

$type_id = $db->get('types', 'id', array('name' => 'Normal'));
$db->delete(
    'pokemon', 
    array('AND' => array('level[<]' => 60, 'type_id' => $type_id))
);

Note that calling delete doesn’t return the id of the deleted row. This means you’ll have to find a way to get it on your own if you want to use that id for something.

Aggregate Functions

Medoo also comes with some aggregate functions.
For example, we want to get the total number of trainers from the trainers table. For that, we use the count method:

$total_trainers = $db->count('trainers');

If we want to know the trainer with the most/least number of Pokemon, there’s also a function for that. For getting the most, we use the max method. This takes up the name of the table and the field you want to use:

$db->max('trainers', 'pokemon_count');

For getting the least, we use the min method:

$db->min('trainers', 'pokemon_count');

We can also use the avg method, if we want to know the average number of pokemon that each trainer in the trainers table have:

$db->avg('trainers', 'pokemon_count');

We can also get the total number of pokemon that all of the trainers have by using the sum method:

$db->sum('trainers', 'pokemon_count');

Note that with the count, min, max, and sum method we can also specify some additional conditions. We supply those as the last argument. For example, we only want to know the maximum number of pokemon that a trainer has in the ‘Hoenn’ region:

$db->max('trainers', 'pokemon_count', array('region' => 'Hoenn'));

Debugging

Medoo also provides utilities for checking errors and debugging. Note that medoo doesn’t return any errors when you try to do something that cannot yield a successful result, that’s why sometimes you have to check for it explicitly.
For example, we made a typo for the trainers table. Instead of trainers we typed in trainerw:

$db->update(
    'trainerw', 
    array('pokemon_count[-]' => 1), 
    array('id' => 99)
);

Executing the code above won’t make medoo complain about it. It just fails silently. To check for the error we need to call the error method right after the offending code:

$db->error();

This returns an array which look like the following:

Array ( 
    [0] => 42S02 
    [1] => 1146 
    [2] => Table 'pokemon.trainerw' doesn't exist 
)

The first item is the error code returned by MySQL. You can see a list of error codes on this page. The second item is the SQL state. And the third item is a human readable description of the error.
If the error is an error with the arguments passed to the specific method that you’re using, medoo returns an error so you won’t really need to check for those if it happens.
If there aren’t any errors returned when you call the error method, then it might not be an error. Instead, it can be that the query generated by medoo doesn’t do what you want it to do. In those cases you need to use the last_query() method. As the name suggests, calling this method will return the last query executed by medoo. Let’s take a look at some examples:

Decrease the pokemon_count by 1 for trainer with the id of 1:

$db->update(
    'trainers', 
    array('pokemon_count[-]' => 1), 
    array('id' => 1)
);

echo $db->last_query();

/*
returns: 
UPDATE "trainers" SET "pokemon_count" = "pokemon_count" - 1 WHERE "id" = 1
*/

Select the pokemon with the name ‘virizion’:

$db->get(
    'pokemon', 
    array('name', 'level', 'type'), array('name' => 'virizion')
);

echo $db->last_query();
/*
returns:
SELECT "name","level","type" FROM "pokemon" WHERE "name" = 'virizion' LIMIT 1
*/

Select all pokemon with their corresponding type:

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

echo $db->last_query();
/*
returns:
SELECT "pokemon"."name","types"."name" AS "type" FROM "pokemon" INNER JOIN "types" ON "pokemon"."type_id" = "types"."id"
*/

Conclusion

That’s it! In this tutorial we have learned about Medoo, a lightweight and easy to use database abstraction library for PHP. We learned how to select, update, delete, and insert data into the database.

Tutorial Lancer

Popular Posts

Labels