Javascript query builder with PHP script

What it's all about.

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.

0. Prerequisites.

To run this sample you will need the following programs/services be installed on your computer/server:

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.

1. Running sample with the testing data

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:

  • Unpack eqjs.zip file to some folder on your hard drive
  • Copy the content of samples\PHP7Demo01 folder from archive into /htdocs folder of your Apache installation. You can use any sub-folder of /htdocs as well (e.g. /htdocs/easyquery)
  • Open QueryBuilder.html page from that folder in the browser. For example, if you place it into apache dir>/htdocs/easyquery folder 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).
    You can try to build a simple query and execute it: For example, add "Customer Company Name" and "Order Freight" columns into "Columns" panel and a simple condition like "Customer Country is equal to USA".

If you encounter any problem on any of the steps described above please don't hesitate and contact us immediately.

2. Create data model for your own database.

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 command line utility

  • Open eqdm folder, find eqdm.json file there and open it in some text editor
  • Specify the type of your database in DbType. Possible options are: "MsSql", "MySql" and "PostreSql" (other DB types will be supported soon as well ).
  • Specify the connection string to your database. If you don't know how the connection string should look like for your DB - use ConnectionStrings.com website. Just select your database and then use the Connector/Net option there.
  • You can also specify your model ID and name in the config file. Alternatively, your can pass them in parameters as --modelId:ModelID and --modelName:ModelName.
  • Save the config file and start eqdm utility using run.bat (or run.sh for Linux and Mac) command.

When it's finished - you will find ModelID.xml and ModelID.json files in the same folder.

DME

  • Run DMEditor40.exe file from \DME40 sub-folder of unpacked EasyQuery.JS package.
  • Select "Model | New" menu item.
  • Type your model name (any), select the type of your database type (MS SQL, Oracle, MySQL, etc) and enter the connection string. If you don’t know how connection string may look like - use “Build connection” button and follow the wizard which helps you to set up a connection to your DB.
  • Press OK in “New model” dialog and then follow the prompts that will appear to add tables, links, etc. Save your model to some XML file (Model | Save menu) for future use.
  • Use “Model | Save as..” menu item to create a copy of your model file in JSON format (e.g. "MyModel.json"). Select “JSON” file type in “Save as type” field.

That’s all, close DME.

3. Connect your model and modify EasyQuery.php script

Copy the JSON file with your model (built on step #2) into the same folder with EasyQuery.php file. 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_ID and 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").

4. Generating and executing SQL by the query defined in UI

EasyQuery widgets send the query, defined by the user in a JSON format. You can get it through queryJson parameter in saveQuery, syncQuery or 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:

  • Open SQL query builder web-page and click on Register link.
  • Fill out the form and click on Register button to finish registration and get your API Key.
  • You will get your API key in a moment by email. Copy it into your EasyQuery.php file:
public static $SQBAPI_HOST = "http://sqlquerybuilder.com/";
public static $SQBAPI_KEY = "Your API key goes here";
  • Then press “Add model” link, enter the ID of your model (the same as you set for MODEL_ID config setting above) and copy the content of the XML file created on the step #2.
  • Now you can send a POST request to /api/2.0/SqlQueryBuilder action 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.

5. Standalone (local) version of SQL query builder web-service

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.


Tags: 
Published: 2016-10-28 Last updated: 2018-02-07