Guidance needed creating multi-criteria search form with subform

[Keith-i]

Brilliant, all working beautifully. Thanks ever so much.

Is there any way to get the 2 secondary iframes to be refreshed/cleared when I hit my reset button?

[kev1n]

Add the following function to the Custom Code of the frm_propsearch form.

Code: Select all

function resetIframes() {
   
     let f = $("#iframe_instructionslist")[0].contentWindow; 
     f.nuSetProperty('Propertyid', ''); // Set a hashcookie
     f.nuGetBreadcrumb(); // refresh the iframe

     f = $("#iframe_enquirieslist")[0].contentWindow;
     f.nuSetProperty('Propertyid', '');
     f.nuGetBreadcrumb();

}

Then call

Code: Select all

resetIframes();

in your reset's button onclick event.

[Keith-i]

I wonder if you could just help me out again with a bit of code to get my filters to search based on a date range. I have two input objects in which I can select a date. These are given the hash names datefrom_fltr and dateto_fltr. Where I'm struggling is the additional code required in the BB of the iframe to filter the results based on theses dates. My existing BB code with the other filter boxes is below

Code: Select all

$filter = ' AND (1 = 1) ';

setFilter($filter, 'proptype_fltr', 'Type');
setFilter($filter, 'propstyle_fltr', 'Style');
setFilter($filter, 'location_fltr', 'Location');
setFilter($filter, 'beds_fltr', 'Bedrooms');
setFilter($filter, 'seaview_fltr', 'Seaview');
setFilter($filter, 'integral_fltr', 'Integral');
//add datefrom_fltr and dateto_fltr here?

function setFilter(&$filter, $filterName, $columnName) {
    $value = nuGetProperty($filterName);
    if ($value != '') $filter .= " AND $columnName LIKE '%$value%' ";
//add extra code here to search on date range?
}
$create = "CREATE TABLE #TABLE_ID# ";

$select = "

SELECT
 tblInstructions.*,
    tblProperties.*,
    tblRoads.*

FROM
    tblInstructions
        LEFT JOIN tblProperties ON tblProperties.idProperties = tblInstructions.id_Properties
        LEFT JOIN tblRoads ON tblRoads.idRoads = tblProperties.id_Roads

WHERE (1 = 1)

" . $filter;

// To output the SQL to nuDebug Results:
// nuDebug($select);
nuRunQuery($create . $select);
 

[kev1n]

Add the new setDateFilter() function from below and call it like this:

Code: Select all

setDateFilter($filter, 'datefrom_fltr', 'dateto_fltr', 'HERE_YOUR_DATE_COLUMN');

Code: Select all

function setDateFilter(&$filter, $dateFilter1, $dateFilter2, $dateColumn) {
    $date1 = nuGetProperty($dateFilter1);
    $date2 = nuGetProperty($dateFilter2);
    if ($date1 != '' && $date2 != '') {
        $filter .= " AND (`$dateColumn` BETWEEN `$date1` AND `$date2`) ";
    }
}

The expected date format is yyyy-mm-dd. If your date fields have another format, you need to format it in JavaScript like:

Code: Select all

frame.nuSetProperty('datefrom_fltr', $('#your_date_object_id').nuRemoveFormatting());

All untested. If you encounter an issue, uncomment the nuDebug(); line in the code and paste the generated SQL in the forum.

[Keith-i]

Thanks, I'll give it try.

[Keith-i]

Ok, heading in the right direction but getting an error on debug

Code: Select all

globeadmin

===PDO MESSAGE===

SQLSTATE[42S22]: Column not found: 1054 Unknown column '2023-02-02' in 'where clause'

===SQL===========

CREATE TABLE ___nu163f61ae843b0a___ 

SELECT
 tblInstructions.*,
    tblProperties.*,
    tblRoads.*

FROM
    tblInstructions
        LEFT JOIN tblProperties ON tblProperties.idProperties = tblInstructions.id_Properties
        LEFT JOIN tblRoads ON tblRoads.idRoads = tblProperties.id_Roads

WHERE (1 = 1)

 AND (1 = 1)  AND Type LIKE '%1%'  AND (`Date` BETWEEN `2023-02-02` AND `2022-02-02`) 

Is it perhaps some syntax around the dates?

[kev1n]

Use single quotes instead of backticks around the date columns:

Code: Select all

function setDateFilter(&$filter, $dateFilter1, $dateFilter2, $dateColumn) {
    $date1 = nuGetProperty($dateFilter1);
    $date2 = nuGetProperty($dateFilter2);
    if ($date1 != '' && $date2 != '') {
        $filter .= " AND (`$dateColumn` BETWEEN '$date1' AND '$date2') ";
    }
}

[Keith-i]

Beautiful, thanks!