BaseSQL

BaseSQL is a complete database framework written in PHP. It was built to accelerate projects development by handle database connections and queries in a simple yet powerful way.

Features

• DBMS supported: MySQL, SQLite, PostgreSQL
• Security: Prevents SQL injection
• Difficulty: Simple to manage
• Price: Free usage under the MIT license
• Modeling: Design patterns and SOLID principles

Install via composer

Add BaseSQL to composer.json configuration file.

composer require willpinha/basesql

And update the composer.

composer update

Documentation

The full documentation can be found here.

Quick start

Require composer autoload and use the main class BaseSQL.

require_once("vendor/autoload.php");

use BaseSQL\BaseSQL;

Connect to a database

The BaseSQL class receives a single constructor argument indicating the DBMS you want to access.

$basesql = new BaseSQL("mysql");

Choosing the database type directly affects which instances will be returned by the BaseSQL object's methods.

$conn = $basesql->createConnection([
  "hostname" => "...",
  "username" => "...",
  "password" => "...",
  "database" => "..."
]);

In our example, since we choose MySQL as our DBMS, the variable $conn will be a MySQL Connection object.

Build queries

The Query class is in charge of building and parsing SQL expressions. To get a Query instance based on the DBMS choosen, you should call the BaseSQL createQuery() method.

$sql = $basesql->createQuery();

Lets build an insert query and get its translation.

// building insert query
$sql->insert()
    ->into("table")
    ->values([
      "columnA" => "valueA",
      "columnB" => "valueB"
    ]);
    
// getting query translation/result
$translation = $sql->translate();

$paramQuery = $translation->getParamQuery(); // string
$args = $translation->getArgs();             // array
$isWriteType = $translation->isWriteType();  // boolean

The Query class supports almost every SQL expression, even the unique expressions a particularly DBMS has.

Run queries

After building a query, you can run it through a Connection object.

$sql = $basesql->createQuery();

$sql->update("table")
    ->set([
      "column" => "newValue"
    ])
    ->where("column", "=", "oldValue");
    
$result = $conn->query($sql);

The query() method will return different values depending on the query type:

• Write-type queries (like INSERT, UPDATE, DELETE) will return true on success and false otherwise.

• Read-type queries (like SELECT) will return a QueryResult instance that can be used to get information from the database.

Get results

Lets build a read-type query and get its results.

$sql = $basesql->createQuery();

$sql->select(["columnA", "columnB", "columnC"])
    ->from("table")
    ->where("columnA", "LIKE", "%somevalue")
    ->andWhere("columnB", "=", "othervalue");
    
$result = $conn->query($sql);