Filter or External search

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.

Filter or External search

Postby boribon2 » Wed Jan 10, 2018 6:40 pm

I have a table two DATE type fields in it: "from", "to" and an INT type field: "price".
Let's some records from the table

id itemid from to price
1 1 2017/12/01 2017/12/08 $100
2 1 2017/12/09 2017/12/16 $98
3 1 2017/12/17 2017/12/31 $99
4 2 2017/12/01 2017/12/07 $8

I'd like to filter records this way: I give a date in an own field on the top, click on a button and I get those records which are valid on the given day. For example I give date 2017/12/05 and get 2. and 4. records, because 2017/12/05 is between 'from' and 'to' in the 2. and in the 4. records. How can I customize searching this way?
boribon2
 
Posts: 20
Joined: Sat May 23, 2015 10:31 pm

Re: Filter or External search

Postby kirondedshem » Wed Jan 10, 2018 7:21 pm

Since all the serach settings seem to be tied to particular field, yet yours wants to be considered in relation to two fields. Regardless, you can always put your custom search condition in record_selecting, now all you have to do is have a search filed on the form which will give you the value
You need to add acustom field which will do the serach for you, Ive achived soemthing similar like this, maybe it will also work for you.

1. Add a custom field called "select_date",
IMPORTANT:leave its sql expression empty(dont put anything)
2.set the following
- edit tage to use text box, tick use datatime picker
- view tag set format = date only(no time object)
-allow this controll onto the list page by ticking it in field settings
-enable extended serach for this field
IMPORTANT: Ensure in field settings->advanced serach page->Search Op1 1 is set to "=", as this affects how the serach filter is generated.
IMPORTANT: If possible dont enable any advanced serches for from and to fields, as this will mess up our condition
NOTE:By now you should be able to open up the list page and see select date both in extended search as well as a column on list page

3. Now we need to hide the field from bieng in the list page table, but still allow it to be in extended search. Pu the following code in specified event
// Page Data Rendering event
function Page_DataRendering(&$header) {
// Example:
//$header = "your header";
$this->select_date->Visible = FALSE;
}

4. Now sice we need to handle the serach manually we need to save every search parameter passed to us in a session, so put the following code.
NOTE: phpmaker list pages always pass the serach parameters as get variables so we are going to use that.
// Page Load event
function Page_Load() {
//echo "Page Load";

if(isset($_GET["x_select_date"]) && ($_GET["x_select_date"] != null) )
{
$_SESSION["current_select_date_value"] = $_GET["x_select_date"];
}

}

NOTE:By now we should be able to save all serach parameters everytime they are passed from advanced serach fields

5. Now we need to add out own search filter in record_selecting event based on the value passed here.
NOTE:Since this field has been ticked for advanced serach there will always be a serach condition formulated for it automatically by phpmaker in record selecting, so we have to remove that first before we apply ours, but still leave the other seraches passed if any.So put code like this.

// Recordset Selecting event
function Recordset_Selecting(&$filter) {
// Enter your code here

var_dump("original:".$filter);
//check if we have a date serach active
if(isset($_SESSION["current_select_date_value"]))
{
$current_select_date = $_SESSION["current_select_date_value"];
//formulate the expected serach applied for this field
$expected_date_condition = "'' = '$current_select_date'";
//formulate our own search which does the between from and to.
$our_date_condition = " ( from < '$current_select_date' and to > '$current_select_date' ) ";

//now replace thier condition with our condition
$filter = str_ireplace($expected_date_condition,$our_date_condition,$filter);

var_dump("editied:".$filter);

}


}


Now the filter you want should be able to apply atleast, or at the very least this approach will get you what you want, so just play with it

HINT: You dont have to use a custom field which also mean you need to diable the automated serach operator before putting your. You can alternatively draw the search field on your own, using either jquery in startup script or adding it in the header in page_render. and just save its content in a session and formulate your condition.
kirondedshem
 
Posts: 330
Joined: Sat Jan 28, 2017 7:13 pm

Re: Filter or External search

Postby boribon2 » Wed Jan 10, 2018 9:14 pm

thank you so much for your quick and detailed answer. That's exactly what I want. It works fine.
boribon2
 
Posts: 20
Joined: Sat May 23, 2015 10:31 pm


Return to General Discussion (PHPMaker)