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... $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
$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;
}
}
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:
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.
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.