How To Use Medoo PHP Database Framework

http://medoo.in/

Medoo is the lightest PHP database framework to accelerate development.
Version 1.1.3
Compatible with PHP 7.0

Lightweight - 20KB with only one file.

Easy - Extremely easy to learn and use, friendly construction.

Powerful - Support various common and complex SQL queries.

Compatible - Support various SQL database, including MySQL, MSSQL, SQLite, MariaDB, Sybase, Oracle, PostgreSQL and more.

Security - Prevent SQL injection.

Free - Under MIT license, you can use it anywhere if you want.

$database = new medoo([
'database_type' => 'mysql',
'database_name' => 'mydb',
'server' => 'host.website.com',
'username' => 'user',
'password' => 'password',
'charset' => 'utf8'
]);

Insert Into Database

//table - data array
$database->insert("usertable", [
'Email' => $email,
'Name' => $fullname,
'Password' => $password
]);

Update Database

//table - data array - entry
$database->update("usertable", [
'Name' => 'New Name' ], [
'ID' => 1
]);

Read from Database

//get database data from ID 1 and put it into a variable array
$profile = $database->get("usertable", [
'Email',
'Name',
'Password'], [
'ID' => 1
]);

//reading an exact entry (AND ... OR):
$profile = $database->get("usertable", [
'Email',
'Name',
'Password'], [
AND => [ 'ID' => 1, 'Username' => 'User' ]
]);

Delete Entry in Database

//any identifying marker can be used to find and delete the entry -- make sure it is unique or all records containing the identifying marker will be deleted
$database->delete("usertable", [
'Name' => 'The Name'
]);

Counting in Database

//get the count of how many entries in the table
$count = $database->count("usertable");

//get the count of how many are female in the table
$count = $database->count("usertable", [
"gender" => "female"
]);

Max in Database

//grab the highest number
$max = $database->max("usertable", "age", [
"gender" => "female"
]);

echo "The age of oldest female user is " . $max;

Min in Database

//grab the lowest number
$min = $database->min("usertable", "age", [
"gender" => "male"
]);

echo "The age of youngest male user is " . $min;

Average in Database

//get the average of all the numbers
$average = $database->avg("usertable", "age", [
"gender" => "male"
]);

echo "The average age of male user is " . $average;

Sum in Database

//get the total number of a calculated column
$total = $database->sum("usertable", "money");
echo "We have $" . $total;

Has - Exist in Database

if ($database->has("usertable", [
"AND" => [
"OR" => [
"username" => "foo",
"email" => "foo"
],
"password" => "12345"
]
]))
{
echo "Password is correct.";
}
else
{
echo "Password error.";
}

Replace in Database

$database->replace("usertable", "type", "user", "new_user", [
"userid[>]" => 1000
]);

$database->replace("usertable", "type", [
"user" => "new_user",
"business" => "new_business"
], [
"userid[>]" => 1000
]);

$database->replace("usertable", [
"type" => [
"user" => "new_user",
"business" => "new_business"
],
"group" => [
"groupA" => "groupB"
]
], [
"userid[>]" => 1000
]);

Select in Database

$datas = $database->select("usertable", [
"username",
"email"
], [
"userid[>]" => 100
]);

// $datas = array(
// 	[0] => array(
// 		"username" => "foo",
// 		"email" => "[email protected]"
// 	),
// 	[1] => array(
// 		"username" => "cat",
// 		"email" => "[email protected]"
// 	)
// )

foreach($datas as $data)
{
echo "username:" . $data. " - email:" . $data . "
";
}

// Select all columns
$datas = $database->select("usertable", "*");

// Select a column
$datas = $database->select("usertable", "username");

// $datas = array(
// 	[0] => "foo",
// 	[1] => "cat"
// )

Query Database

$data = $database->query("SELECT email FROM usertable")->fetchAll();
print_r($data);

Quote in Database

$data = "Medoo";

echo "We love " . $data; // We love Medoo

echo "We love " . $database->quote($data); // We love 'Medoo'

Database Information

print_r($database->info());

Get Last Query from Database Log

$database->select("usertable", [
"username",
"email"
], [
"userid[<]"=>20
]);

$database->insert("usertable", [
"username" => "foo",
"email" => "[email protected]"
]);

echo $database->last_query();
// INSERT INTO "usertable" ("username", "email") VALUES ("foo", '[email protected]")

Get Database Log

$database->select("usertable", [
"username",
"email"
], [
"userid[<]"=>20
]);

$database->insert("usertable", [
"username" => "foo",
"email" => "[email protected]"
]);

var_dump( $database->log() );
// array(2) {
//	[0]=> string(62) "SELECT "username","email" FROM "usertable" WHERE "userid" < 20"
//	[1]=> string(74) "INSERT INTO "usertable" ("username", "email") VALUES ("foo", '[email protected]")"
// }

Error in Database

$database->select("usertable", [
"username",
"email"
], [
"userid[<]"=>20
]);

var_dump($database->error());

Debugging Database

$database->debug()->select("usertable", [
"username",
"email"
], [
"userid[<]"=>20
]);

// Will output:
// SELECT "username","email" FROM "usertable" WHERE "userid" < 20

// [Multiple situation]
// Output nothing
$database->insert("usertable", [
"username" => "foo",
"email" => "[email protected]"
]);

// Will output the generated query
$postid = $database->debug()->get("post", "postid", ["username" => "foo"]);

// Be careful, this query will be executed.
$database->update("usertable", [
"level[+]" => 5,
"post" => $postid
], [
"username" => "foo"
]);

WHERE

Basic

$database->select("usertable", "username", [
"email" => "[email protected]"
]);
// WHERE email = '[email protected]'

$database->select("usertable", "username", [
"userid" => 200
]);
// WHERE userid = 200

$database->select("usertable", "username", [
"userid[>]" => 200
]);
// WHERE userid > 200

$database->select("usertable", "username", [
"userid[>=]" => 200
]);
// WHERE userid >= 200

$database->select("usertable", "username", [
"userid[!]" => 200
]);
// WHERE userid != 200

$database->select("usertable", "username", [
"age" =>
]);
// WHERE age BETWEEN 200 AND 500

$database->select("usertable", "username", [
"age" =>
]);
// WHERE age NOT BETWEEN 200 AND 500

// and is also available for datetime
$database->select("usertable", "username", [
"birthday" =>
]);
//WHERE "create_date" BETWEEN '2015-01-01' AND '2015-05-01' (now)

// You can use not only single string or number value, but also array
$database->select("usertable", "username", [
"OR" => [
"userid" => [2, 123, 234, 54],
"email" => ["[email protected]", "[email protected]", "[email protected]"]
]
]);
// WHERE
// userid IN (2,123,234,54) OR
// email IN ("[email protected]','[email protected]','[email protected]")

// [Negative condition]
$database->select("usertable", "username", [
"AND" => [
"username[!]" => "foo",
"userid[!]" => 1024,
"email" =>,
"city[!]" => null,
"promoted[!]" => true
]
]);
// WHERE
// `username` != 'foo' AND
// `userid` != 1024 AND
// `email` NOT IN ("[email protected]','[email protected]','[email protected]") AND
// `city` IS NOT NULL
// `promoted` != 1

// Or fetched from select() or get() function
$database->select("usertable", "username", [
"userid" => $database->select("post", "userid", ["comments[>]" => 40])
]);
// WHERE userid IN (2, 51, 321, 3431)

Relativity Condition

// [Basic]
$database->select("usertable", "username", [
"AND" => [
"userid[>]" => 200,
"age" =>,
"gender" => "female"
]
]);
// WHERE userid > 200 AND age BETWEEN 18 AND 25 AND gender = 'female'

$database->select("usertable", "username", [
"OR" => [
"userid[>]" => 200,
"age" =>,
"gender" => "female"
]
]);
// WHERE userid > 200 OR age BETWEEN 18 AND 25 OR gender = 'female'

// [Compound]
$database->has("usertable", [
"AND" => [
"OR" => [
"username" => "foo",
"email" => "[email protected]"
],
"password" => "12345"
]
]);
// WHERE (username = 'foo' OR email = '[email protected]") AND password = '12345'

// [IMPORTANT]
// Because Medoo is using array data construction to describe relativity condition,
// array with duplicated key will be overwritten.
//
// This will be error:
$database->select("usertable", '*", [
"AND" => [
"OR" => [
"username" => "foo",
"email" => "[email protected]"
],
"OR" => [
"username" => "bar",
"email" => "[email protected]"
]
]
]);
// [X] SELECT * FROM "usertable" WHERE ("username" = 'bar' OR "email" = '[email protected]")

// To correct that, just assign a comment for each AND and OR key name. The comment content can be everything.
$database->select("usertable", '*", [
"AND #Actually, this comment feature can be used on every AND and OR relativity condition" => [
"OR #the first condition" => [
"username" => "foo",
"email" => "[email protected]"
],
"OR #the second condition" => [
"username" => "bar",
"email" => "[email protected]"
]
]
]);
// SELECT * FROM "usertable"
// WHERE (
// 	(
// 		"username" = 'foo' OR "email" = '[email protected]'
// 	)
// 	AND
// 	(
// 		"username" = 'bar' OR "email" = '[email protected]'
// 	)
// )

LIKE Condition

// By default, the keyword will be quoted with % front and end to match the whole word.
$database->select("person", "id", [
"city[~]" => "lon"
]);

WHERE "city" LIKE '%lon%'

// Array support
$database->select("person", "id", [
"city" =>
]);

WHERE "city" LIKE '%lon%' OR "city" LIKE '%foo%' OR "city" LIKE '%bar%'

// Negative condition [!~]
$database->select("person", "id", [
"city[!~]" => "lon"
]);

WHERE "city" NOT LIKE '%lon%'

// SQL LIKE Wildcard
// You can use SQL wildcard to match more complex situation
$database->select("person", "id", [
"city[~]" => "%stan" // Kazakhstan,  Uzbekistan, Türkmenistan
]);

$database->select("person", "id", [
"city[~]" => "Londo_" // London, Londox, Londos...
]);

$database->select("person", "id", [
"name" => "at" // Bat, Cat, Rat
]);

$database->select("person", "id", [
"name" => "at" // Eat, Fat, Hat...
]);

ORDER Condition

$database->select("account", "user_id", [

// Single condition
"ORDER" => "user_id",

// Multiple condition
"ORDER" => [
// Order by column with sorting by customized order.
"user_id" => [43, 12, 57, 98, 144, 1],

// Order by column
"register_date",

// Order by column with descending sorting
"profile_id" => "DESC",

// Order by column with ascending sorting
"date" => "ASC"
]
]);

Full Text Searching

// [MATCH]
$database->select("post_table", "post_id", [
"MATCH" => [
"columns" => ["content", "title"],
"keyword" => "foo"
]
]);
// WHERE MATCH (content, title) AGAINST ("foo")

SQL Functions

$data = $database->select("account", [
'user_id',
'user_name'
], [
'#datetime' => 'NOW()'
]);

// SELECT "user_id","user_name"
// FROM "account"
// WHERE "datetime" = NOW()

// [IMPORTANT] Keep in mind that, the value will not be quoted should be matched as XXX() uppercase.
// The following sample will be failed.
$database->select("account", [
'user_id',
'user_name'
], [
'#datetime2' => 'now()',

'datetime3' => 'NOW()',

'#datetime4' => 'NOW'
]);

Additional Condition

$database->select("account", "user_id", [
"GROUP" => "type",

// Must have to use it with GROUP together
"HAVING" => [
"user_id[>]" => 500
],

// LIMIT => 20
"LIMIT" => [20, 100]
]);
//	SELECT user_id FROM account
//	GROUP BY type
//	HAVING user_id > 500
//	LIMIT 20,100

ADVANCED

PDO

Transaction

$database->pdo->beginTransaction();

$database->insert("usertable", [
"username" => "foo",
"email" => "[email protected]",
"age" => 25
]);

/* Commit the changes */
$database->pdo->commit();

/* Recognize mistake and roll back changes */
$database->pdo->rollBack();

Prepare

$calories = 150;
$colour = 'red';

$sth = $database->pdo->prepare("SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour");

$sth->bindParam(":calories", $calories, PDO::PARAM_INT);
$sth->bindParam(":colour", $colour, PDO::PARAM_STR, 12);

$sth->execute();

Action

Transaction

$database->action(function($database) {
$database->insert("usertable", [
"name" => "foo",
"email" => "[email protected]"
]);

$database->delete("usertable", [
"userid" => 2312
]);

// If you want to  find something wrong, just return false to rollback the whole transaction.
if ($database->has("post", ["userid" => 2312]))
{
return false;
}
});

$log_date_range = date("Y-m-d H:i:s", strtotime("-7 days"));

$thelogs = $database->select("logs", array(
"date",
"category",
"description",
"class",
"ID"), array(
"date[>]" => $log_date_range
));

$database->query("ALTER TABLE `wpbbubble` DROP `id`");
$database->query("ALTER TABLE `wpbbubble` AUTO_INCREMENT = 1");
$database->query("ALTER TABLE `wpbbubble` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;");