2. PHPRunner Tips and Tricks
  3. Filter list on demand with dropdown
This topic is locked

Filter list on demand with dropdown

 		8/14/2010 11:32:02 AM
PHPRunner Tips and Tricks
F
 FunkDaddy author

OK. So, this post elaborates on the tip provided in he online manual found here: http://xlinesoft.com/phprunner/docs/add_dropdown_list_box_with_values_for_search.htm
There are several other posts in the main PHPR forums that talk about this "feature", but I decided to create a tip & trick entry to help clarify how to use it properly.
When I first used this sample code I was a litle confused because I needed to filter a list via a dropdown, but the values of the dropdown needed to be pulled from a different list and used as the values to filter the current list where I needed the dropdown filter box added. The online manual example seems to assume that you are populating the dropdown with values from the current list. This would be fine if the list had unique values... it would act as a defacto "quick" search box without having to press the actual search button to process the request (since the dropdown triggers the action onChange). However, if the list contained records that had columns with similar values (such as "Product_Selected") then the dropdown would populate with those values several times, showing duplicates and making an inordinately long dropdown list).
Anyhow, enough background info, heres the scenario I needed to address:

  1. I had a table called "Products_tbl" which contained columns "ProductID, Product_Name, etc"
  2. I had a table called "Orders_tbl" which contained columns called "OrderID, Selected_Product, etc"
  3. I wanted to view the "Orders_tbl" in a list and add a dropdown box to that list page that would pull the values from "Products_tbl" (show them in the dropdown used to filter the list) and use it to filter the "Orders_tbl_list" page on-the-fly.
  4. The "Orders_tbl" used the "Selected_Product" column/field to store the "ProductID" which was provided via a lookup box pulling info from "Product_tbl"... thus, the names of the columns were different between both tables, yet they were tied to each other nonetheless by the ProductID key. This isn't all that unusual or confusing... I'm just pointing this out so you understand why I wrote the code as shown below:


//create dropdown box

$str = "";

$str.= "<select onchange=\"window.location.href=this.options[this.selectedIndex].value;\"><option value=\"\">Please select</option>";
//select values from database

global $conn;

$strSQL = "SELECT ProductID, Product_Name FROM products_tbl";

$rs = db_query($strSQL,$conn);

while ($data = db_fetch_array($rs))

$str.="<option value=\"orders_tbl_list.php?a=search&value=1&SearchFor=".$data["ProductID"]."&SearchOption=Contains&SearchField=Selected_Product\">".$data["Product_Name"]."</option>";
$str.="</select>";

echo $str;


I know there isn't anything genius or magical about this tip... but I believe it helps clarify and simplify things a little better than the online manual, because it shows how to work with data in 2 different lists instead of assuming use of the same list being filtered for dropdown values.
Cheers,
romaldus 8/18/2010



OK. So, this post elaborates on the tip provided in he online manual found here: http://xlinesoft.com/phprunner/docs/add_dropdown_list_box_with_values_for_search.htm
...........


BTW.. could you please give a screenshot as an example of generated apps using this custom code?
F
 		FunkDaddy author 9/6/2010

Romaldus, I can't share the screenshot of the app because I'm developing in "stealth mode". Realistically, there isn't a need to view the screenshot... all you will see differently on the screen is an added dropdown box.. .whenever you change the value the list refreshes and loads the new filtered list value.
By the way... I forgot to mention to those reading this post that you must select the fields you wish to be able to filter on the list page by checking the "search" checkbox in PHPRunner "Fields" tab... otherwise, the filter cannot use that field when searching/filtering!
Cheers,
R
 		Ricky001 9/27/2010



OK. So, this post elaborates on the tip provided in he online manual found here: http://xlinesoft.com/phprunner/docs/add_dropdown_list_box_with_values_for_search.htm
There are several other posts in the main PHPR forums that talk about this "feature", but I decided to create a tip & trick entry to help clarify how to use it properly.
When I first used this sample code I was a litle confused because I needed to filter a list via a dropdown, but the values of the dropdown needed to be pulled from a different list and used as the values to filter the current list where I needed the dropdown filter box added. The online manual example seems to assume that you are populating the dropdown with values from the current list. This would be fine if the list had unique values... it would act as a defacto "quick" search box without having to press the actual search button to process the request (since the dropdown triggers the action onChange). However, if the list contained records that had columns with similar values (such as "Product_Selected") then the dropdown would populate with those values several times, showing duplicates and making an inordinately long dropdown list).
Anyhow, enough background info, heres the scenario I needed to address:

  1. I had a table called "Products_tbl" which contained columns "ProductID, Product_Name, etc"
  2. I had a table called "Orders_tbl" which contained columns called "OrderID, Selected_Product, etc"
  3. I wanted to view the "Orders_tbl" in a list and add a dropdown box to that list page that would pull the values from "Products_tbl" (show them in the dropdown used to filter the list) and use it to filter the "Orders_tbl_list" page on-the-fly.
  4. The "Orders_tbl" used the "Selected_Product" column/field to store the "ProductID" which was provided via a lookup box pulling info from "Product_tbl"... thus, the names of the columns were different between both tables, yet they were tied to each other nonetheless by the ProductID key. This isn't all that unusual or confusing... I'm just pointing this out so you understand why I wrote the code as shown below:


//create dropdown box

$str = "";

$str.= "<select onchange=\"window.location.href=this.options[this.selectedIndex].value;\"><option value=\"\">Please select</option>";
//select values from database

global $conn;

$strSQL = "SELECT ProductID, Product_Name FROM products_tbl";

$rs = db_query($strSQL,$conn);

while ($data = db_fetch_array($rs))

$str.="<option value=\"orders_tbl_list.php?a=search&value=1&SearchFor=".$data["ProductID"]."&SearchOption=Contains&SearchField=Selected_Product\">".$data["Product_Name"]."</option>";
$str.="</select>";

echo $str;


I know there isn't anything genius or magical about this tip... but I believe it helps clarify and simplify things a little better than the online manual, because it shows how to work with data in 2 different lists instead of assuming use of the same list being filtered for dropdown values.
Cheers,
F
 		FunkDaddy author 3/30/2011

NEW AND IMPROVED VERSION BY AUTHOR!
So I'm revisiting my original post because I needed to improve the functionality offered when filtering list pages with a dropdown field (see the 1st thread in the post at top of page).
The code I provided above works great, however, there is one small problem with it: when you select a value from the dropdown to filter the list page it doesn't keep the value you selected once the page refreshes to display the filtered values. This can be annoying to a end-user, since often times they may not remember which values they selected from the dropdown... especially if the filtered results return nothing.
Anyhyow, here is how I improved on the code I originally posted to give you this extra functionality (remembering the selected value from dropdown after page reloads):

  1. Assign an id attribute to the select element (Ex:my_dropdown).
  2. Change the url of the window.location.href javascript associated with the onchange event of the select element.
  3. Notice I broke that url into 3 parts using + signs to connect them as one.
  4. Change the $str.= after the while loop by removing the query string url we originally built in my original post. We've already moved it to another location in step 2 (no need to repeat it here).
  5. Add new portion of code into the page... see code below.. the "//Insert PHP variable into a javascript..." portion.



//create dropdown box

$str = "";

$str.= "<select id=\"my_dropdown\" onchange=\"

window.location.href='orders_tbl_list.php?a=search&value=1&SearchFor=' + this.options[this.selectedIndex].value +'&SearchOption=Contains&SearchField=ProductID';

\"><option value=\"\">Please select</option>";
//select values from database

global $conn;

$strSQL = "SELECT ProductID, Product_Name FROM products_tbl";

$rs = db_query($strSQL,$conn);

while ($data = db_fetch_array($rs))

$str.="<option value=".$data["ProductID"].">".$data["Product_Name"]."</option>";
$str.="</select>";

echo $str;
//Insert PHP variable into a javascript value... which we later use in the JS onLoad event to set the dropdown box again so once page refreshes we don't lose the value that was selected in the first place (so we know what we selected)!
echo "<script>window.SearchFor = '" . $_GET["SearchFor"]. "';</script>";


6. On the same list page where you've inserted the dropdown box using the php snippet method (where you are doing the filtering) you'll add the following code to JavascScript OnLoad event:



//Grab value of dropdown box that filters schools on list page

//We want to set the value of dropdown so once its selected and page refreshes we know what value we had selected

//The SearchFor variable is set by php snippet in list page

document.getElementById("my_dropdown").value = SearchFor;


Voila! You are done. Now, when users select the dropdown to filter a list page dynamically we are able to display the selected value in the dropdown box after the page refreshes to display the filtered values!
Cheers,
E
 		electromotive 10/18/2011

Keeping the value after you refresh the page...
I did this another way, inserting the selected attribute into the option.



while ($data = db_fetch_array($rs)) {

if ($data["ProductID"]==$_SESSION["CurrentProduct"]) $selected = " selected"; else $selected = " ";

$str.="<option value=\"orders_tbl_list.php?a=search&value=1&SearchFor=".$data["ProductID"]."&SearchOption=Contains&SearchField=Selected_Product\"".$select.">".$data["Product_Name"]."</option>";

}