nuBuilder Forum

Posted: **Thu Mar 09, 2023 4:11 pm**

I have an iframe that gets populated with data from a launch page with some search/filter boxes on it. I'd like to order the results on the iframe table by date descending but whenever I add the 'order by' command in the SQL I get an error. Similarly, whenever I try to set the date order descending on the related report it reverts to ASC.

Code from my 'Before Browse' PHP on the iframe. Image attached shows error message.

Code: Select all

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

setValFilter($filter, 'minprice_fltr', 'maxprice_fltr', 'SaleValue');
setDateFilter($filter, 'datesfrom_fltr', 'datesto_fltr', 'SaleDate');

function setValFilter(&$filter, $valFilter1, $valFilter2, $valColumn) {
    $val1 = nuGetProperty($valFilter1);
    $val2 = nuGetProperty($valFilter2);
    if ($val1 != '' && $val2 != '') {
        $filter .= " AND (`$valColumn` BETWEEN '$val1' AND '$val2') ";
    }
}

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

$create = "CREATE TABLE #TABLE_ID# ";

$select = "

SELECT
 tblValues.*,
    tblProperties.*,
    tblRoads.*

FROM
    tblValues
        JOIN tblProperties ON tblProperties.idProperties = tblValues.id_Properties
        JOIN tblRoads ON tblRoads.idRoads = tblProperties.id_Roads

WHERE (1 = 1)

" . $filter;

ORDER BY
    tblValues.SaleDate DESC

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

Posted: **Thu Mar 09, 2023 5:12 pm**

Can you post the generated SQL ? (Options Menu -> Form Info)

Posted: **Fri Mar 10, 2023 9:53 am**

Hi kev1n

Thanks for your continued help and support.
I don't get any generated SQL showing under 'form info' when I have the error. If I remove the offedning line of 'Order By' from the BB PHP then I do get SQL showing which all looks normal. All I can offer is the nuDebug message.

Code: Select all

[0] : ===USER==========

globeadmin

===PDO MESSAGE===

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'rcs2022db.___nu1640aeedfeff4c___' doesn't exist

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

SELECT idValues,FlatNo,BuildingName,UnitNo,UnitName,HouseNo,RoadName,SaleDate,SaleValue
 FROM ___nu1640aeedfeff4c___
WHERE 1 LIMIT 0, 20

===BACK TRACE====

/var/www/html/rcsdb/core/nuform.php - line 1217 (nuRunQuery)

/var/www/html/rcsdb/core/nuform.php - line 455 (nuBrowseRows)

/var/www/html/rcsdb/core/nuapi.php - line 92 (nuGetFormObject)

Posted: **Fri Mar 10, 2023 9:58 am**

If you uncomment //nuDebug($select); in your code, you'll get the raw SQL in nuDebug Results.

Posted: **Fri Mar 10, 2023 10:15 am**

I did actually do that and that is the debug message I provided above. It doesn't seem to get as far as generating any SQL.

Posted: **Fri Mar 10, 2023 11:27 am**

The issue with this PHP code is that the ORDER BY clause is not included in the SQL query string, it is placed outside of the quotes on a separate line. This will cause a syntax error when the query is executed.

To fix the code, you should include the ORDER BY clause within the SQL query string, like this:

$select = "

Code: Select all

SELECT
 tblValues.*,
    tblProperties.*,
    tblRoads.*

FROM
    tblValues
        JOIN tblProperties ON tblProperties.idProperties = tblValues.id_Properties
        JOIN tblRoads ON tblRoads.idRoads = tblProperties.id_Roads

WHERE (1 = 1)

" . $filter. "  

." ORDER BY
    tblValues.SaleDate DESC ";

Posted: **Fri Mar 10, 2023 1:17 pm**

Thanks. Almost there but not quite... latest error when trying to open the form shown below. I'm guessing it is something to do with position of quotes and periods but it is beyond my knowledge unfortunately.

Code: Select all

Before Browse of Form frm_valuesbyvalue

/var/www/html/rcsdb/core/nucommon.php(1416) : eval()'d code
syntax error, unexpected '"' (T_CONSTANT_ENCAPSED_STRING)

Traced from...

(line:92) /var/www/html/rcsdb/core/nuapi.php - nuGetFormObject

(line:454) /var/www/html/rcsdb/core/nuform.php - nuBrowseColumns

(line:1095) /var/www/html/rcsdb/core/nuform.php - nuBeforeBrowse

(line:24) /var/www/html/rcsdb/core/nuform.php - nuEval

Posted: **Fri Mar 10, 2023 1:42 pm**

Code updated

Posted: **Fri Mar 10, 2023 1:57 pm**

Thanks but still not working quite right. I don't seem to be getting any error messages now, but I'm also not getting any data either! Just a blank form.

Posted: **Fri Mar 10, 2023 2:03 pm**

kev1n wrote: ↑Fri Mar 10, 2023 9:58 am If you uncomment //nuDebug($select); in your code, you'll get the raw SQL in nuDebug Results.

Please do that again

nuBuilder Forum

Order By DESC not working on iframe or report

Order By DESC not working on iframe or report

Re: Order By DESC not working on iframe or report

Re: Order By DESC not working on iframe or report

Re: Order By DESC not working on iframe or report

Re: Order By DESC not working on iframe or report

Re: Order By DESC not working on iframe or report

Re: Order By DESC not working on iframe or report

Re: Order By DESC not working on iframe or report

Re: Order By DESC not working on iframe or report

Re: Order By DESC not working on iframe or report