Here we will describe how to use EasyQuery.JS widgets to implement an ad-hoc user-friendly query builder in your PHP application. In result you can get something like this sample web-page for advanced DB search:
So, let’s start.
To run this sample you will need the following programs/services be installed on your computer/server:
mysqliextension for PHP
Please note: you will need it only to run eqdm command line utility (see more details below) and it's not necessary to run it on the computer where you plan to set up this demo web-page or on your production server.
Of course, to perform all the steps below you will need to download EasyQuery.JS first and unpack it into some folder on your hard drive.
eqjs.zip archive contains almost all necessary parts to run and test query builder web-page locally. It's preconfigured to run over well-known demo Northwind database and already include an API key for the testing account on SqlQueryBuilder.com web-service (more about it later).
So, all you need to do is:
eqjs.zipfile to some folder on your hard drive
samples\PHP7Demo01folder from archive into
/htdocsfolder of your Apache installation. You can use any sub-folder of
/htdocsas well (e.g.
apache dir>/htdocs/easyqueryfolder you will be able to access it by
http://localhost/easyquery/QueryBuilder.html(don’t forget to add your port number if you run your Apache server on other than 80 port).
If you encounter any problem on any of the steps described above please don't hesitate and contact us immediately.
Now, when our sample web-page is setup and running well, we are going to discuss how to get the same behavior in your own web-application and for your own database
As you possibly know (if not - please read this article first) EasyQuery components and widgets do not work with your database directly. Instead, they use a special user-friendly representation of your DB called “data model”.
The data model defines which entities and attributes your users can operate with when they build their queries. It allows to set user-friendly names for tables (entities), fields (attributes) and operators (like “is equal to” or “starts with”). Additionally data model can contain a list of predefined values for some attribute - so your users will be able to select a criteria parameter from some list instead of typing it.
.NET version of EasyQuery allows you to build data model at run-time. On other platforms, you will need to create data model “manually” using either our eqdm command line utility (runs on Windows, Linux and Mac) or Data Model Editor (DME) GUI application (only for Windows).
Here are step-by-step instructions for both cases:
eqdm.jsonfile there and open it in some text editor
DbType. Possible options are: "MsSql", "MySql" and "PostreSql" (other DB types will be supported soon as well ).
run.shfor Linux and Mac) command.
When it's finished - you will find ModelID.xml and ModelID.json files in the same folder.
\DME40sub-folder of unpacked EasyQuery.JS package.
That’s all, close DME.
Copy the JSON file with your model (built on step #2) into the same folder with
This script processes all AJAX requests from EasyQuery widgets. At the beginning of that script you can find the following definitions:
public static $SQBAPI_HOST= "http://sqlquerybuilder.com/"; //You will need to change this address in case of using a standalone (local) version of SQL Query Builder web-servive public static $SQBAPI_KEY = "3ea4523c-1d18-4d8c-82f5-c4f998d67daf"; //<-- change to your API key public static $MODEL_ID = "NWind"; //<-- change to the ID of your model public static $MODEL_FILE_JSON = "NWind.json"; //<-- change to the name of your model file
Our sample script loads NWind.json model file. You need to replace the value of
MODEL_FILE_JSON configuration settings to the ID of your model and the name of JSON file created on previous step (e.g. "MyModel" and "MyModel.json").
EasyQuery widgets send the query, defined by the user in a JSON format. You can get it through
queryJson parameter in
executeQuery action handlers.
So, how do we get a correct SQL statement by this JSON query?
The answer is a special SQL query builder web-service we created for this task. This service implements a web API which allows you to pass your query JSON string and get a correct SQL statement in the result. To use it you need to get your API Key first and attach this key to any your request (in a Header section).
So, here are the instructions:
public static $SQBAPI_HOST = "http://sqlquerybuilder.com/"; public static $SQBAPI_KEY = "Your API key goes here";
MODEL_IDconfig setting above) and copy the content of the XML file created on the step #2.
/api/2.0/SqlQueryBuilderaction when you need to get an SQL statement. Send the JSON representation of the query returned by EasyQuery widgets as the request's content.
So all you need to do now - is to execute this SQL statement over your database, return the result set back to the client-side in some format and show that result set to the user in a form of some data grid or chart. We gave an example of possible JSON string in the Node.js script from our sample.
In our demo web page, we show generated SQL statement on any query change. Of course, you don’t need to do it in a production environment. Most possibly, you will hide it from users and show only the result table returned after SQL execution.
Our SQL query builder REST service is free but has some limitations on the number of daily requests one user can send.
To remove those limits or if you want to avoid using a third-party web-service for SQL generation - we have a local version of this service which you can install on your own Windows, Linux or Mac server.
For more information please read EasyQuery.JS web-page.
Feel free to send a support request if you have any questions regarding EasyQuery widgets or SqlQueryBuilder.com web-service.