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;
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'];
$type_name = ucfirst($pokemon_types[0]['name']);
$type_id = null;
foreach($types as $t){
if($t['name'] == $type_name){
$type_id = $t['id'];
}
}
$level = mt_rand(1, 100);
$pokemon_db_id = $db->insert(
'pokemon',
array(
'name' => $pokemon_name,
'trainer_id' => $trainer_id,
'type_id' => $type_id,
'level' => $level
)
);
$pokemon_moves = $pokemon_data['moves'];
if(!empty($pokemon_moves)){
$move_count = count($pokemon_moves) - 1;
$move_limit = 4;
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();
- Select the pokemon with the name ‘virizion’:
$db->get(
'pokemon',
array('name', 'level', 'type'), array('name' => 'virizion')
);
echo $db->last_query();
- 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();
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.
