Categories
Contact Doug!
Learn About Doug!
View Doug Boude's online resume
updated 11/18/2009

View Doug Boude's profile on LinkedIn
Link to me!

Follow Doug Boude on Twitter
Follow me!

Be Doug's friend on Facebook
Befriend me!
(I promise not to follow you home)
OO Lexicon
Chat with Doug!
Recent Entries
You may also be interested in...
Web Hosting

<< May, 2009 >>
SMTWTFS
12
3456789
10111213141516
17181920212223
24252627282930
31
Search Blog

Recent Comments
Re: Disappearing IE Popup Window During Save/Open Dialog (by LZ at 4/20 7:58 AM)
Re: Create Dynamic WHERE Clauses in PHP (by pooja at 3/20 7:29 AM)
Re: Just What IS a 'Service Layer', Anyway? (by EugenK at 3/07 7:56 PM)
Re: Using Google as your CF Mail Server (by 5starwebteam.com at 2/25 1:27 AM)
Re: Why Provide for Service layer objects in CFWheels? (by Steven Benjamin at 1/25 11:43 AM)
Re: What is an 'Advanced' Coldfusion Developer? (by ColdFusion Developer at 12/24 5:14 AM)
Re: Equivalent of SQL "TOP X" in Oracle (by Ashenafi Desalegn at 12/06 5:29 AM)
Re: PHP Export to Excel Snippet (by serene at 12/05 1:44 AM)
Re: Just What Is 'Application Logic', Anyway? (by Arif at 11/13 8:06 AM)
Re: Hosts File Changes Not Acknowledged on Vista 64 (by Aaron at 10/22 2:31 PM)
Re: PHP Export to Excel Snippet (by Jafar Shah at 10/10 4:28 AM)
Re: Viewing Option Text (in IE7) that's Wider than the Select List (by Chenelle S at 10/04 12:53 PM)
Re: PHP Export to Excel Snippet (by Kilo at 9/26 5:20 PM)
Re: Porting Coldfusion Code to Mura (by tariq at 9/03 9:51 AM)
Re: Just What IS a 'Service Layer', Anyway? (by James at 8/27 4:06 PM)
Re: Calculating Business Hours (by helen at 8/14 2:54 AM)
Re: What IS 'Business Logic', Anyway? (by dougboude at 8/06 11:30 AM)
Re: What IS 'Business Logic', Anyway? (by Adrianne at 8/06 10:29 AM)
Re: Family Law: The Weapon of Choice for Woman Scorned (by dougboude at 8/04 4:39 PM)
Re: Family Law: The Weapon of Choice for Woman Scorned (by Lola LB at 8/04 7:43 AM)
Archives
Photo Albums
Funnies (5)
Family (3)
RSS

Powered by
BlogCFM v1.11

21 May 2009
Create Dynamic WHERE Clauses in PHP

At times it is necessary to dynamically create delimited lists of items, such as in the WHERE clause of SQL queries. For instance, perhaps our query could have from one to N different OR statements in the WHERE clause based on selections a user makes on a search form, something like

"select userid, firstname, lastname from usertable

WHERE (rank >700 AND rank <1000) OR (rank >200 AND rank < 400) OR (lastname LIKE 'ar%') OR ...."

Looping over items and knowing when to insert a delimiter and when not to takes some extra coding, because you have to track if this is the first or last item in the list or array. Instead of worrying about tracking first or last items, what I do is simply insert the items I need to delimit into an array, and then dump them all out to a properly delimited statement by leveraging the 'implode' function.

Here's a snippet of code I use to create a dynamic WHERE clause for a sql query based on filter criteria submitted via a form. In this example, I am processing a submitted filter criteria named 'rankfilter' to build the WHERE clause for my query:

$Query = "SELECT userid,firstname,lastname,username,password FROM UserTable WHERE (";//start my query string...
$aRanks = split(",",$_GET['rankfilter']);//turn the submitted list of rank options into an array...
$aORs = array();//create an empty array to hold the individual criteria to be OR'd...
foreach($aRanks as $thisrank){
 switch($thisrank){
  case "core":
   array_push($aORs,'(rank >=700 AND rank <100000)');
   break;
  case "all":
   array_push($aORs,'(rank >=0 AND rank <100000)');
   break;
  case "1":
   array_push($aORs,'(rank >=500 AND rank <700)');
   break; 
  case "2":
   array_push($aORs,'(rank >=400 AND rank <500)');
   break; 
  case "3":
   array_push($aORs,'(rank >=300 AND rank <400)');
   break;
  case "4":
   array_push($aORs,'(rank >=0 AND rank <300)');
   break;   
 }
}

$Query .= implode(" OR ",$aORs); //transform the array of criteria into a properly delimited WHERE clause...

$Query .= ") ORDER BY lastname, firstname";

$Result=mysql_db_query($DBName,$Query,$Link);//execute the query

Don't focus so much on the switch/case statement as you do on the fact that every time I create a new OR statement, I'm pushing it onto my $aORs array. After I've created all of the needed ORs, I invoke the 'implode' function and spit out a nice WHERE clause where each statement is separated by " OR ".

Resulting SQL:

SELECT userid,firstname,lastname,username,password
FROM UserTable
WHERE ((rank >=700 AND rank <100000) OR (rank >=500 AND rank <700) OR (rank >=300 AND rank <400))
ORDER BY lastname, firstname


You can use this same approach any time you need to create some kind of delimited list of values. All you have to do is replace the first parameter in the 'implode' function with whatever you need the delimiter to be.

Hope this helps.

Posted by dougboude at 11:40 AM | PRINT THIS POST! | Link | 4 comments



20 May 2009
Basic Ajax Select List Filter in PHP

I am a ColdFusion guy, without a doubt. At my new job, however, I have inherited the maintenance and enhancement duties of a large PHP application as well. One of the enhancements I was asked to make was to produce a select list of names that could be filtered on the fly as the user typed into a text box. Being somewhat new to PHP and having had to figure out a good approach to creating this feature, I thought I'd share it in case it helps someone else.

Picture if you will, a select list filled with names and immediately below it a text box for filtering that list. Better yet, here's what mine looks like:

 select list with ajax name filter


As the user begins typing in the text box, the select list begins to be filtered, re-filtering as each letter is added or removed.

I'm using the Prototype javascript library to perform the ajax calls, and a PHP script I call "adminAjax.php" that listens for and responds to those ajax calls. First off then, the HTML and JS that sets up the user interface:

<div id="namelist" name="namelist" style="height:350px;width:200px;"></div>
<div>
 Last Name: <input type="text" id="fltName" name="fltName" size="25" />
</div>

<script>
 Event.observe(window, 'load', function() {
  Event.observe('fltName', 'keyup', function(e){filterByName(e);});
  filterByName('');//this initially populates the select list with ALL names...
 });
</script>

 

basically, I have one div that will hold the select list ("namelist"), and some js that, on page load, will bind the 'filterByName' function to the keyup event on the 'fltName' textbox. So, with every character entered or removed from that textbox, the current value will be passed to the filterByName function. Here's the filterByName function:

function filterByName(strname){
 var params = new Hash();
 params.set('namestring',strname);
 params.set('actionval','getSelect');
 new Ajax.Updater('namelist','adminAjax.php',{parameters:params,method:'get'});
}

 

Okay, now on to adminAjax.php.

I'm using this template exclusively for ajax responses, so what I did was to create a switch/case statement that routes to the appropriate function based on the incoming 'actionval' parameter. Important to note: in my case, I chose to create the entire select list on this end and then return the completed HTML. The Updater that made the Ajax call will then place the returned HTML into the target div for us. Here's the code:

<?
 //make sure we have a default value for the actionval parameter...
 if(!isset($_GET['actionval'])){$_GET['actionval']='none';}
 
 switch ($_GET['actionval']){
  case 'getSelect':
   $retval = getSelect();
   echo $retval;
   break;
  case 'some_other_action':
   $retval = bogusCall($_GET['someParam']);
   echo $retval;
   break;
 }

 //end of our switch router! Now for the functions that feed it...

 function getSelect(){
  include("settings.php");
  $Query = "SELECT userid,firstname,lastname from myTable ";
  $Query .= " where firstname <> '' and lastname <> '' ";
  
  //filtering on lastname/firstname...?
  if(isset($_GET['namestring']) && strlen($_GET['namestring'])>0){
   $aStr = split(",",$_GET['namestring']);
   $len = strlen(trim($aStr[0]));
   $flen = (count($aStr) > 1)?strlen(trim($aStr[1])):0;
   $Query .= "AND SUBSTR(lastname,1,".$len.") = '".trim($aStr[0])."' ";
   if($flen > 0){
    $Query .= "AND SUBSTR(firstname,1,".$flen.") = '".trim($aStr[1])."' ";
   }
  }
  $Query .= "order by lastname,firstname";

         $Result=mysql_db_query($DBName,$Query,$Link);

         if(mysql_numrows($Result) == 0){
          $strField = "<br><br>No Records Found. Please Change Filter Values and Try Again.";
         } else {//we have some records...let's build the select list HTML.
   $strField ='<select name="selUsers" id="selUsers" size="20" style="width:200px;" >';
   while($row = mysql_fetch_assoc($Result)){
    $strField .= '<option value=\''.$row['userid'].'\'>'.$row['lastname'].', '.$row['firstname'].'</option>';
   }
   $strField .='</select>';
         }
         mysql_close();
  return $strField;  
 }

That's it!

You may be thinking what I was originally thinking, something along the lines of "wouldn't it just be faster to have given the page a json representation of all the names, then used JS to filter it on the client side?" Well, the answer (at least in my case) is NOPE. I am working with about 3,000 names, and my first approach was to do it using the JSON data. This worked fine in Firefox, but IE...of course it ran painfully slow. Using the Ajax approach and just creating a new select list as the filter value changes works surprisingly fast in all browsers!

Hope this helps.

If you would like a good set of starter snippets, you can grab the zip file here.

 

 

Posted by dougboude at 2:02 PM | PRINT THIS POST! | Link | 2 comments