New from Chevere is the SQL2P package. This software generates Parameter(s) for MySQL.

The package source is available at chevere/sql2p.

What it does?

From a CREATE TABLE statement like this one below.

CREATE TABLE `invoice` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT UNSIGNED NOT NULL,
  `datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `details` LONGTEXT NULL,
  `quantity` INT UNSIGNED NOT NULL,
  `rate` DECIMAL(10,2) NOT NULL,
  `total` DECIMAL(19,4) GENERATED ALWAYS AS (quantity*rate),
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

SQL2P generates the following PHP code.

use Chevere\Parameter\Interfaces\ArrayParameterInterface;
use function Chevere\Parameter\arrayp;
use function Chevere\Parameter\datetime;
use function Chevere\Parameter\float;
use function Chevere\Parameter\int;
use function Chevere\Parameter\null;
use function Chevere\Parameter\string;
use function Chevere\Parameter\union;

function invoiceTable(): ArrayParameterInterface
{
    return arrayp(
        id: int(min: 0),
        client_id: int(min: 0),
        datetime: datetime(),
        details: union(
            null(),
            string()
        ),
        quantity: int(min: 0),
        rate: float(),
        total: float()
    );
}

The invoiceTable() function returns table invoice schema in PHP code using Array Parameter where each column is represented by another Parameter.

From this you can add your own validation rules on top of generated code.

For example, you limit quantity to a range of 100, 200 by adding max and min arguments. Add a regex to details to validate string shape.

details: union(
    null(),
-    string()
+    string('/^(?!\s*$)./')
),
-quantity: int(min: 0),
+quantity: int(max: 200, min: 100),

Array Parameter object returned by this function can be also used to dynamic interact with one or more of these columns. See Array Composing to learn more.

Data validation

Use SQL2P to validate data against table Parameter schema.

For example, on a single fetch result you may get the following array for a database row.

SELECT * FROM invoice WHERE id = 1

$fetch = [
    'id' => 1,
    'client_id' => 1234,
    'datetime' => '2023-10-22 19:58:44',
    'details' => null,
    'quantity' => 100,
    'rate' => 16.5,
    'total' => 1650,
];

Function invoiceTable() can be used to validate $fetch by invoking it.

$table = invoiceTable();
$table($fetch); // validation

Use arrayFrom function to create an array taking only the columns you need.

SELECT id, total FROM invoice WHERE id = 1

use function Chevere\Parameter\arrayFrom;

$fetch = [
    'id' => 1,
    'total' => 1650,
];
$table = arrayFrom(invoiceTable(), 'id', 'total');
$table($fetch);

Use arguments function to get typed access to fetched array members.

use function Chevere\Parameter\arguments;

$invoice = arguments($table, $fetch);
$total = $invoice->required('total')->int(); // 1650

When fetching multiple rows wrap Array table with iterable function.

SELECT id, total FROM invoice WHERE id > 0

$fetchAll = [
    0 => [
        'id' => 1,
        'total' => 1650,
    ],
    1 => [
        'id' => 2,
        'total' => 1820,
    ],
];
$iterable = iterable($table);
$iterable($fetchAll);

Note that arguments function supports iterable.

$invoices = arguments($iterable, $fetchAll);
$secondRow = $invoices->required('1')->array();

Learn more

Go to SQL2P package documentation to learn more.