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.
![medoo-logo-256](https://uploads.sitepoint.com/wp-content/uploads/2014/11/1417350467medoo-logo-256.png)
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. We also learned some debugging techniques that we can use if something goes wrong. If you want to learn more, check out meedo’s official documentation.
Are you using Medoo for anything? What do you think about the project? Let us know!
Frequently Asked Questions (FAQs) about Getting Started with Medoo
What is Medoo and why should I use it?
Medoo is a lightweight PHP database framework that simplifies the process of working with databases. It supports a wide range of databases including MySQL, MSSQL, SQLite, and more. The main advantage of using Medoo is its simplicity and ease of use. It provides a straightforward API for executing common database operations such as select, insert, update, and delete. Moreover, it supports complex queries and transactions, making it a versatile tool for any PHP developer.
How do I install Medoo?
Installing Medoo is a straightforward process. You can download it directly from the official website or install it via Composer. To install via Composer, you need to run the command composer require catfan/medoo
. After installation, you can include it in your PHP script using the require
command.
How do I connect to a database using Medoo?
To connect to a database using Medoo, you need to create a new instance of the Medoo class and pass the database configuration as an array. The configuration array should include the database type, server name, database name, username, and password. Here’s an example:$database = new Medoo([
'database_type' => 'mysql',
'database_name' => 'name',
'server' => 'localhost',
'username' => 'your_username',
'password' => 'your_password'
]);
How do I execute a SELECT query using Medoo?
To execute a SELECT query, you can use the select
method of the Medoo class. The select
method takes three parameters: the table name, the columns to select, and the where condition. Here’s an example:$data = $database->select("table", [
"column1",
"column2"
], [
"column3" => "value"
]);
How do I handle errors in Medoo?
Medoo provides the error()
method to handle database errors. This method returns an array containing the SQLSTATE error code and the specific driver error code. Here’s how you can use it:$error = $database->error();
if ($error[2]) {
echo "Error: " . $error[2];
}
How do I use transactions in Medoo?
Medoo supports database transactions through the action
method. This method accepts a callback function where you can execute your database operations. If an exception is thrown within the callback, Medoo will automatically rollback the transaction. Here’s an example:$database->action(function($db) {
$db->insert("table1", ["column1" => "value1"]);
$db->insert("table2", ["column2" => "value2"]);
});
How do I update data using Medoo?
To update data, you can use the update
method of the Medoo class. This method takes three parameters: the table name, the data to update, and the where condition. Here’s an example:$database->update("table", [
"column1" => "new_value"
], [
"column2" => "value"
]);
How do I delete data using Medoo?
To delete data, you can use the delete
method of the Medoo class. This method takes two parameters: the table name and the where condition. Here’s an example:$database->delete("table", [
"column" => "value"
]);
How do I execute raw SQL queries in Medoo?
Medoo provides the query
method to execute raw SQL queries. This method accepts the SQL query as a string and returns the result. Here’s an example:$data = $database->query("SELECT * FROM table")->fetchAll();
How do I secure my database operations in Medoo?
Medoo automatically escapes all input data to prevent SQL injection attacks. However, it’s always a good practice to validate and sanitize user input before using it in your database operations.
Wern is a web developer from the Philippines. He loves building things for the web and sharing the things he has learned by writing in his blog. When he's not coding or learning something new, he enjoys watching anime and playing video games.