mstone121/php-object-sql

An object-oriented approach to SQL generation

v0.3 2021-04-27 02:16 UTC

Requires

• php: >=7.2

MIT

• Matt Stone

• sql
• object-oriented

README

An object-oriented approach to SQL generation in PHP

The goal of this project is to apply the principles of object-oriented programming without the strict requirements and lengthy setup of ORMs to the generation of SQL queries.

Before

$query = "SELECT question.id, qr.id, qr.response_label
    FROM questions q
    LEFT JOIN question_responses qr ON qr.question_id = q.id";

if ($questionIds) {
    $query .= "WHERE q.id IN ('" . implode("','", $questionIds) . "')";
}

$query .= " ORDER BY q.id, qr.id";

After

$query = new QString(
    new QSelect([
        'q.id',
        'qr.id',
        'qr.label'
    ]),
    new QFrom('questions'),
    new QJoinsCollection(
        new QJoinOn(
            'question_responses qr',
            'qr.question_id = q.id',
            'LEFT'
        )
    ),
    new QOrder(['q.id', 'qr.id'])
);

if ($questionIds) {
    $query->addComponent(
        new QWhere(
            new QAnd(
                new QIn('q.id', $questionIds)
            )
        )
    );
}

Please note that no checking is performed on strings passed to QComponents. This means you could potentially create a query like so:

new QString(
    new QSelect([
        "1; TRUNCATE questions; SELECT *"
    ]),
    new QFrom('questions')
)

and it would delete everything in the questions table.

This library assumes that you are using it consciously and therefore allows you greater flexibility along with greater responsibility.