Get Started

Upgrade

Where Syntax

Query

Aggregation

Fetch

Transaction

Management

Advanced

Raw object

PDO object

Debug

Information

version: 2.1.4

Get Started

Using Medoo is extremely easy!

Requirement

PHP_PDO extension list

The following list indicates which PDO extension should be installed according to the type and version of the database or the system platform.

Name Driver
MySQL, MariaDB php_pdo_mysql
MSSQL php_pdo_sqlsrv / php_pdo_dblib
Oracle php_pdo_oci
SQLite php_pdo_sqlite
PostgreSQL php_pdo_pgsql
Sybase php_pdo_dblib

PHP PDO Driver Installation

Medoo requires PHP with PDO supported. If you didn't install it before, follow this step.

// Edit php.ini file and remove the ';' for the database extension you want to install.

// .dll is for Windows and .so for Linux/UNIX.

// From
;extension=php_pdo_mysql.dll
;extension=php_pdo_mysql.so

// To
extension=php_pdo_mysql.dll
extension=php_pdo_mysql.so

// Save it, and restart PHP or Apache/Nginx server.

// If PDO installed successfully, you can find it on phpinfo() output.

Or install via terminal. The PDO_XXX extension will be enabled and automatically configured.

$ sudo apt-get install php7.4-mysql

Composer Installation

If you know about composer, it is easy to install and manage the version dependence. Just use this command to add Medoo to composer.json. Or you can edit it depending on your requirement.

$ composer require catfan/medoo

And update the composer.

$ composer update

Download Installation

The simple way. Just download the medoo.php file and put it into the correct directory, and then require it.

require  'Medoo.php';

Configuration

Pass an array of configurations for initialization and start a database connection.

// Require Composer's autoloader.
require 'vendor/autoload.php';

// Using Medoo namespace.
use Medoo\Medoo;

$database = new Medoo([
	// [required]
	'type' => 'mysql',
	'host' => 'localhost',
	'database' => 'name',
	'username' => 'your_username',
	'password' => 'your_password',

	// [optional]
	'charset' => 'utf8mb4',
	'collation' => 'utf8mb4_general_ci',
	'port' => 3306,

	// [optional] Table prefix, all table names will be prefixed as PREFIX_table.
	'prefix' => 'PREFIX_',

	// [optional] Enable logging, it is disabled by default for better performance.
	'logging' => true,

	// [optional]
	// Error mode
	// Error handling strategies when error is occurred.
	// PDO::ERRMODE_SILENT (default) | PDO::ERRMODE_WARNING | PDO::ERRMODE_EXCEPTION
	// Read more from https://www.php.net/manual/en/pdo.error-handling.php.
	'error' => PDO::ERRMODE_SILENT,

	// [optional]
	// The driver_option for connection.
	// Read more from http://www.php.net/manual/en/pdo.setattribute.php.
	'option' => [
		PDO::ATTR_CASE => PDO::CASE_NATURAL
	],

	// [optional] Medoo will execute those commands after connected to the database.
	'command' => [
		'SET SQL_MODE=ANSI_QUOTES'
	]
]);

Customized DSN Connection

You can also use customized DSN to connect the database that Medoo didn't support by default, especially for some new database requiring special DSN parameters for database connection, or if you want to add more DSN parameters value for connection than original one.

The format of DSN connection string:
{driver}:{key}={value};{key}={value}
$database = new Medoo([
	'dsn' => [
		// The PDO driver name for DSN driver parameter.
		'driver' => 'mydb',
		// The parameters with key and value for DSN.
		'server' => '12.23.34.45',
		'port' => '8886'
	],
	// [optional] Medoo will have a different handle method according to different database types.
	'type' => 'mysql',

	'username' => 'your_username',
	'password' => 'your_password'
]);

// The final DSN connection string will be generated like this.
mydb:server=12.23.34.45;port=8886

PDO object initialization

You can also create Medoo bypassing initialized and connected PDO object.

$pdo = new PDO('mysql:dbname=test;host=127.0.0.1', 'user', 'password');

$database = new Medoo([
	// Initialized and connected PDO object.
	'pdo' => $pdo,

	// [optional] Medoo will have different handle method according to different database type.
	'type' => 'mysql'
]);

For MySQL

This option is for MySQL only.

$database = new Medoo([
	// [optional] MySQL socket (shouldn't be used with server and port).
	'socket' => '/tmp/mysql.sock',
]);

For MariaDB

MariaDB acts the same as MySQL. The database type will be changed to mysql automatically.

$database = new Medoo([
	'type' => 'mariadb',
	'host' => 'localhost',
	'database' => 'name',
	'username' => 'your_username',
	'password' => 'your_password',
]);

For MSSQL

Since Microsoft's latest version of MSSQL database, it is using pdo_sqlsrv PHP extension for both Windows and Linux/UNIX platforms. Medoo is using pdo_sqlsrv driver to connect MSSQL by default. You can check out their GitHub repo for more detail https://github.com/Microsoft/msphpsql. If you want to use pdo_dblib driver to connect old version database, you need to provide driver option for initialization.
$database = new Medoo([
	'type' => 'mssql',
	'host' => 'localhost',
	'database' => 'name',
	'username' => 'your_username',
	'password' => 'your_password',

	// [optional] The application name.
	'appname' => 'test',

	// [optional]
	// If you want to force Medoo to use dblib driver for connecting MSSQL database.
	// The default value is sqlsrv.
	'driver' => 'dblib'
]);

Medoo also supports those options for connecting MSSQL databases while using sqlsrv driver. You can check out more detail from https://docs.microsoft.com/en-us/sql/connect/php/connection-options?view=sql-server-2017.

$database = new Medoo([
	'type' => 'mssql',
	'host' => 'localhost',
	'database' => 'name',
	'username' => 'your_username',
	'password' => 'your_password',

	// [optional] MSSQL connection options.
	'application_intent' => 'ReadOnly',
	'attach_db_file_name' => './database.sql',
	'authentication' => 'SqlPassword',
	'column_encryption' => 'Enabled',
	'connection_pooling' => 1,
	'encrypt' => 1,
	'failover_partner' => 'MultiSubnetFailover',
	'key_store_authentication' => 'KeyVaultPassword',
	'key_store_principal_id' => 'AzureName',
	'key_store_secret' => 'AzurePass',
	'login_timeout' => '20',
	'multiple_active_result_sets' => 1,
	'multi_subnet_failover' => 'Yes',
	'scrollable' => 'buffered',
	'trace_file' => './path',
	'trace_on' => 1,
	'transaction_isolation' => PDO::SQLSRV_TXN_SNAPSHOT,
	'transparent_network_ip_resolution' => 'Enabled',
	'trust_server_certificate' => 1,
	'wsid' => 'Computer1'
]);

For SQLite

File Database
$database = new Medoo([
	'type' => 'sqlite',
	'database' => 'my/database/path/database.db'
]);
Memory database
$database = new Medoo([
	'type' => 'sqlite',
	'database' => ':memory:'
]);
Temporary database

Temporary database will be deleted when the connection is closed.

$database = new Medoo([
	'type' => 'sqlite',
	'database' => ''
]);

// Or simply with no database option.
$database = new Medoo([
	'type' => 'sqlite'
]);

Debug DSN String

You can check out the DSN string from info() API.

$database = new Medoo([
	'type' => 'mysql',
	'host' => '127.0.0.1',
	'database' => 'test',
	'username' => 'your_username',
	'password' => 'your_password',
]);

echo $database->info()['dsn'];

// mysql:dbname=test;host=127.0.0.1

Test Mode

Use for test case. You can test the generated query via $database->queryString on different database. All queries will not be executed.

$database = new Medoo([
	'testMode' => true
]);

// Set the database type.
$database->type = 'mysql';

$database->select("account", ["user_name"], ["user_id[>]" => 10]);

echo $database->queryString;