Order By DESC not working on iframe or report

[Keith-i]

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);

[kev1n]

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

[Keith-i]

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)

[kev1n]

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

[Keith-i]

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.

[kev1n]

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 ";

[Keith-i]

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

[kev1n]

Code updated

[Keith-i]

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.

[kev1n]

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

Please do that again