Creating complex queries in your web form
- Details
- Category: Programming
- Created on Monday, 11 June 2007 04:28
- Last Updated on Saturday, 15 March 2014 21:26
This article shows a nifty (I hope) way to make the logic part of your webform cleaner.
Creating complex queries in your web form
Introduction
A few days back I was writing a small php script to have a web form to query the inventory database of the hosts that my team monitors. The form had a few checkboxes and a text field to let the user drill down to the hosts that he was interested in.
This article shows a nifty (I hope) way to make the logic part of your webform cleaner.
Version 1
Initially I started with the sql select hostname from hosts
. However,
as the number of elements of the form increased, the sql got messier
and messier. At one stage, this was how my code looked like
? $basesql = "select hostname from hosts"; $sql=""; ? if (isset($mon) and $mon==1) { ? ... ? // some more code to add the appropriate ? // where condition to the basesql ? ... ? }
At this stage, I saw that if I wanted to add more controls on the form, the code would get messier and messier. I sat down and examined if I could take out the common parts and make it into a function. (Needless to say, the thinking part was done when I was at home :-) )
Version 2
I saw a pattern here - I had an if
condition and based on that if
condition, I was extending the where
part in the sql. This suggested
that the part which extends the sql can easily be made into a
function. Something like this
function addclausetosql($basesql,&$sql,$clause) { if ( empty($sql)) { // No clause has yet been appended to the basequery, that is why // the $sql is still empty $sql .= $basesql . " where " . $clause ." "; } else { $sql .= " and " . $clause ." "; } }
and the part which invokes this function becomes something like this
// I am using input filter to automatically clean my $_GET and $_POST // xxx is something related to the secret sauce that we use $mon=$_GET['mon'];$notxxx=$_GET['notxxx'];$hwild=$_GET['hwild']; $basesql = "select hostname,source from host "; $sql= =""; if (isset($mon) and $mon==1) { addclausetosql ($basesql, $sql, " (FIND_IN_SET('mon',source)>0) " ); } if (isset($notmon) and $notmon==1) { addclausetosql($basesql,$sql," (FIND_IN_SET('mon',source)<=0) " ); } if (isset($notxxx) and $notxxx==1) { addclausetosql ($basesql,$sql," (FIND_IN_SET('xxx',source)<=0) " ); } if (!empty($hwild)) { // I should make sure $hwild does not contain bad sql, but // I don't have anything ready made to slap it on and I am // running out of time addclausetosql($basesql,$sql, " hostname like \"$hwild\""); } empty($sql) ? ($sql=$basesql):(1); // Now you have the sql query you need to run and you can execute it ... ... ... //this part prints out the result
As you can see, the code becomes less messier now. Adding new
controls (i.e. checkboxes, select, textbox) on the webform now simply
means adding one more if
condition with an appropriate clause to be
added to the base sql.
Version Future
(or this is the part which is left as an exercise to the reader)
The function addclausetosql
takes a reference to $sql
as an argument
and modifies $sql
. This is almost like changing a global variable,
and it might trip me at a later date. However, the name of the
function should give me a clue to what is happening.
It might be a good idea to wrap the function into a class. The class
would have member variables called $basesql
and $sql
and it would have
a member function called addclausetosql
. That will be a much cleaner
implementation.
Learnings
- Recognize mess in your code
- Think hard and clean up mess