INNER JOIN-arguments based on dropdown selection

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
planet-fisherman
User
Posts: 11

INNER JOIN-arguments based on dropdown selection

Post by planet-fisherman »

Hi,

I'm fairly new to PHPMaker and I'm having the feeling that what I'm doing can be achived much easier but I just cannot figure out a way to do it.
I've searched the forum for hours now...

Firstly I couldn't find a way of filtering with looked up options in a drop down menu. This is why I'm using the $_GET[]-arguments from a dropdown menu I created myself.
My biggest challenge is how to include these selected opions in the query. Including it in the filter (WHERE...) would be easy but how to change the ON argument of the INNER JOIN based on user selection?

Currently I'm running an INNER JOIN query in a custom file like this:
$sql = "SELECT f.ArticelID, f.CustomerPos FROM DTA_AUFPOS f LEFT JOIN DTA_IST_AGANG s ON f.Barcode=s.Barcode AND s.Agang='";
$sql .= $GET['Agang'] . "' WHERE s.ROWID is NULL AND f.ProjectID=";
$sql .= $
GET['ProjectID'] . " AND f.OrderID=";
$sql .= $_GET['OrderID'].";

and then print it with
echo $db->ExecuteHtml ...

But in this way it seems I miss out on a lot of further funcionality of the program like formating the results, search options etc. which I need for further development.

Thanking you for any support in this.


kirondedshem
User
Posts: 642

Post by kirondedshem »

HINT: lookign at your query, if you can accept cahanging your sql a bit, you can still use as a view or custom view, ie leave all get dynamic parameter filter in the where clause. eg if you can write your sql like so

CHANGE THIS
"SELECT f.ArticelID, f.CustomerPos FROM DTA_AUFPOS f LEFT JOIN DTA_IST_AGANG s ON f.Barcode=s.Barcode AND s.Agang='".= $GET['Agang'] . "' WHERE s.ROWID is NULL AND f.ProjectID=".$GET['ProjectID'] . " AND f.OrderID=".$_GET['OrderID'];

TO THIS
"SELECT f.ArticelID, f.CustomerPos FROM DTA_AUFPOS f LEFT JOIN DTA_IST_AGANG s ON f.Barcode=s.Barcode AND s.ROWID is NULL WHERE s.Agang = '".$GET['Agang']."' AND f.ProjectID=".$GET['ProjectID'] . " AND f.OrderID=".$_GET['OrderID'];

NOTICE how all values after the where are got from the $_GET,so this kind of query can be used to create a view or a custom view, after that set each of the where parameters as extended filter which serach set to "=" and you should have what you want.

/////////////////////////BUT BUT, IF still you wnat the approach of having a dynamic join depending on your get parameters////////////////////////////

Its kind of tricky, But ive done soemthing similar where I had a custom field on a list, but whose sql expression changes dynamically, for this you need to use page_load to access the sql query that was about to be run, then modify it to your need (but ensure to still leave all field names as known in field settings in that query),for example

  1. Your case requires all the sql query to be visible so you can modify it, so craete a custom view of your query with default conditions to act as palce holders which you will need to modify, for example your query can be like below

SELECT f.ArticelID, f.CustomerPos FROM DTA_AUFPOS f LEFT JOIN DTA_IST_AGANG s ON f.Barcode=s.Barcode AND s.Agang='MY-Agang' WHERE s.ROWID is NULL AND f.ProjectID= 'MY-ProjectID' AND f.OrderID= 'MY-OrderID'

NOTICE IVE named all parameters we need to midify as MY-Agang, MY-ProjectID, MY-OrderID

  1. Set this up in phpmaker and enable all three field to use extended filter or draw you own filters

  2. We need to capture all parameters passed in each of the extended filters so we can modify our sql in page load, so save each of the filters here
    NOTE:phpmaker list pages always pass thier parameters using GET so we capture them there

function Page_Load()
{
//save all the parameters in asession to help resume later
if(isset($GET["x_Agang"]) && isset($GET["x_ProjectID"]) && isset($GET["x_OrderID"]))
{
$
SESSION["current_Agang"] = $GET["x_Agang"];
$
SESSION["current_ProjectID"] = $GET["x_ProjectID"];
$
SESSION["current_OrderID"] = $_GET["x_OrderID"];
}

}

  1. You can see and edit the sql being used on list page of your custom view, go to page_load of list and add this so page_load looks like so
    NOTE:we need to replace the place holders in basic query then save it back

function Page_Load()
{
//save all the parameters in asession to help resume later

if(isset($_GET["x_Agang"]) && isset($_GET["x_ProjectID"]) && isset($_GET["x_OrderID"]))
{
	$_SESSION["current_Agang"] = $_GET["x_Agang"];	
    $_SESSION["current_ProjectID"] = $_GET["x_ProjectID"];	
	$_SESSION["current_OrderID"] = $_GET["x_OrderID"];	
}

//we always pick them from session only
if(isset($_SESSION["current_Agang"]) && isset($_SESSION["current_ProjectID"]) && isset($_SESSION["current_OrderID"]))
{
	
	//get the sql select statement used
$sql = $this->getSqlSelect();
var_dump("<br>before".$sql);

//edit the sql to add values for each place holder

//replace MY-Agang
$sql = str_ireplace("MY-Agang",ew_AdjustSql($_SESSION["current_Agang"]),$sql);
$sql = str_ireplace("MY-ProjectID",ew_AdjustSql($_SESSION["current_ProjectID"]),$sql);
$sql = str_ireplace("MY-OrderID",ew_AdjustSql($_SESSION["current_OrderID"]),$sql);

var_dump("<br>after".$sql);

//save the qsl back
$this->setSqlSelect($sql);
	
}

}

  1. For you case since all extended filters are only used to edit your sql, you dont need them to be added in awhere clause so disable all default phpmaker generated searching in by code below

function Page_Selecting(&$filter) {
// Enter your code here

//dont apply filter here
$filter = "";

}

NOTE: The whole idea is to change the sql dynamically, Ive only used it on default list pages so I cant gaurantee its behaviour of reports of type cross tab or detai summary or even charts, but the approcah is something like that


planet-fisherman
User
Posts: 11

Post by planet-fisherman »

kirondedshem wrote:
CHANGE THIS
"SELECT f.ArticelID, f.CustomerPos FROM DTA_AUFPOS f LEFT JOIN DTA_IST_AGANG s
ON f.Barcode=s.Barcode AND s.Agang='".= $GET['Agang'] . "' WHERE s.ROWID
is NULL AND f.ProjectID=".$
GET['ProjectID'] . " AND
f.OrderID=".$_GET['OrderID'];

TO THIS
"SELECT f.ArticelID, f.CustomerPos FROM DTA_AUFPOS f LEFT JOIN DTA_IST_AGANG s
ON f.Barcode=s.Barcode AND s.ROWID is NULL WHERE s.Agang =
'".$GET['Agang']."' AND f.ProjectID=".$GET['ProjectID'] . " AND
f.OrderID=".$_GET['OrderID'];

Unfortunately I cannot find a way of changing the SQL syntax and still end up with the same results.
So I followed your second approach.
PHPMaker responds with exceptional errors if you try to save a string as filter in a integer field like here: f.ProjectID='MY-ProjectID'
But I figured I might just as well replace f.ProjectID=1234.

Where I'm stuck right now is with the filtering.

  1. Set this up in phpmaker and enable all three field to use extended filter or draw
    you own filters

  2. We need to capture all parameters passed in each of the extended filters so we can
    modify our sql in page load, so save each of the filters here
    NOTE:phpmaker list pages always pass thier parameters using GET so we capture them
    there

Can you explain these steps further? I cannot find the right place to add the "extended filter". If I set a filter in Table options => General => Filter it does take effect but it's not made part of the sql-query that I could modify.
You are right about extended search criterias. They are always passed via GET but again they also don't appear in the
$sql = $this->getSqlSelect();
These filters must be applied in a later step and therefore cannot be modified as you proposed.
When I var_dump() I always end up with the basic SELECT... INNER JOIN but no WHERE-statements.

Thanks again for your help.


kirondedshem
User
Posts: 642

Post by kirondedshem »

  1. Set this up in phpmaker and enable all three field to use extended filter or draw

    If I set a filter in Table options => General => Filter it does take effect but it's not made part of the sql-query that I could modify.

    DONT SET IT AT TABLE LEVEL as this is not what you want, read about table setting to know what this does exactly, so diable it if you had it set

HINT:Read about setting up extended filters in help menu to know more but its something like
Eg To setup extended filter on ProjectID
1.click on the report->select the fields tab(to dispaly field settting of all fields in that report)

  1. click on "ProjectID" from list->scroll to the left untill you find group of columns with heding "Report Page"
  2. You will see a column containg check boxes labeld "Ext. Filter"->ENABLE THIS OPTION(tick box in thta column in for the row cotaining "ProjectID" field)
  3. regenerate all files, repoeat that for all fields you want to have a separate inupt serach box for

HINT:WHILE in field setting of a given field You can play with field settings (located at the bottom right section)->Extended filter->Display as(set it as checkbox,drop don,text) to make it alookup to allow users to pick from possible values of that field based on this report.

  1. We need to capture all parameters passed in each of the extended filters so we can
    modify our sql in page load, so save each of the filters here
    #They are always passed via GET but again they also don't appear in the
    #$sql = $this->getSqlSelect();
    #These filters must be applied in a later step and therefore cannot be modified as you proposed.
    #When I var_dump() I always end up with the basic SELECT... INNER JOIN but no WHERE-statements.

Yes, What we are doiong is to modify the DEFAULT select having jions to change the place holders of those jions to make them jion like we want.
SO unless your default sql statement also has where clause in it we also replace its place holders for the where clause, BUT if not then we are not modifyng the whree clause here (we do it in page_selecting instead)

HINT:The where clause is concatenated during Page_Selecting event, so if you have put some values in an extended filter and you ver_duump($filter) in the event you should see the sql conditions for the where clasue formulated for those specific fields.
So you can add your own conditions or delete there conditions(which is what am doing) or even replace them with yours if you so wish to (if your default sql in page load does not have a where clause then you need to add your respective filters here).
NOTE:Phpmaker will get the where clause defined in page_selecting and concatenate it ont the default sql select to form a where condition for automatically.

SO if possible first work on making the jions to cahnge dynamically and ensure that works before anything else


sangnandar
User
Posts: 980

Post by sangnandar »

Supposed you have MyField1 field and enable extended search on that field.
The ext search value is accessible through
$this->MyField1->AdvancedSearch->SearchValue;

For example,
Page_DataRendering()
if ($this->MyField1->AdvancedSearch->SearchValue == ... )

Didn't tried but pretty confident that it can also work on Recordset_Searching().


kirondedshem
User
Posts: 642

Post by kirondedshem »

@ sangnandar
The ext search value is accessible through
$this->MyField1->AdvancedSearch->SearchValue;
this is true

Didn't tried but pretty confident that it can also work on Recordset_Searching().
For some reason this is not true, from what I see order or execution is
page_load->record_Selecting->page_render etc etc.

So you only have page_load event if you want to change the default sql coz after that it dircetly goes to record_selecting.
But for reason the $this->filed_name->SearchValue is not yet loaded in page_load event, thats why I have to get them myself from the GET parameters manually coz I need them here and yet page_render will be too late make sql adjustments.

Try to var_dump($this->filed_name->SearchValue) in page_load event, coz am sure it wont be there unless they've made some adjustments as of recent


planet-fisherman
User
Posts: 11

Post by planet-fisherman »

Sorry to bother you again. It seems I'm simply to stupid to understand...

First of all: Are you talking about PHPMaker or PHPReportMaker. Because I cannot find extended filters in PHPMaker as of version 2017.0.7.
In ReportMaker I can follow your explanation up to the point of modifying the WHERE-statements. Altough my basic statement in the custom view contains WHERE-conditions they don't show up in your vardump($sql). Not at Page_Load and not at Page_Selection. So therefore I cannot replace anything. Even with the filters it seems that the vardump($sql) never changes. All these filters must be applied in a later step. So I don't understand how you can modify this during Page_Load.


kirondedshem
User
Posts: 642

Post by kirondedshem »

First of all: Are you talking about PHPMaker or PHPReportMaker. Because I cannot find extended filters in PHPMaker

sorry about that, I just copied it from one of my report maker projects since I was answering a similar question in report maker so i dint notice it
So where ever am referencing extended serch, AM REFERING TO EXTENDED SERACH

go to field settings of a given field-> scroll to "List section"-> you should see a column at teh end of the list section called "Ext. Seacrh"->tick it for a field you want.


planet-fisherman
User
Posts: 11

Post by planet-fisherman »

Alright, so here I am again.
I finally figured out a way that works for me although I still couldn't follow your procedure but it was very helpful to get me on the right track.

In case anyone is looking for something similar:
For the job I needed to manipulate the basic SQL as well as the filters since the filters (even if there is any in the SQL of the custom view) are not made part of the basic SQL-statement.
So I put my code into Recordset_Selecting of Table-Specific -> Common since here I can access the basic SQL as well as the filters.

// get the $GET-values passed from my dropdown fields
$
SESSION["current_Agang"] = $GET["x_Agang"];
$
SESSION["current_ProjectID"] = $GET["x_ProjectID"];
$
SESSION["current_OrderID"] = $_GET["x_OrderID"];

// to manipulate the basic SQL
$sql = $this->getSqlSelect();
$sql = str_ireplace("MY-AGANG",ew_AdjustSql($_SESSION["current_Agang"]),$sql);

since my field "Agang" is beeing selected via dropdown also it will be included in the filter. But I only want the information for the join-argument of the basic SQL and not in the filter. So I will redefine the filters manually.

// to redifine the filters and get rid of the automaticaly defined filter (s.Agang = N'MY-AGANG')
$filter = "(f.ProjectID= ".$SESSION["current_ProjectID"].") AND (f.OrderID= ".$SESSION["current_OrderID"].")";

Feel free to correct me if there is room for improvement!
Thanks again for your help, kirondedshem!


kirondedshem
User
Posts: 642

Post by kirondedshem »

// to redifine the filters and get rid of the automaticaly defined filter (s.Agang = N'MY-AGANG')
$filter = "(f.ProjectID= ".$SESSION["current_ProjectID"].") AND (f.OrderID= ".$SESSION["current_OrderID"].")";

This approach in record_selecting event works fine , BUT Just a few tips of thing to look out for (you dont have to follow it now, BUT it ensures that your design is as adjustable as possible)
assuming you have other extended serches which you still want to be applied but only ignore those for fields you know you have put on the form to do something else. eg I had a case where I had a field called select_date which I only used for manual queries on the list, but another one select regions which I still wanted to use to apply normal extended filter based on regions.

So since your approach always ERASES all the search parameters, The ones I still want to use wont work either.
SO what you should have done, is remove the ProjectID & OrderID filter conditions minus affecting other filters. Tick is to know how the filter condition looks in sql, then replace it with for example "1 = 1", that way that filter will not be considered since its always true. You can use regular expressions to do a replace in a more intelligent way.

Eg 1 Here am replacing a BETWEEN conditions for a date serach field

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

//remove date filter
var_dump($filter);
$filter = preg_replace("('' BETWEEN '(.+)' AND '(.+)')","(1 = 1)",$filter);
var_dump($filter);

}

Eg 2, here am not using regular expressions but am fomulating the whole serach condition for a dta eoperator using =, I pick the value from session so taht I can formulate the exact condition I expcet it to use and then apply a normal replace
// Recordset Selecting event
function Recordset_Selecting(&$filter) {
// Enter your code here

    //formulate actual serach condition
$the_date_clause = "'' = '".$_SESSION["vw_outlet_open_close_balance_select_date_1"]."'";
$filter = str_ireplace($the_date_clause,"(1 = 1)",$filter);

}

HINT:You can always var_dump the filter value beofre and after so you are sure your replace is working well


planet-fisherman
User
Posts: 11

Post by planet-fisherman »

For the purpose of completing the topic:

The "WHERE"-statement is also saved in a $SESSION-variable. Unless we also change it here our results will only be correct on first page load. As soon as you re-order or flip the page the $GET-variables aren't passed again but the filter is taken from the session. Therefore I added

$SESSION["MYPROJECT_where"] = $filter;
$
SESSION["MYPROJECT_searchwhere"] = $filter;

Similarly the basic SQL-query. We changed it only when there were $_GET[]-variables. But they will be ommitted in above described cases. Therefore I also added

if (isset($SESSION["my_SQL_QUERY"]))
{
$this->setSqlSelect($
SESSION["my_SQL_QUERY"]);
}

Now it finally works the way I wanted it to - all the way.
Thanks for all the support.

In case this is helpful to anyone, here is my full code.

// Recordset Selecting event
function Recordset_Selecting(&$filter) {
if (isset($SESSION["my_SQL_QUERY"]))
{
$this->setSqlSelect($
SESSION["my_SQL_QUERY"]);
}

if(isset($_GET["x_Agang"]) && isset($_GET["x_Projektnr"]) && isset($_GET["x_Auftragsnr"]))
	{

	//den Arbeitsgang im Basic-SQL ändern
	if (!isset($_SESSION["current_Agang"]))
	{
		$_SESSION["current_Agang"] = "MY-AGANG";  // default in basic SQL
	}
	$sql = $this->getSqlSelect();
	$sql = str_ireplace($_SESSION["current_Agang"],ew_AdjustSql($_GET["x_Agang"]),$sql);
	
	$_SESSION["current_Agang"] = $_GET["x_Agang"];
	$_SESSION["current_ProjectID"] = $_GET["x_ProjectID"];
	$_SESSION["current_OrderID"] = $_GET["x_OrderID"];
	
	//put query in session for page flip and re-order functions
	$_SESSION["my_SQL_QUERY"] = $sql;
	
	//re-define filters and put them in session for page flip and re-order functions
	$filter = "(f.ProjectID = ".$_SESSION["current_ProjectID"].") AND (f.OrderID = ".$_SESSION["current_OrderID"].")";
	$_SESSION["MY-PROJECT_where"] = $filter;
	$_SESSION["MY-PROJECT_searchwhere"] = $filter;
	
	//save the sql back
	$this->setSqlSelect($sql);

}
}

One very last question: What I find confusing is that this code is being processed twice on page load. Is this normal behaviour? Every output is show first before every other <html>-output and a second time just before listing of the recordsets.


sangnandar
User
Posts: 980

Post by sangnandar »

Extended search run the same sql as recordset. Create a project with an extended search enabled to see how they go.
So yes, if this is the case, it's normal.

In some cases they caused an error. If that happen you can safely put @ to surpress the error.


Post Reply