Dynamic SQL generation for searching database on different fields
Searching a database is not same as searching using keywords. Database search are applied to an existing table or linked tables. Here the search is applied to many fields and is not restricted to keyword search. The users depending on the knowledge or requirment, will use different combination of fields. For example in a student database search the user may be knowing the class of the student so a selection can be given to the form saying search within class five students. Same way search can be restricted to search for male or female student. This way search can be restricted or expanded by using different combinations available. We will learn how to develop query dynamically based on the selection ( combination ) of the user. We will use PHP to develop the dynamic query and restrict our self to query generation only as common data display process can be applied once query is available.
Search for Pin code and other details of an Indian location by entering keywords and selecting matching suggestion using JQuery autocomplete with database as source.We will start with our student table, it has four fields. We will develop a drop down list for all class to be listed. A radio button selection for male female or Any selection. We will give a mark selection where user can enter minimum or maximum mark. Note that here all options we provided are optional for the user. User can try in any combination or may try with out any combination also.
Here is a demo of the search box how it will appear. On submit you can see how the query is generated based on the inputs. Try with different combination of selection.
Here this form will generate different query based on the selection of the user. You can see the query changes based on the user selection. We will be adding field name and its value to search only when we find the data is available. So before adding the field to the search query we will check the length of the data entered. If the length is more than zero then we assume that the user has selected that option for searching.
For example if class selection is Four then we can check the length of variable $class like this
You can see class field search is applied only if class is selected in the drop down list. Same way we have included other fields to search with checking the length of the variable.
Searching gender fieldWhile searching if we know the student is male or female then we can use that. If we select Male as gender the variable \$sex will have the value of radio button used inside the form. Here is the code to include gender field in search query
Here is the code for form and search query generation. You can download this code with sql dump files at the end of this page.
This article is written by plus2net.com team.
▼ More on Search