Download the PHP package maplephp/query without Composer

MaplePHP - Database query builder

MaplePHP - Database query builder is a powerful yet user-friendly library for making safe database queries, with support for MySQL, SQLite and PostgreSQL.

Contents

• Connect to the database
• Make queries
• Attributes
• Multiple Connections
• Migrations (Coming soon)

Connect to the database

Connect to MySQL

Connect to SQLite

Connect to PostgreSQL

Note: That the first connection will count as the main connection if not overwritten. You can also have multiple connection, click here for more information.

Make queries

Start with the namespace

Select 1:

Default alias will be the table name e.g. users and login if they were not overwritten.

Select 2:

Where 1

Where 2

"compare", "or"/"and" and "not".

Where 3

Where 4

Having

Having command works the same as where command above with exception that you rename "where" method to "having" and that the method "havingBind" do not exist.

Limit

Order

Join

Note that no value in the join is, by default, enclosed. This means it will not add quotes to strings. This means it will attempt to add a database column by default if it is a string, and will return an error if the string column does not exist. If you want to enclose the value with quotes, use Attributes (see the section below).

Pluck

Insert

Update on duplicate

Will update row if primary key exist else Insert

Update

Delete

Set

Preview SQL code before executing

Attributes

Each value is automatically escaped by default in the most effective manner to ensure consequential and secure data storage, guarding against SQL injection vulnerabilities. While it's possible to exert complete control over SQL input using various Raw methods, such an approach is not advisable due to the potential for mistakes that could introduce vulnerabilities. A safer alternative is to leverage the Attr class. The Attr class offers comprehensive configuration capabilities for nearly every value in the DB library, as illustrated below:

Escape values and protect against SQL injections

Example:

• Input value: Lorem "ipsum" dolor
• Output value: Lorem \"ipsum\" dolor

Enable/disable string enclose

Example:

• Input value: 1186
• Output value: '1186' E.g. will add or remove quotes to values

Enable/disable XSS protection

Some like to have the all the database data already HTML special character escaped.

Example:

• Input value: Lorem ipsum dolor
• Output value: Lorem \<strong>ipsum\</strong> dolor

Automatically json encode array data

A pragmatic function that will automatically encode all array input data to a json string

Example:

• Input value: array("firstname" => "John", "lastname" => "Doe");
• Output value: {"firstname":"John","lastname":"Doe"}

The default values vary based on whether it is a table column, a condition in a WHERE clause, or a value to be set. For instance, if expecting a table columns, the default is not to enclose value with quotes, whereas for WHERE or SET inputs, it defaults is to enclose the values. Regardless, every value defaults to prep, encode and jsonEncode being set to true.

Multiple connections

You can have multiple connection to different MySQL or SQLite databases or both.