Database

Introduction

Exception uses Medoo, a multiple SQL ORM based on PDO engine.

Connection configuration is into env file.

You can access to it in every model using $this->data() or everywhere using $app->data() (remember to include global istance compiling global $app;).

A full documentation of Medoo is here: https://medoo.in/doc

ORM Guide

WHERE Syntax

Some of Medoo functions are required $where argument to filter record like SQL WHERE clause which is powerful but with a lot of complex syntax, logical relativity, and potential security problem about SQL injection. But Medoo provided a powerful and extremely easy way to build WHERE query clause and prevent injection.

Basic condition

The basic condition is simple enough to understand. You can use additional symbol to get advanced filter range for number.

$this->data->select("account", "user_name", [
    "email" => "[email protected]"
]);
// WHERE email = '[email protected]'

$this->data->select("account", "user_name", [
    "user_id" => 200
]);
// WHERE user_id = 200

$this->data->select("account", "user_name", [
    "user_id[>]" => 200
]);
// WHERE user_id > 200

$this->data->select("account", "user_name", [
    "user_id[>=]" => 200
]);
// WHERE user_id >= 200

$this->data->select("account", "user_name", [
    "user_id[!]" => 200
]);
// WHERE user_id != 200

$this->data->select("account", "user_name", [
    "age[<>]" => [200, 500]
]);
// WHERE age BETWEEN 200 AND 500

$this->data->select("account", "user_name", [
    "age[><]" => [200, 500]
]);
// WHERE age NOT BETWEEN 200 AND 500

// [><] and [<>] is also available for datetime
$this->data->select("account", "user_name", [
    "birthday[<>]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);
// WHERE ("birthday" BETWEEN '2015-01-01' AND '2017-01-01')

$this->data->select("account", "user_name", [
    "birthday[><]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);
// WHERE ("birthday" NOT BETWEEN '2015-01-01' AND '2017-01-01')

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

// [Negative condition]
$this->data->select("account", "user_name", [
    "AND" => [
        "user_name[!]" => "foo",
        "user_id[!]" => 1024,
        "email[!]" => ["[email protected]", "[email protected]", "[email protected]"],
        "city[!]" => null,
        "promoted[!]" => true
    ]
]);
// WHERE
// `user_name` != 'foo' AND
// `user_id` != 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
$this->data->select("account", "user_name", [
    "user_id" => $this->data->select("post", "user_id", ["comments[>]" => 40])
]);
// WHERE user_id IN (2, 51, 321, 3431)

Relativity condition

The relativity condition can describe complex relationship between data and data. You can use AND and OR to build complex relativity condition query.

Basic
$this->data->select("account", "user_name", [
    "AND" => [
        "user_id[>]" => 200,
        "age[<>]" => [18, 25],
        "gender" => "female"
    ]
]);

// Medoo will connect relativity condition with AND by default. The following usage is the same like above.
$this->data->select("account", "user_name", [
    "user_id[>]" => 200,
    "age[<>]" => [18, 25],
    "gender" => "female"
]);

// WHERE user_id > 200 AND age BETWEEN 18 AND 25 AND gender = 'female'

$this->data->select("account", "user_name", [
    "OR" => [
        "user_id[>]" => 200,
        "age[<>]" => [18, 25],
        "gender" => "female"
    ]
]);
// WHERE user_id > 200 OR age BETWEEN 18 AND 25 OR gender = 'female'
Compound
$this->data->has("account", [
    "AND" => [
        "OR" => [
            "user_name" => "foo",
            "email" => "[email protected]"
        ],
        "password" => "12345"
    ]
]);
// WHERE (user_name = '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:
$this->data->select("account", '*', [
    "AND" => [
        "OR" => [
            "user_name" => "foo",
            "email" => "[email protected]"
        ],
        "OR" => [
            "user_name" => "bar",
            "email" => "[email protected]"
        ]
    ]
]);
// [X] SELECT * FROM "account" WHERE ("user_name" = '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.
$this->data->select("account", '*', [
    "AND #Actually, this comment feature can be used on every AND and OR relativity condition" => [
        "OR #the first condition" => [
            "user_name" => "foo",
            "email" => "[email protected]"
        ],
        "OR #the second condition" => [
            "user_name" => "bar",
            "email" => "[email protected]"
        ]
    ]
]);
// SELECT * FROM "account"
// WHERE (
//  (
//      "user_name" = 'foo' OR "email" = '[email protected]'
//  )
//  AND
//  (
//      "user_name" = 'bar' OR "email" = '[email protected]'
//  )
// )
Columns Relationship
$this->data->select("post", [
        "[>]account" => ["author_id" => "user_id"],
    ], [
        "post.id",
        "post.content"
    ], [
        "AND" => [
            // Connect two column with condition sign like [=], [>], [<], [!=] as one of array value
            "post.restrict[<]account.age",
            
            "account.user_name" => "foo",
            "account.email" => "[email protected]",
        ]
    ]
);

// WHERE "post"."restrict" < "account"."age" AND "account"."user_name" = 'foo' AND "account"."email" = '[email protected]'

LIKE condition

LIKE condition can be use it like basic condition or relativity condition with just adding [~] syntax now.

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

WHERE "city" LIKE '%lon%'

// Array support
$this->data->select("person", "id", [
    "city[~]" => ["lon", "foo", "bar"]
]);

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

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

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

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

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

$this->data->select("person", "id", [
    "name[~]" => "[!BCR]at" // Eat, Fat, Hat...
]);
Compound
// You can use SQL wildcard to match more complex situation
$this->data->select("person", "id", [
    "content[~]" => ["AND" => ["lon", "on"]]
]);

// WHERE ("content" LIKE '%lon%' AND "content" LIKE '%on%')

$this->data->select("person", "id", [
    "content[~]" => ["OR" => ["lon", "on"]]
]);

// WHERE ("content" LIKE '%lon%' OR "content" LIKE '%on%')

Order condition

$this->data->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

Full text searching feature is supported by MySQL database for advanced search result.

Search mode list
natural IN NATURAL LANGUAGE MODE
natural+query IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
boolean IN BOOLEAN MODE
query WITH QUERY EXPANSION
// [MATCH]
$this->data->select("post_table", "post_id", [
    "MATCH" => [
        "columns" => ["content", "title"],
        "keyword" => "foo",

        // [optional] Search mode
        "mode" => "natural"
    ]
]);
// WHERE MATCH (content, title) AGAINST ('foo' IN NATURAL LANGUAGE MODE)

Using Regular Expression

$data = $this->data->select('account', [
    'user_id',
    'user_name'
], [
    'user_name[REGEXP]' => '[a-z0-9]*'
]);

// SELECT "user_id","user_name"
// FROM "account"
// WHERE "user_name" REGEXP '[a-z0-9]*'

Using SQL functions

You can now use SQL function with raw object for complex usage. Read more from https://medoo.in/api/raw.

$data = $this->data->select('account', [
    'user_id',
    'user_name'
], [
    'datetime' => Medoo::raw('NOW()')
]);

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

LIMIT and OFFSET

$this->data->select("account", "user_id", [

    // Get the first 100 of rows
    'LIMIT' => 100

    // Started from the top 20 rows, and get the next 100
    'LIMIT' => [20, 100],

    // For Oracle and MSSQL database, you also need to use with GROUP by together
    'GROUP' => 'location'
]);

GROUP and HAVING

$this->data->select("account", "user_id", [
    'GROUP' => 'type',

    // GROUP by array of values
    'GROUP' => [
        'type',
        'age',
        'gender'
    ],

    // Must have to use it with GROUP together
    'HAVING' => [
        'user_id[>]' => 500
    ]
]);

SELECT

Select data from database

select($table, $columns, $where)
  • table [string]

    The table name.

  • columns [string/array]

    The target columns of data will be fetched.

  • where (optional) [array]

    The WHERE clause to filter records.

select($table, $join, $columns, $where)
  • table [string]

    The table name.

  • join [array]

    Table relativity for table joining. Ignore it if no table joining required.

  • columns [string/array]

    The target columns of data will be fetched.

  • where (optional) [array]

    The WHERE clause to filter records.

Return: [array]
You can use "*" as columns parameter to fetch all columns, but we recommended providing all targeted columns for better performance and readability.
$datas = $this->data->select("account", [
    "user_name",
    "email"
], [
    "user_id[>]" => 100
]);

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

foreach($datas as $data)
{
    echo "user_name:" . $data["user_name"] . " - email:" . $data["email"] . "<br/>";
}

// Select all columns
$datas = $this->data->select("account", "*");

// Select a column
$datas = $this->data->select("account", "user_name");

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

Table joining

SQL JOIN clause can combine rows together between two table. Medoo provide simple syntax for JOIN clause.

// [>] == LEFT JOIN
// [<] == RIGH JOIN
// [<>] == FULL JOIN
// [><] == INNER JOIN

$this->data->select("post", [
    // Here is the table relativity argument that tells the relativity between the table you want to join.

    // The row author_id from table post is equal the row user_id from table account
    "[>]account" => ["author_id" => "user_id"],

    // The row user_id from table post is equal the row user_id from table album.
    // This is a shortcut to declare the relativity if the row name are the same in both table.
    "[>]album" => "user_id",

    // [post.user_id is equal photo.user_id and post.avatar_id is equal photo.avatar_id]
    // Like above, there are two row or more are the same in both table.
    "[>]photo" => ["user_id", "avatar_id"],

    // If you want to join the same table with different value,
    // you have to assign the table with alias.
    "[>]account (replyer)" => ["replyer_id" => "user_id"],

    // You can refer the previous joined table by adding the table name before the column.
    "[>]account" => ["author_id" => "user_id"],
    "[>]album" => ["account.user_id" => "user_id"],

    // Multiple condition
    "[>]account" => [
        "author_id" => "user_id",
        "album.user_id" => "user_id"
    ]
], [
    "post.post_id",
    "post.title",
    "account.user_id",
    "account.city",
    "replyer.user_id",
    "replyer.city"
], [
    "post.user_id" => 100,
    "ORDER" => ["post.post_id" => "DESC"],
    "LIMIT" => 50
]);

// SELECT
//  `post`.`post_id`,
//  `post`.`title`,
//  `account`.`city`
// FROM `post`
// LEFT JOIN `account` ON `post`.`author_id` = `account`.`user_id`
// LEFT JOIN `album` USING (`user_id`)
// LEFT JOIN `photo` USING (`user_id`, `avatar_id`)
// WHERE
//  `post`.`user_id` = 100
// ORDER BY `post`.`post_id` DESC
// LIMIT 50

Data mapping

Customize output data construction - The key name for wrapping data has no relation with columns itself and it is multidimensional.

$data = $this->data->select("post", [
    "[>]account" => ["user_id"]
], [
    "post.post_id",
    "post.content",

    "userData" => [
        "account.user_id",
        "account.email",

        "meta" => [
            "account.location",
            "account.gender"
        ]
    ]
], [
    "LIMIT" => [0, 2]
]);

echo json_encode($data);

// Output data
[
    {
        post_id: "1",
        content: "Hello world!",
        userData: {
            user_id: "1",
            email: "[email protected]",
            meta: {
                location: "New York",
                gender: "male"
            }
        }
    },
    {
        post_id: "2",
        content: "Hey everyone",
        userData: {
            user_id: "2",
            email: "[email protected]",
            meta: {
                location: "London",
                gender: "female"
            }
        }
    }
]

Data type declaration

Set the type of output data

// Supported data type: [String | Bool | Int | Number | Object | JSON]
// [String] is the default type for all output data.
// [Object] is a PHP object data decoded by serialize(), and will be unserialize()
// [JSON] is a valid JSON, and will be json_decode()

$data = $this->data->select("post", [
    "[>]account" => ["user_id"]
], [
    "post.post_id",

    "profile" => [
        "account.age [Int]",
        "account.is_locked [Bool]",
        "account.userData [JSON]"
    ]
], [
    "LIMIT" => [0, 2]
]);

echo json_encode($data);

// Output data
[
    {
        post_id: "1",
        profile: {
            age: 20,
            is_locked: true,
            userData: ["foo", "bar", "tim"]
        }
    },
    {
        post_id: "2",
        profile: {
            age: 25,
            is_locked: false,
            userData: ["mydata1", "mydata2"]
        }
    }
]

// Store an object into database, and get it back
class Foo {
    var $bar = "cat";

    public function __wakeup()
    {
        $this->bar = "dog";
    }
}

$object_data = new Foo();

$this->data->insert("account", [
    "data" => $object_data
]);

$data = $this->data->select("account", [
    "data [object]"
], [
    "id" => 10
]);

echo $data[ 0 ][ "data" ]->bar;

// The object's __wakeup function will be called and update the value
// So the output will be "dog"
"dog"

Alias

You can use alias as a new column or table name instead of original one. This is useful for table joining to prevent name conflict.

$data = $this->data->select("account", [
    "user_id",
    "nickname(my_nickname)"
], [
    "LIMIT" => 20
]);

// $data = array(
//  [0] => array(
//      "user_id" => "1",
//      "my_nickname" => "foo"
//  ),
//  [1] => array(
//      "user_id" => "2",
//      "my_nickname" => "bar"
//  )
// )

$data = $this->data->select("post (content)", [
    "[>]account (user)" => "user_id",
], [
    "content.user_id (author_id)",
    "user.user_id"
], [
    "LIMIT" => 20
]);

// SELECT
//  "content"."user_id" AS author_id,
//  "user"."user_id"
// FROM
//  "post" AS "content"
// LEFT JOIN "account" AS "user" USING ("user_id")
// LIMIT 2

// $data = array(
//  [0] => array(
//      "author_id" => "1",
//      "user_id" => "321"
//  ),
//  [1] => array(
//      "author_id" => "2",
//      "user_id" => "322"
//  )
// )

insert

Insert new records in table

insert($table, $data)
  • table [string]

    The table name.

  • data [array]

    The data that will be inserted into table.

Return: [PDOStatement] The PDOStatement object.
$this->data->insert("account", [
    "user_name" => "foo",
    "email" => "[email protected]",
    "age" => 25
]);

Last insert ID

If you want the row ID after the insertion, you need to call the lastInsertId() alone and get it.

$this->data->insert("account", [
    "user_name" => "foo",
    "email" => "[email protected]",
    "age" => 25
]);

$account_id = $this->data->id();

Array serialization

By default, the array data will be serialized by serialize() before insertion, but you can assign it as JSON to be serialized by json_encode().

$this->data->insert("account", [
    "user_name" => "foo",
    "email" => "[email protected]",
    "age" => 25,
    "lang" => ["en", "fr", "jp", "cn"] // => 'a:4:{i:0;s:2:"en";i:1;s:2:"fr";i:2;s:2:"jp";i:3;s:2:"cn";}'
]);

$this->data->insert("account", [
    "user_name" => "foo",
    "email" => "[email protected]",
    "age" => 25,
    "lang [JSON]" => ["en", "fr", "jp", "cn"] // => '["en","fr","jp","cn"]'
]);

Type auto-detection

Medoo will automatically detect the data type before insertion and optimize it to store into database.

class Foo {
    var $bar = "cat";

    public function __wakeup()
    {
        $this->bar = "dog";
    }
}

$object_data = new Foo();

$fp = fopen($_FILES[ "file" ][ "tmp_name" ], "rb");

$this->data->insert("account", [
    // String value
    "user_name" => "foo",

    // Integer value
    "age" => 25,

    // Boolean value
    "is_locked" => true,
    
    // Array value
    "lang" => ["en", "fr", "jp", "cn"],

    // Array value encoded as JSON
    "lang [JSON]" => ["en", "fr", "jp", "cn"],

    // Object value
    "object_data" => $object_data,

    // Large Objects (LOBs)
    "image" => $fp
]);

Multi-insertion

You can also insert data multiply.

$this->data->insert("account", [
    [
        "user_name" => "foo",
        "email" => "[email protected]",
        "age" => 25,
        "city" => "New York",
        "lang [JSON]" => ["en", "fr", "jp", "cn"]
    ],
    [
        "user_name" => "bar",
        "email" => "[email protected]",
        "age" => 14,
        "city" => "Hong Kong",
        "lang [JSON]" => ["en", "jp", "cn"]
    ]
]);

PDOStatement

The return object of insert() is PDOStatement, so you can use its methods to get more information.

$data = $this->data->insert("account", [
    "user_name" => "foo",
    "email" => "[email protected]",
    "age" => 25
]);

// Returns the number of rows affected by the last SQL statement
echo $data->rowCount();

// Read more: http://php.net/manual/en/class.pdostatement.php

Using SQL functions

You can now use SQL function with raw object for complex usage. Read more from https://medoo.in/api/raw.

$this->data->insert("account", [
    "user_name" => "bar",
    "uid" => Medoo::raw("UUID()")
]);

update

Modify data in table

update($table, $data, $where)
  • table [string]

    The table name.

  • data [array]

    The data that will be modified.

  • where (optional) [array]

    The WHERE clause to filter records.

Return: [PDOStatement] The PDOStatement object.
Most of feature are like insert(), it supports array serialization and type auto-detection. Additionally you can use [+], [-], [*] and [/] for mathematical operation.
class Foo {
    var $bar = "cat";

    public function __wakeup()
    {
        $this->bar = "dog";
    }
}

$object_data = new Foo();

$fp = fopen($_FILES[ "file" ][ "tmp_name" ], "rb");

$this->data->update("account", [
    "type" => "user",

    // All age plus one
    "age[+]" => 1,

    // All level subtract 5
    "level[-]" => 5,

    // All score multiplied by 2
    "score[*]" => 2,

    // Array value
    "lang" => ["en", "fr", "jp", "cn"],

    // Array value encoded as JSON
    "lang [JSON]" => ["en", "fr", "jp", "cn"],

    // Boolean value
    "is_locked" => true,

    // Object value
    "object_data" => $object_data,

    // Large Objects (LOBs)
    "image" => $fp,

    // You can also assign # for using SQL functions
    "#uid" => "UUID()"
], [
    "user_id[<]" => 1000
]);

// The return object of update() is PDOStatement, so you can use its methods to get more information. 
$data = $this->data->update("account", [
    "age[+]" => 1
], [
    "user_id[>]" => 100
]);

// Returns the number of rows affected by the last SQL statement
echo $data->rowCount();

// Read more: http://php.net/manual/en/class.pdostatement.php

delete

Delete data from table

delete($table, $where)
  • table [string]

    The table name.

  • where [array]

    The WHERE clause to filter records.

Return: [PDOStatement] The PDOStatement object.
$this->data->delete("account", [
    "AND" => [
        "type" => "business",
        "age[<]" => 18
    ]
]);

// The return object of delete() is PDOStatement, so you can use its methods to get more information. 
$data = $this->data->delete("account", [
    "AND" => [
        "type" => "business",
        "age[<]" => 18
    ]
]);

// Returns the number of rows affected by the last SQL statement
echo $data->rowCount();

// Read more: http://php.net/manual/en/class.pdostatement.php

replace

Replace old data into new one

replace($table, $columns, $where)
  • table [string]

    The table name.

  • columns [array]

    The target columns of data will be replaced.

  • where (optional) [array]

    The WHERE clause to filter records.

Return: [PDOStatement] The PDOStatement object.
$this->data->replace("account", [
    "type" => [
        "user" => "new_user",
        "business" => "new_business"
    ],
    "column" => [
        "old_value" => "new_value"
    ]
], [
    "user_id[>]" => 1000
]);

get

Get only one record from table

get($table, $columns, $where)
  • table [string]

    The table name.

  • columns [string/array]

    The target columns of data will be fetch.

  • where (optional) [array]

    The WHERE clause to filter records.

get($table, $join, $columns, $where)
  • table [string]

    The table name.

  • join [array]

    Table relativity for table joining. Ignore it if no table joining required.

  • columns [string/array]

    The target columns of data will be fetched.

  • where (optional) [array]

    The WHERE clause to filter records.

Return: [string/array/int/object] Return the data of the column.
$email = $this->data->get("account", "email", [
    "user_id" => 1234
]);

// $email = "[email protected]"

$profile = $this->data->get("account", [
    "email",
    "gender",
    "location"
], [
    "user_id" => 1234
]);

// $profile = array(
//  "email" => "[email protected]",
//  "gender" => "female",
//  "location" => "earth"
// )

$data = $this->data->get("post", [
    "[>]account" => "user_id"
], [
    "post.content",
    "account.user_name",
    "account.location"
], [
    "ORDER" => "post.rate"
]);

// $data = array(
//  "content" => "A new day",
//  "user_name" => "foo",
//  "location" => "earth"
// )

has

Determine whether the target data existed

has($table, $where)
  • table [string]

    The table name.

  • where [array]

    The WHERE clause to filter records.

has($table, $join, $where)
  • table [string]

    The table name.

  • join [array]

    Table relativity for table joining.

  • where [array]

    The WHERE clause to filter records.

Return: [boolean] True of False if the target data has been founded.
if ($this->data->has("account", [
    "AND" => [
        "OR" => [
            "user_name" => "foo",
            "email" => "foo"
        ],
        "password" => "12345"
    ]
]))
{
    echo "Password is correct.";
}
else
{
    echo "Password error.";
}

count

Counts the number of rows

count($table, $where)
  • table [string]

    The table name.

  • where (optional) [array]

    The WHERE clause to filter records.

count($table, $join, $column, $where)
  • table [string]

    The table name.

  • join [array]

    Table relativity for table joining.

  • column [string]

    The target column will be counted.

  • where (optional) [array]

    The WHERE clause to filter records.

Return: [number] The number of rows.
The type of returned data is number.
$count = $this->data->count("account", [
    "gender" => "female"
]);

echo "We have " . $count . " female users.";

max

Get the maximum value for the column

max($table, $column, $where)
  • table [string]

    The table name.

  • column [string]

    The target column will be calculated.

  • where (optional) [array]

    The WHERE clause to filter records.

max($table, $join, $column, $where)
  • table [string]

    The table name.

  • join [array]

    Table relativity for table joining.

  • column [string]

    The target column will be calculated.

  • where (optional) [array]

    The WHERE clause to filter records.

Return: [number] The maximum number of the column.
$max = $this->data->max("account", "age", [
    "gender" => "female"
]);

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

min

Get the minimum value for the column

min($table, $column, $where)
  • table [string]

    The table name.

  • column [string]

    The target column will be calculated.

  • where (optional) [array]

    The WHERE clause to filter records.

min($table, $join, $column, $where)
  • table [string]

    The table name.

  • join [array]

    Table relativity for table joining.

  • column [string]

    The target column will be calculated.

  • where (optional) [array]

    The WHERE clause to filter records.

Return: [number] The minimum number of the column.
$min = $this->data->min("account", "age", [
    "gender" => "male"
]);

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

avg

Get the average value for the column

avg($table, $column, $where)
  • table [string]

    The table name.

  • column [string]

    The target column will be calculated.

  • where (optional) [array]

    The WHERE clause to filter records.

avg($table, $join, $column, $where)
  • table [string]

    The table name.

  • join [array]

    Table relativity for table joining.

  • column [string]

    The target column will be calculated.

  • where (optional) [array]

    The WHERE clause to filter records.

Return: [number] The average number of the column.
$average = $this->data->avg("account", "age", [
    "gender" => "male"
]);

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

sum

Get the total value for the column

sum($table, $column, $where)
  • table [string]

    The table name.

  • column [string]

    The target column will be calculated.

  • where (optional) [array]

    The WHERE clause to filter records.

sum($table, $join, $column, $where)
  • table [string]

    The table name.

  • join [array]

    Table relativity for table joining.

  • column [string]

    The target column will be calculated.

  • where (optional) [array]

    The WHERE clause to filter records.

Return: [number] The total number of the column.
$total = $this->data->sum("account", "money");

echo "We have $" . $total;

id

Returns the ID of the last inserted row

id()
Return: [number] The last inserted row ID.
$this->data->insert("account", [
    "user_name" => "foo",
    "email" => "[email protected]",
    "age" => 25
]);

$account_id = $this->data->id();

action

Start a transaction

action($callback)
  • callback [function]

    The transaction wrap for executing queries.

Return: void
Not every database or database engine supports transactions. You have to check before using it. All queries will be automatically committed inside the transaction wrap. You can also return false value to rollback the transactions.
$this->data->action(function($database) {
    $this->data->insert("account", [
        "name" => "foo",
        "email" => "[email protected]"
    ]);

    $this->data->delete("account", [
        "user_id" => 2312
    ]);

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

query

Execute customized raw query

query($query)
  • query [string]

    The SQL query.

  • map (optional) [array]

    The array of input parameters value for prepared statement

Return: [object] The PDOStatement object.
Medoo is handled all queries with SQL-92 standard. You should keep in mind about the quotation mark in the query, or use prepared statement to prevent SQL injection.
$this->data->query("CREATE TABLE table (
    c1 INT STORAGE DISK,
    c2 INT STORAGE MEMORY
) ENGINE NDB;");

$data = $this->data->query("SELECT email FROM account")->fetchAll();
print_r($data);

Quotation Syntax

The raw SQL expression provided a shortcut quoting and prefixing for table name and column name, so you don't have to care about the quotation and maintaining the table prefix. All you have to do is using <name> to explain they are table or column name. Medoo will analysis they are table or column, and translate them with correct quotation and prefix.

$data = $this->data->query("SELECT <email> FROM <account>")->fetchAll();
// This query will be translated to:
// SELECT "email" FROM "account"

$data = $this->data->query("SELECT <account.email>,<account.nickname>
    FROM <account>
    WHERE <id> != 100
")->fetchAll();
// SELECT "WP_account"."email", "WP_account"."nickname" FROM "WP_account" WHERE "id" != 100

Prepared statement

The query() also support prepared statement. Medoo will auto-detect the data type for input parameters.

$data = $this->data->query(
    "SELECT * FROM <account> WHERE <user_name> = :user_name AND <age> = :age", [
        ":user_name" => "John Smite",
        ":age" => 20
    ]
)->fetchAll();

print_r($data);

quote

Quotes the string for query

quote($string)
  • string [string]

    The target string.

Return: [string]
$data = "Medoo";

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

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

last

Return the last query performed.

last()
Return: [string]
Like log(), but just return the last query.
$this->data->select("account", [
    "user_name",
    "email"
], [
    "user_id[<]" => 20
]);

$this->data->insert("account", [
    "user_name" => "foo",
    "email" => "[email protected]"
]);

echo $this->data->last();
// INSERT INTO "account" ("user_name", "email") VALUES ('foo', '[email protected]')

log

Return the all executed queries.

log()
Return: [array] an array with all executed queries
This function will only retrun all records if setting `logging => true` on initialization, otherwise will only return one last record by default.
$database = new Medoo([
    "database_type" => "mysql",
    "database_name" => "name",
    "server" => "localhost",
    "username" => "your_username",
    "password" => "your_password",
    "charset" => "utf8",

    // Enable logging
    "logging" => true,
]);

$this->data->select("account", [
    "user_name",
    "email"
], [
    "user_id[<]" => 20
]);

$this->data->insert("account", [
    "user_name" => "foo",
    "email" => "[email protected]"
]);

var_dump( $this->data->log() );
// array(2) {
//  [0]=> string(62) "SELECT "user_name","email" FROM "account" WHERE "user_id" < 20"
//  [1]=> string(74) "INSERT INTO "account" ("user_name", "email") VALUES ('foo', '[email protected]')"
// }

// Will output only one last record if "logging" => false or ignored by default on initialization
// array(1) {
//  [0]=> string(74) "INSERT INTO "account" ("user_name", "email") VALUES ('foo', '[email protected]')"
// }

error

Return error information associated with the last operation.

error()
Return: [array] an array of error information about the last operation performed
$this->data->select("bccount", [
    "user_name",
    "email"
], [
    "user_id[<]" => 20
]);

var_dump( $this->data->error() );

// array(3) { [0]=> string(5) "42S02" [1]=> int(1146) [2]=> string(36) "Table 'my_database.bccount' doesn't exist" }

debug

Output the generated SQL without execute it.

debug()
Return: Medoo object with debug mode enabled
This feature will output the generated SQL query automatically without using echo or other function. Please keep removed it when you finish debugging. And check the following code will be executed normally if this query do not be executed.

Basic Usage

Output the generated SQL.

$this->data->debug()->select("bccount", [
    "user_name",
    "email"
], [
    "user_id[<]" => 20
]);

// Will output:
// SELECT "user_name","email" FROM "bccount" WHERE "user_id" < 20

Dangerous Case

In some of case, the page will execute multiply SQL queries in one request. If you just using debug() on one of query, the following functions will still be called, and cause some unexpected result.

// This output nothing
$this->data->insert("account", [
    "user_name" => "foo",
    "email" => "[email protected]"
]);

// Will output the generated query
$post_id = $this->data->debug()->get("post", "post_id", ["user_name" => "foo"]);

// Be careful, this query will be executed! $post_id is now became false value, because debug() ran above.
$this->data->update("account", [
    "level[+]" => 5,
    "post" => $post_id
], [
    "user_name" => "foo"
]);

info

Get information about the connected database.

info()
Return: [string]
print_r($this->data->info());

/*
Array
(
    [server] => Uptime: 5074  Threads: 1  Questions: 15  Slow queries: 0  Opens: 67  Flush tables: 1
        Open tables: 60  Queries per second avg: 0.002
    [client] => mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $
    [driver] => mysql
    [version] => 5.6.10
    [connection] => localhost via TCP/IP
)
*/

Do you need help?

Write me an e-mail: [email protected]