2. PHPRunner General questions
  3. Query by Parameter
This topic is locked

Query by Parameter

 		7/28/2008 5:03:36 AM
PHPRunner General questions
T
 thesofa author

Hi

I used to programme in Access ( sorry for swearing) but now my needs have moved me to MySQL

I used to use query by parameter a lot in Access, so If I wanted a report to collate date between 2 dates, when the report is run, Access presents a dialogue asking for the start date and then for the finish date for the report data.

In addition, further fields can be added as a parameter to limit the report data.

I realise I can do this all from the advanced search page, but we are talking about teachers here, they need their hand holding just to make a cup of tea, so setting 2 dates and a year group from Advanced search may well be beyond their abilities.

Is it possible to have PHPR send up dialogue boxes to ask for the dates and year groups?

I suppose I can redirect the report straight to the Advanced search page, can I change the choice for the dates to be Between rather than Equals?

Cheers for the help in advance.
J
 		Jane 7/28/2008

Hi,
to redirect to the advanced search directly use following code in the List page: Before process event:

if (@$_REQUEST["a"]!="search" && @$_REQUEST["a"]!="advsearch")

{

header("Location: tablename_search.php");

exit();

}


To remove all search options (except Between) on the advanced search page use following code in the "Search Page: Before display" event on the Events tab:

// write search options

$options="";

$options.="<OPTION VALUE=\"Between\" selected>"."Between"."</option>";

$searchtype = "<SELECT ID=\"SearchOption\" NAME=\"asearchopt_FieldName\" SIZE=1 onChange=\"return ShowHideControls();\">";

$searchtype .= $options;

$searchtype .= "</SELECT>";

$smarty->assign("searchtype_FieldName",$searchtype);



where FieldName is your actual field name.
T
 		thesofa author 7/28/2008

Hi,

to redirect to the advanced search directly use following code in the List page: Before process event:
To remove all search options (except Between) on the advanced search page use following code in the "Search Page: Before display" event on the Events tab:
where FieldName is your actual field name.



Thanks
T
 		thesofa author 7/28/2008



Thanks



Thanks, I have tried this and this seems to make no difference to my search pages.

The code is as follows

$options="";

$options.="<OPTION VALUE=\"Between\" selected>"."Between"."</option>";

$searchtype = "<SELECT ID=\"SearchOption\" NAME=\"asearchopt_datestamp\" SIZE=1 onChange=\"return ShowHideControls();\">";

$searchtype .= $options;

$searchtype .= "</SELECT>";

$smarty->assign("searchtype_datestamp",$searchtype);


It is quite possible I have missed some of the needed fieldname changes, the query for the page is as follows

SELECT

nd_behaviours.datestamp AS `Date Awarded`,

Concat(left(staff.s_firstname, 1), ". ", staff.s_lastname) AS Teacher,
nd_dept_list.department AS Department,

nd_behaviour_list.behaviour AS Reason,

SUM(nd_behaviour_list.weight) AS Score,

Concat(nd_pupils.p_firstname, " ", nd_pupils.`Middle name(s)`, " ", nd_pupils.p_lastname) AS Pupil,
nd_behaviours.id_det,

nd_pupils.reg AS `Tutor Group`
FROM nd_behaviours

INNER JOIN staff ON nd_behaviours.staff_id = staff.id_staff

INNER JOIN nd_pupils ON nd_behaviours.pup_id = nd_pupils.id_pup

INNER JOIN nd_dept_list ON nd_behaviours.department = nd_dept_list.idDept

INNER JOIN nd_behaviour_list ON nd_behaviours.reason = nd_behaviour_list.id_behaviours

GROUP BY nd_behaviours.id_det, nd_pupils.reg

ORDER BY nd_pupils.reg, nd_pupils.p_lastname DESC, nd_pupils.p_firstname DESC, staff.s_lastname DESC


the following fields are searchable too

Tutor Group

Pupil

Date Awarded

Department

Plz tell me what I have missed out?
J
 		Jane 7/29/2008

Field names are case sensitive here.

I.e.

$options="";

$options.="<OPTION VALUE=\"Between\" selected>"."Between"."</option>";

$searchtype = "<SELECT ID=\"SearchOption\" NAME=\"asearchoptDate Awarded\" SIZE=1 onChange=\"return ShowHideControls();\">";

$searchtype .= $options;

$searchtype .= "</SELECT>";

$smarty->assign("searchtypeDate Awarded",$searchtype);
T
 		thesofa author 7/29/2008

Field names are case sensitive here.

I.e.



OK, I have tried this, I still get all the options in the Equals, Contains, Between drop down box.

Recalling the days of V3.0, I have found this link about a similar thing, do I need to follow the instructions in the last post there too, or is that outdated now we are on version 4.2?
J
 		Jane 7/29/2008

This code works on my test box.

Try to debug it. For example open advanced search page on the Visual Editor tab, turn on HTML mode and compare smarty variables.
T
 		thesofa author 7/29/2008

This code works on my test box.

Try to debug it. For example open advanced search page on the Visual Editor tab, turn on HTML mode and compare smarty variables.


How do I recognise a Smarty variable?
This is the relevant bit I think

<TD class=fieldname>Date Awarded <input type="Hidden" name="asearchfield[]" value="Date Awarded"></TD>

<TD align=middle><INPUT type=checkbox name=not_Date_Awarded

{$not_Date_Awarded}></TD>

<TD>{$searchtype_Date_Awarded} </TD>

<TD width=270>{build_edit_control field="Date Awarded" value=$value_Date_Awarded mode="search"}</TD>

<TD width=270><SPAN id=second_Date_Awarded>{build_edit_control field="Date Awarded" second=true value=$value1_Date_Awarded mode="search"} </SPAN></TD></TR>

<TR class=shade>



There are some Date_Awarded and some Date Awarded, should they all be the same?
J
 		Jane 7/29/2008

See correct variable name in Bold:

<TD class=fieldname>Date Awarded <input type="Hidden" name="asearchfield[]" value="Date Awarded"></TD>

<TD align=middle><INPUT type=checkbox name=not_Date_Awarded

{$not_Date_Awarded}></TD>

<TD>{$searchtype_Date_Awarded} </TD>

<TD width=270>{build_edit_control field="Date Awarded" value=$value_Date_Awarded mode="search"}</TD>

<TD width=270><SPAN id=second_Date_Awarded>{build_edit_control field="Date Awarded" second=true value=$value1_Date_Awarded mode="search"} </SPAN></TD></TR>

<TR class=shade>
T
 		thesofa author 7/29/2008

See correct variable name in Bold:




I think I have sorted it, I have compared the code in the section you sent me to the answer above and I have changed the section as below

// write search options

$options="";

$options.="<OPTION VALUE=\"Between\" selected>"."Between"."</option>";

$searchtype = "<SELECT ID=\"SearchOption\" NAME=\"asearchopt_Date_Awarded\" SIZE=1 onChange=\"return ShowHideControls();\">";

$searchtype .= $options;

$searchtype .= "</SELECT>";

$smarty->assign("searchtype_Date_Awarded",$searchtype);


That seems to have sorted it, my fault for having spaces in the field Titles.....
T
 		thesofa author 8/1/2008

This works fine as described, BUT, if the result set occupies more than one page, when I click on the link to go to page 2, the page goes back to the advanced search page!!!!
T
 		thesofa author 8/3/2008

OK, a change of tactic here, I still need to go to the advanced search page as soon as this report page is selected,

BUT, the method above has limitations , is there a way of creating a session variable for this searching, something like @session_filtered.

Set this to "" at the start of the user's session and when user clicks on search on the advanced search page, set this to 1 or Yes, then check for content on the list page, so i can then make changes to the report in terms of number of records to a page, without it resetting back to the advanced search page.
I know it's dirty, but will it work?
J
 		Jane 8/4/2008

Hi,
try to use this code instead:

if (@$_REQUEST["a"]!="search" && @$_REQUEST["a"]!="advsearch" && !@$_SESSION["tablename_advsearch"])

{

header("Location: tablename_search.php");

exit();

}


Then fill $_SESSION["tablename_advsearch"] in the Search page: Before process event:

$_SESSION["tablename_advsearch"] = 1;
T
 		thesofa author 8/4/2008

OK, That makes sense, and better named than I would have done.

Thank heavens for copy and paste!!

Where do I empty the variable for doing more than one report?
T
 		thesofa author 8/4/2008

Hi
just tried that with this result

Parse error: parse error, unexpected T_BOOLEAN_AND in D:\htdocs\staff\betest\include\Behaviours_by_Tutor_Group_events.php on line 15


Line 15 reads

if (@$_REQUEST["a"]!="search" && @$_REQUEST["a"]!="advsearch")&& !@$_SESSION["Behaviours_by_Tutor_Group_advsearch"]


As instructed added this

// Search page: Before process

function BeforeProcessSearch()

{

//********** Custom code ************

// put your custom code here

$_SESSION["Behaviours_by_Tutor_Group_advsearch"] = 1;
} // function BeforeProcessSearch


What have I missed out?
J
 		Jane 8/4/2008

Hi,
you've forgot second ) bracket:

if (@$_REQUEST["a"]!="search" && @$_REQUEST["a"]!="advsearch")&& !@$_SESSION["Behaviours_by_Tutor_Group_advsearch"])


You can empty $_SESSION["Behaviours_by_Tutor_Group_advsearch"] variable on the another reports for example.
T
 		thesofa author 8/4/2008

Hi,

you've forgot second ) bracket:
You can empty $_SESSION["Behaviours_by_Tutor_Group_advsearch"] variable on the another reports for example.



I have moved the second bracket from just after "advsearch")&& and put it at the end.

Once again, many, many thanks.

When would the variable get emptied "naturally"?

Only at system startup?
As these reports are for printing, is there an event that takes place after the printing has been completed?then I could empty the variable there, rather than leaving it lying around all the time?
J
 		Jane 8/4/2008

Hi,
you can empty your variables in any place you want. Just select page and add corresponding events.