Searching on multiple fields

[icoso]

Im sorry I dont' understand the acronyms, HK, PK??? I think I'm over thinking this. In a Previous post I asked how to prevent a Forms auto search function from auto-firing when I first open the form. Right Now if I open that TaxCustomer Form by itself, it goes to the Search screen as shown above, but it doesn't auto retrieve any results. That's what I wanted it to do. I have to enter a value in the search and either click the search button or the searchEncrypted button. It works great this way.

How then do I make this form auto fire the search function? How do I turn it back on? Where does it get the SQL for the auto search function?

With that in mind couldn't I call this same form, from a launch form where I set the #FRM_search# and #FRM_lastname# variables and then have the form run the auto firing search using the #FRM_lastname# variable in its SQL statement that it uses when it auto runs? Then that would retrieve the records and list them normally, Right? Ijust now have to figure out how I turned off that auto-search and where it gets the SQL to run? And can I pas the FRM_lastname variable to that SQL?

I tried add this to the BB:

Code: Select all

if ("#FRM_search#" == false) {
   nuDebug("NOT Called by SearchForm");
}  else {  
$s = "
SELECT TaxCustomers.* FROM TaxCustomers";

nuDebug("Call by SearchForm = #FRM_lastname#");

if ("#FRM_lastname#" != "") {$s = $s." WHERE (TaxCustomers.cust_lastname LIKE '#FRM_lastname#%')";}
$s = $s." ORDER BY cust_lastname";

nuDebug("SQL=".$s);

//This runs the SQL query against my table to retrieve the data  I need. 
$t = nuRunQuery($s);

} // end if FRM_Search

But it didn't work. I didn't see any results. I looked at my Debug and I see that the variable are being passed through ok from my launch form but no results were listed, So Im wondering if the natural function of the from somehow ignored those results or stopped it from running.

[kev1n]

HK = Hash Cookie
PK = Primary Key

But it didn't work. I didn't see any results

When the Search form is open, press CTRL+SHIFT+I to view the generated Browse SQL. Does that look ok and return results in e.g. phpMyAdmin?

[icoso]

HEre is the result of hitting the CRTL-Shift-I:

BrowseFormSql.png

This SQL statement appears to be a combination of what is in the SQL block on the Browse screen when I go into the Properties of the form and whatever default SQL is for the Select part. Those fields are what is displayed on the browse screen. But this SQL doesn't seem to be executed. I found the post where I originally asked how to stop it from auto firing the SQL search. It's here: https://forums.nubuilder.cloud/viewtopic.php?f=19&t=10819 SO what you put in this post is in the Custom code of this form.

The BB code does not seem to be firing or at least I cant see that its doing anything. COuld I add the code to the SQL on the Browse screen that would auto fire if I set the FRM_Search variable to be "true" which menas the form was called by the launched search form. dn then remove that code that prevents it from being auto-run?

[kev1n]

#date_from # and #date_to# need to be set as global hash cookies before the form is opened

[icoso]

I had typed a reply to this but for some reason it didn't post. Maybe I didn't click submit. Anyways, I think I've had a breakthrough. I added these two lines to my Browse screen SQL:

Code: Select all

 AND IF ('#FRM_lastname#' <> "" AND '#FRM_lastname#' not like '#%', TaxCustomers.cust_lastname LIKE '#FRM_lastname#%', 1)

 AND IF ('#FRM_firstname#' <> "" AND '#FRM_firstname#' not like '#%', TaxCustomers.cust_firstname LIKE '#FRM_firstname#%', 1)

And I call my TaxCustomers form from my new launch form in which I have the firstname and lastname fields on thee and use the

Code: Select all

nuSetProperty('FRM_lastname', lastname);
nuSetProperty('FRM_firstname', firstname);

to pass them through to my TaxCustomers Form. The SQL retrieves just the data I specified in the browse list, AND I can then use the norm search field to search as I previously did.

The BB code that I had:

Code: Select all

if ("#FRM_search#" == false) {

   nuDebug("NOT Called by SearchForm");

}  else {  

$s = "
SELECT TaxCustomers.* FROM TaxCustomers";

nuDebug("Call by SearchForm = #FRM_lastname#");

if ("#FRM_lastname#" != "") {$s = $s." WHERE (TaxCustomers.cust_lastname LIKE '#FRM_lastname#%')";}

$s = $s." ORDER BY cust_lastname";

nuDebug("SQL=".$s);

//This runs the SQL query against my table to retrieve the data  I need. 
$t = nuRunQuery($s);

} // end if FRM_Search

doesn't seem to make any difference and doesn't do anything. What's the purpose of the BB Code? Could I use it to create or modify the SQL that would normally be on the Properties->Browse SQL field? Because If I could then I wouldn't have all those AND IF ('#FRM_lastname#' <> "" AND '#FRM_lastname#' not like '#%', TaxCustomers.cust_lastname LIKE '#FRM_lastname#%', 1) statements for every field I have on the launch form. I could use the PHP to build my SQL statement based on what fields actually had something entered in them on my launch form. So is the SQL that gets generated and shown on the CRTL-SHIFT-I a variable that I could add to or create from within the BB code block?

[kev1n]

From the Wiki:

This SQL is used to display records on a Browse Form from either...

An normal SQL Statement eg...

SELECT * FROM customer
Or an SQL statement that can reference a complicated temporary table created with PHP using a Hash Cookie...

SELECT * FROM #TABLE_ID#

An example can be found here.

[kev1n]

BTW in this thread, we were already talking about creating a temporary table and you're probably already doing it.

[icoso]

Ok that make sense, But Im not doing a complicated SQL statement where I m joining multiple tables. Some how the SQL statement that runs gets generated at some point. It appears to be whatever is in the Browse field (SQL only) combine which whatever the Browse screen has on it for fields. ie: This is on the Browse SQL field:

Code: Select all

SELECT * FROM TaxCustomers WHERE 
(
    IF('#date_from#' <> '' AND '#date_from#' not like '#%', tax_billdate >= '#date_from#', 1)
    AND
    IF('#date_to#' <> '' AND '#date_to#' not like '#%', tax_billdate <= '#date_to#', 1)
)
 AND IF ('#FRM_lastname#' <> "" AND '#FRM_lastname#' not like '#%', TaxCustomers.cust_lastname LIKE '#FRM_lastname#%', 1)

 AND IF ('#FRM_firstname#' <> "" AND '#FRM_firstname#' not like '#%', TaxCustomers.cust_firstname LIKE '#FRM_firstname#%', 1)

AND 

(LENGTH('#SEARCH_FIELD#' ) > 2 AND LEFT('#SEARCH_FIELD#', 1) <> '#')

 ORDER BY cust_lastname, cust_prissn, tax_billdate DESC

Then that gets changed before its actually executed to include the Select statement that is on the beginning of that CTRL-SHIFT-I screen "SELECT Fieldname1, fieldname2, fieldname3..." whatever is listed on the browse screen. This part of the SQL code prevents it from auto-running. " AND (LENGTH('#SEARCH_FIELD#' ) > 2 AND LEFT('#SEARCH_FIELD#', 1) <> '#')" if it opens on it's own, so for what Im trying to do I have to remove that part. So the question is:

At what point do the FULL SQL statement get generated and then executed and where does the BB PHP code come in to play? Can I use the BB code to modify the SQL statement that gets executed? Since it uses hash cookies in the SQL could I use the BB code to set a HASH COokies that contains the additional SQL I need to add to whatever is in the Browse field? OR does the BB PHP code have access to the FIELD VAriable that contains the SQL statement? What is the variable that the Browse SQL statement is stored in? Can it be accessed in anyway in the BB Code?

[kev1n]

Just should try adding additional brackets in your WHERE clause.

WHERE ( ...... )

You cannot access the SQL in BB and modify it. You'd have to do it in this way:

PHP Code in BB PHP:

Code: Select all

$s = "  your sql query here.... ";
nuRunQuery("CREATE TABLE #TABLE_ID# ".$s);

and then in your Browse SQL, write

Code: Select all

SELECT * FROM #TABLE_ID#

[icoso]

A question about has cookies.

If I set a Hash Cookie like this:

Code: Select all

nuSetProperty('SQLSEARCH', sql);

and the variable SQLSEARCH does NOT exist on the current form and I then try to reference this has cookie on a form that I call, can I reference this hash cookie? How do I reference this hash cookie in the subsequent form that I called?
For example: I tried just using a JavaScript alert:

Code: Select all

alert('Search Form SQL='+$('#SQLSEARCH').val());
alert('Search Form SQL='+$('#SQLSEARCH'));
alert('Search Form SQL='+'#SQLSEARCH#');

I tried all three of these and got undefined on the first two and just a text string 'Search Form SQL=#SQLSEARCH#' on the last one.

In my example I have a launch form with the fields FRM_lastname and FRM_firstname on them. I also have a button btn_SEARCH on this form. In the Javascript (Custom Code) I have:

Code: Select all

if ($('#FRM_lastname').val() != "") {
   sql = sql + 'AND TaxCustomers.cust_lastname LIKE ' + $('#FRM_lastname').val() +'%';
}
if ($('#FRM_firstname').val() != "") {
   sql= sql + ' AND TaxCustomers.cust_firstname LIKE ' + $('#FRM_firstname').val()+'%';
}

If I do an alert using the sql variable. It displays the correct text.
If I do an alert AFTER this assignment:

Code: Select all

nuSetProperty('SQLSEARCH', sql);
alert('MGMT Form SQL='+$('#SQLSEARCH').val());

I get undefined for the $('#SQLSEARCH').val().

Why? AM I not referencing the SQLSEARCH Variable correctly or since that doesn't exist on the form I can't set it and pass it?

Also, I tested further with this:

Code: Select all

var lastname = $('#FRM_lastname').val();
var firstname = $('#FRM_firstname').val();
var sql = '';

if (lastname != "") {
   sql = sql + 'AND TaxCustomers.cust_lastname LIKE ' + lastname +'%';
}
if (firstname != "") {
   sql= sql + ' AND TaxCustomers.cust_firstname LIKE ' + firstname +'%';
}

nuSetProperty('FRM_lname', lastname);
nuSetProperty('FRM_fname', firstname);

I get the same undefined results when trying to use the FRM_lname or FRM_fname variables. HOWEVER if I change those to be FRM_lastname and FRM_firstname in the nuSetProperty above and then use those variables in the next form that I call, it works without any issues. So this makes me think I have to have the field ON the form before I can use the nuSetProperty command. Is this correct? If yes, How can I pass along the "SQLSEARCH" variable text to the next form? Can I set a hidden field on the launch form name SQLSEARCH and then use that on the called form?