Filter or External search

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
boribon2
User
Posts: 75
Location: Hungary

Filter or External search

Post by boribon2 »

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?


kirondedshem
User
Posts: 642

Post by kirondedshem »

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

  2. edit tage to use text box, tick use datatime picker

  3. 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

  4. 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;
    }

  5. 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

  1. 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.


boribon2
User
Posts: 75
Location: Hungary

Post by boribon2 »

thank you so much for your quick and detailed answer. That's exactly what I want. It works fine.


bobmulder5555
User
Posts: 60

Post by bobmulder5555 »

A bit overdue, but what also works like a charm is to use extended search and set the one date to ">=" instead of "=" and the other one to "<" or "<=". You can do this in your field setup in the Advanced Search Page column. I just discovered the extended search capabilities, and I think they're brilliant. You have just ONE search button (no user confusion!), you click and your walhalla of search possibilities will open up for the user.

In 2018 you need to have the dates in listview or else they don't show up if you click extended search open.
So, if you don't want to see them in your LV, but be able to search on them, set all the items like this:
$this->SearchFromDate->ViewValue = ""; in RowRendered, where SearchFromDate is the fieldname in your Table.

I just did this for a table that had just one date. Make a view and select the date as many times you need. Here I selected the same date item 3 times, so you can search for an exact date AND a time span.
Select tbl3_ri_roosteritem.RI_FullDateYYYYMMDD AS RI_FullDateYYYYMMDD,tbl3_ri_roosteritem.RI_FullDateYYYYMMDD AS RI_SearchFromDate,tbl3_ri_roosteritem.RI_FullDateYYYYMMDD AS RI_SearchToDate from tbl3_ri_roosteritem. If you use PHPMaker or a GUI-tool like Navicat to create views, you just edit the SQL statement that is created from your graphical builder.

A view doesn't take up any space, it's a MySQL stored procedure and the data gets assembled from one or more tables on the fly when a user asks for it.


bobmulder5555
User
Posts: 60

Post by bobmulder5555 »

O yeah, almost forgot: if you don't want to see these extra date fields in your listview page, you also want to get rid of the column headers,

You do this in Table_Specific/List Page/Page_Data_Rendering with
$GLOBALS["vw194_aanbod"]->RI_SearchFromDate->Visible = FALSE;
$GLOBALS["vw194_aanbod"]->RI_SearchToDate->Visible = FALSE;
$GLOBALS["vw194_aanbod"]->RI_FullDateYYYYMMDD->Visible = FALSE;
where vw194_aanbod is the view I had created.

See viewtopic.php?f=4&t=33678&p=95617&hilit=hide+column+header#p95617

The other spot I mentioned to turn off the individual fields is Table_Specific/Common/Row_Rendered BTW


boribon2
User
Posts: 75
Location: Hungary

Post by boribon2 »

thank you.


Post Reply