Php sql query builder
Php sql query builder
Hydrahon is a standalone database / SQL query builder written in PHP. It was built to enhance existing frameworks, libraries and applications that handle the database connection on their own. It does not come with a PDO or mysqli wrapper. The naming is heavily inspired by Eloquent and the Kohana Framework Database component.
What does that mean «Standalone query builder»?
Hydrahon only generates a query string and an array of parameters. On its own, it is not able to execute a query.
Hydrahon follows PSR-4 autoloading and can be installed using composer:
The full documentation can be found on clancats.io
Quick Start (MySQL) ⚡️
Hydrahon is designed to be a pretty generic query builder. So for this quick start, we stick with SQL.
Again this library is not built as a full database abstraction or ORM, it is only and will always be only a query builder. This means we need to implement the database connection and fetching by ourselves. The Hydrahon constructor therefore requires you to provide a callback function that does this, and returns the results.
In this example, we are going to use PDO
Setup a simple table
To continue with our examples, we need to create a simple MySQL table.
Currently, we do not have any data, to fix this let’s go and insert some.
Will execute the following query:
As you can see Hydrahon automatically escapes the parameters.
However, because we are humans that get confused when there are hundreds of thousands of questions marks, I will continue to always display the runnable query:
Ah snap, time runs so fast, «Ray» is actually already 26.
Currently, you might think: «Well isn’t it much simpler to just write the SQL query? I mean the PHP code is even longer. «.
You have to understand that these are some very very basic examples the Hydrahon query builder starts to shine when things get more complex. However, a «Quick Start» is just the wrong place for complicated stuff, so throw an eye on the full documentation.
Dammit John, I hate you.
And finally, fetch the data.
For the next few examples, lets assume a larger dataset so that the queries make sense.
Chaining where conditions:
By default all where conditions are defined with the and operator.
Different where operators:
Please check the relevant section in the full documentation for more where-functions, like
Php sql query builder
FluentPDO is a PHP SQL query builder using PDO. It’s a quick and light library featuring a smart join builder, which automatically creates table joins for you.
The stable release of FluentPDO and actively maintained. Officially supports PHP 7.3 to PHP 8.0, but it can work with previous versions of PHP 7.
The legacy release of FluentPDO. It is no longer supported and will not be maintained or updated. This version works with PHP 5.4 to 7.1.
This version is a full rewrite of Fluent from the ground up. Its main advantage is significantly less memory usage and much greater performance in query building. It also places a few additional restrictions to make queries easier to read and maintain. Documentation has also been a very common request, and version 3 is being fully documented alongside development. Details and metrics will be posted once available.
The preferred way to install FluentPDO is via composer.
Add the following line in your composer.json file:
If you prefer not to use composer, download the latest release, create the directory Envms/FluentPDO in your library directory, and drop this repository into it. Finally, add:
to the top of your application. Note: You will need an autoloader to use FluentPDO without changing its source code.
Create a new PDO instance, and pass the instance to FluentPDO:
Then, creating queries is quick and easy:
which would build the query below:
To get data from the select, all we do is loop through the returned array:
Using the Smart Join Builder
Let’s start with a traditional join, below:
That’s pretty verbose, and not very smart. If your tables use proper primary and foreign key names, you can shorten the above to:
That’s better, but not ideal. However, it would be even easier to not write any joins:
Awesome, right? FluentPDO is able to build the join for you, by you prepending the foreign table name to the requested column.
All three snippets above will create the exact same query:
Close your connection
Finally, it’s always a good idea to free resources as soon as they are done with their duties:
CRUD Query Examples
Free for commercial and non-commercial use under the Apache 2.0 or GPL 2.0 licenses.
Php sql query builder
Query Builder is a fast, simple, methods-chaining, dependency-free library to create SQL Queries simple and fast to write, extend and manage. Supports databases which are supported by PDO. Can be also used as Database Abstraction Layer.
Usage whith connection estabilished before, in some other system’s part.
Query Builder Methods
If method not starts with «or*, multiple calls will join it as «AND».
Wheres (all methods above) can also take Closure as first argument. This can make sub-criterias. Sub-criterias will be joined to main query using joined from used method. As argument of the anonymous function is object NestedCriteria that allows You to use all the where() methods above.
If insert call operate on table with AUTO_INCREMENT column, method will return inserted ID of row. Also You can use another method (after call insert() method) to do the same thing:
In most of all methods and parameters You can pass the RAW value as argument. To do this You have to only use raw() method and pass the result of this method to any argument You want.
Sub-Queries and Nested Queries
In some cases You might need to create SubQuery to provide some special functionality. Use the subQuery() method to do this thing. Examples of usage:
Generated query by Query Builder.
Get compiled Query
If You want to preview a query, before execution, or for debugging intensions, You may want use getQuery() method, which returns Query object, with compiled query (with placeholders), array of bindings and the PDO instance. This object contains all data of current Query Builder instance.
About
Fast, simple and dependency-free query builder for PHP.
Php sql query builder
An elegant lightweight and efficient SQL Query Builder with fluid interface SQL syntax supporting bindings and complicated query generation. Works without establishing a connection to the database.
The recommended way to install the SQL Query Builder is through Composer. Run the following command to install it:
The SQL Query Builder allows to generate complex SQL queries standard using the SQL-2003 dialect (default) and the MySQL dialect, that extends the SQL-2003 dialect.
2.1. Generic Builder ↑
The Generic Query Builder is the default builder for this class and writes standard SQL-2003.
All column aliases are escaped using the ‘ sign by default.
The MySQL Query Builder has its own class, that inherits from the SQL-2003 builder. All columns will be wrapped with the tilde ` sign.
All table and column aliases are escaped using the tilde sign by default.
2.3. Human Readable Output ↑
Both Generic and MySQL Query Builder can write complex SQL queries.
Every developer out there needs at some point revising the output of a complicated query, the SQL Query Builder includes a human-friendly output method, and therefore the writeFormatted method is there to aid the developer when need.
Keep in mind writeFormatted is to be avoided at all cost in production mode as it adds unneeded overhead due to parsing and re-formatting of the generated statement.
More complicated examples can be found in the documentation.
SQL Builder for PHP, with JOIN support?
Are any of you aware of a library that helps you build/manipulate SQL queries, that supports JOIN’s?
It would give a lot of flexibility i’d think if you have something where you could return an object, that has some query set, and still be able to apply JOIN’s to it, subqueries and such.
I’ve search around, and have only found SQL Builder, which seems very basic, and doesn’t support joins. Which would be a major feature that would really make it useful.
10 Answers 10
Maybe you can try an ORM, like Propel or Doctrine, they have a nice programmatic query language, and they return you arrays of objects that represent rows in your database.
For example with Doctrine you can do joins like this:
and you can do a lot more with both.
Zend_Db_Select from the Zend_Db package of the Zend Framework can do such things as:
If you don’t like to run a full-blown ORM package, this could be the way to go.
I highly recommend CakePHP. It creates joins for you automatically, based on the associations between tables.
Say if you were writing a blog:
That would be all you have to write to view a blog post, your database schema is read and cached. As long as you keep it consistent with the conventions, you don’t have to tell cake anything about how your table is set up.
It has adapters to support MySQL, MSSQL, PostgreSQL, SQLite, Oracle and others. You can also wrap webservices as models, and even get it to do joins between data in your database and remote data! It’s very clever stuff.