Javascript query builder with PHP script

What it's all about.

Here we will describe how to use EasyQuery.JS widgets with PHP script to implement an ad-hoc user-friendly query builder on your web-site and 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:

  • Apache HTTP server
  • PHP. Please modify php.ini file to set magic_quotes_gpc variable to Off
  • MySQL server
  • mysqli extension for PHP
  • .NET 3.5 or higher
    Please note: you will need it only to run Data Model Editor utility (see more details below) and it's not necessary to run it on the computer where you plan to setup this demo web-page or on your production server.
    .NET 3.5 is included into Windows Vista or higher version of Windows OS.

Of course, to perform all steps below you need to download EasyQuery.JS first and unpack it into some folder on your hard drive.

1. Running our sample web-page

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 testing account on SqlQueryBuilder.com web-service (more about it later).
So, all you need to do is:

  • Create NWind database on your local MySQL server and import its content using nwind-mysql.sql script from /data folder of eqjs.zip archive.
    You can do it either using PHPMyAdmin web application or MySQL consol commands.
  • Copy the content of /htdocs/php folder from archive into /htdocs folder of your Apache installation. You can use any sub-folder of /htdocs as well (e.g. /htdocs/easyquery).
  • In EasyQuery.php file change the values of $DB_USER, $DB_PASSWORD and other static fields of config class to your username, paswword, etc.
    . . . . . . . . . 
    public static $DB_HOST='localhost';
    public static $DB_PORT='3306';//default mysql port
    public static $DB_USER='root';
    public static $DB_PASSWD='';
    . . . . . . . . . 
    
  • Open QueryBuilder.html page from that folder. 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).
  • Try to build a simple query and execute it. For example you can add "Customer Company Name" and "Order Freight" columns into "Columns" panel and a simple condition like "Customer Country is equal to USA".

NB: 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 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”.

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. PHP version does not have such functionality yet so the only solution - is to create data model “manually” using Data Model Editor (DME) utility.

Here are step-by-step instructions:

  • Run DMEditor35.exe file from \DME 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 help you to setup 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 static fields of config class: $QBREST_HOST, $QBREST_KEY, $MODEL_NAME and $MODEL_FILE_JSON

Our sample script loads NWind.json model file. You need to replace the value of $MODEL_NAME and $MODEL_FILE_JSON variables to the name of your model and corresponding XML file (e.g. "MyModel" and "MyModel.json"):

 . . . . . . . . 
  public static $MODEL_NAME = "NWind"; //<-- change to the name of your model
  public static $MODEL_FILE_JSON = "NWind.json"; //<-- change to the name of your model file
. . . . . . . . . 

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

EasyQuery widgets send the query, defined by user in a JSON format. You can get it through queryJson parameter in saveQuery, buildQuery or executeQuery actions.
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 RESTful API which allows to pass your query JSON string and get a correct SQL statement in 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:

  1. Open http://sqlquerybuilder.com web-page and click on Register button.
  2. Fill the form and click on Register to finish registration and get your API Key.
  3. You will get your API key in a moment by email. Copy it into your PHP script.
    . . . . . . . . . . . .
     public static $QBREST_HOST = "http://sqlquerybuilder.com/";
     public static $QBREST_KEY = "XXXXXX-XXXX-XXXX-XXXX-XXXXXXXX"; //your API key goes here
    . . . . . . . . . . . .
    
  4. Then press “Add your first model” button, enter the name of your model (the same as you set for $MODEL_NAME field above) and copy XML file created on the step #2.

Now you can send a request to /api/v1/SqlQueryBuilder action when you need to get an SQL statement by JSON query returned by EasyQuery widgets.



That's all for now. Our SQL query builder REST service is totally free at the moment and there always will be a free package even we start charging for using our service. It’s running on Windows Azure cloud service which guarantees 99.99% of service availability - so you should not worry about any connection problems and can totally rely on it.

Feel free to request support if you have any questions regarding EasyQuery widgets or SqlQueryBuilder.com web-service.


Published: 2016-10-28 Last updated: 2017-04-14