vlw/mysql

Abstraction library for common MySQL/MariaDB DML operations with php-mysqli

Maintainers

Details

codeberg.org/vlw/php-mysql

Installs: 12

Dependents: 0

Suggesters: 0

Security: 0

3.5.2 2024-09-25 13:28 UTC

GPL-3.0-or-later 619f43b3bfab9eb034dca3e54c7466055240c861

  • Victor Westerlund <victor.woop@vlw.se>

This package is not auto-updated.

Last update: 2024-11-10 12:08:53 UTC

README

This is a simple abstraction library for MySQL DML operations.

For example:

MySQL->for(string $table)
  ->where(?array ...$conditions)
  ->order(?array $order_by)
  ->limit(int|array|null $limit)
  ->select(array $columns): array|bool;

which would be equivalent to the following in MySQL:

SELECT $columns FROM $table WHERE $filter ORDER BY $order_by LIMIT $limit;

[!IMPORTANT] This library requires the MySQL Improved extension and requires PHP 8.0 or newer.

Install from composer

composer require vlw/mysql

use vlw\MySQL\MySQL;

Example / Documentation

Available statements Statement|Method --|-- SELECT|select() UPDATE|update() INSERT|insert() DELETE|delete() WHERE|where() ORDER BY|order() LIMIT|limit()

Example table name: beverages id|beverage_type|beverage_name|beverage_size --|--|--|-- 0|coffee|cappuccino|10 1|coffee|black|15 2|tea|green|10 3|tea|black|15

use vlw\MySQL\MySQL;

// Pass through: https://www.php.net/manual/en/mysqli.construct.php
$db = new MySQL($host, $user, $pass, $db);

All executor methods select(), update(), and insert() will return a mysqli_result object or boolean.

FOR

MySQL->for(
  string $table
): self;

All queries start by chaining the for(string $table) method. This will define which database table the current query should be executed on.

Example:

MySQL->for("beverages")->select("beverage_type");

SELECT

Chain MySQL->select() anywhere after a MySQL->for() to retrieve columns from a database table.

Pass an associative array of strings, CSV string, or null to this method to filter columns.

MySQL->select(
  array|string|null $columns
): mysqli_result|bool;

In most cases you probably want to select with a constraint. Chain the where() method before select() to filter the query

Example

$beverages = MySQL->for("beverages")->select(["beverage_name", "beverage_size"]); // SELECT beverage_name, beverage_size FROM beverages

[
  [
    "beverage_name" => "cappuccino",
    "beverage_size" => 10
  ],
  [
    "beverage_name" => "black",
    "beverage_size" => 15
  ],
  // ...etc
]

INSERT

Chain MySQL->insert() anywhere after a MySQL->for() to append a new row to a database table.

Passing a sequential array to insert() will assume that you wish to insert data for all defined columns in the table. Pass an associative array of [column_name => value] to INSERT data for specific columns (assuming the other columns have a DEFAULT value defined).

MySQL->insert(
  // Array of values to INSERT
  array $values
): mysqli_result|bool
// Returns true if row was inserted

Example

MySQL->for("beverages")->insert([
  null,
  "coffee",
  "latte",
  10
]);
// INSERT INTO beverages VALUES (null, "coffee", "latte", 10);

true

DELETE

Chain MySQL->delete() anywhere after a MySQL->for() to remove a row or rows from the a database table.

MySQL->delete(
  array ...$conditions
): mysqli_result|bool
// Returns true if at least one row was deleted

This method takes at least one MySQL->where()-syntaxed argument to determine which row or rows to delete. Refer to the MySQL->where() section for more information.

Example

MySQL->for("beverages")->insert([
  null,
  "coffee",
  "latte",
  10
]);
// INSERT INTO beverages VALUES (null, "coffee", "latte", 10);

true

UPDATE

Chain MySQL->update() anywhere after a MySQL->for() to modify existing rows in a database table.

MySQL->update(
  // Key, value array of column names and values to update
  array $fields,
): mysqli_result|bool;
// Returns true if at least 1 row was changed

Example

MySQL->for("beverages")->update(["beverage_size" => 10]); // UPDATE beverages SET beverage_size = 10

true

In most cases you probably want to UPDATE against a constaint. Chain a where() method before MySQL->update() to set constraints

WHERE

Filter a MySQL->select() or MySQL->update() method by chaining the MySQL->where() method anywhere before it. The MySQL->delete() executor method also uses the same syntax for its arguments.

Each key, value pair will be AND constrained against each other.

MySQL->where(
  ?array ...$conditions
): self;

Example

$coffee = MySQL->for("beverages")->where(["beverage_type" => "coffee"])->select(["beverage_name", "beverage_size"]); // SELECT beverage_name, beverage_size FROM beverages WHERE (beverage_type = "coffee");

[
  [
    "beverage_name" => "cappuccino",
    "beverage_size" => 10
  ],
  [
    "beverage_name" => "black",
    "beverage_size" => 15
  ]
]

Capture groups

AND

Add additional key value pairs to an array passed to where() and they will all be compared as AND with each other.

MySQL->where([
  "beverage_type" => "coffee",
  "beverage_size" => 15
]);

WHERE (beverage_type = 'coffee' AND beverage_size = 15)

OR

Passing an additional array of key values as an argument will OR it with all other arrays passed.

$filter1 = [
  "beverage_type" => "coffee",
  "beverage_size" => 15
];

$filter2 = [
  "beverage_type" => "tea",
  "beverage_name" => "black"
];

MySQL->where($filter1, $filter2, ...);

WHERE (beverage_type = 'coffee' AND beverage_size = 15) OR (beverage_type = 'tea' AND beverage_name = 'black')

Define custom operators

By default, all values in an the assoc array passed to where() will be treated as an EQUALS (=) operator.

MySQL->where(["column" => "euqals_this_value"]);

Setting the value of any key to another assoc array will allow for more "advanced" filtering by defining your own Operators.

The key of this subarray can be any MySQL operator string, or the ->value of any case in the Operators enum.

MySQL->where([
  "beverage_name" => [
    Operators::LIKE->value => "%wildcard_contains%"
  ]
]);

ORDER BY

Chain the MySQL->order() method before a MySQL->select() statement to order by a specific column

MySQL->order(
  ?array $order_by
): self;

$coffee = MySQL->for("beverages")->order(["beverage_name" => "ASC"])->select(["beverage_name", "beverage_size"]); // SELECT beverage_name, beverage_size FROM beverages ORDER BY beverage_name ASC

[
  [
    "beverage_name" => "tea",
    "beverage_size" => 10
  ],
  [
    "beverage_name" => "tea",
    "beverage_size" => 15
  ],
  // ...etc for "beverage_name = coffee"
]

LIMIT

Chain the limit() method before a MySQL->select() statement to limit the amount of columns returned

MySQL->limit(
  ?int $limit,
  ?int $offset = null
): self;

Passing a single integer argument

This will simply LIMIT the results returned to the integer passed

$coffee = MySQL->for("beverages")->limit(1)->select(["beverage_name", "beverage_size"]); // SELECT beverage_name, beverage_size FROM beverages WHERE beverage_type = "coffee" LIMIT 1

[
  [
    "beverage_name" => "cappuccino",
    "beverage_size" => 10
  ]
]

Passing two integer arguments

This will OFFSET and LIMIT the results returned. The first argument will be the LIMIT and the second argument will be its OFFSET.

$coffee = MySQL->for("beverages")->limit(3, 2)->select(["beverage_name", "beverage_size"]); // SELECT beverage_name, beverage_size FROM beverages LIMIT 3 OFFSET 2

[
  [
    "beverage_name" => "tea",
    "beverage_size" => 10
  ],
  [
    "beverage_name" => "tea",
    "beverage_size" => 15
  ],
  // ...etc
]