Guidance needed creating multi-criteria search form with subform

[Keith-i]

There are 3 seemingly relevant debug messages, two of them refer to table not found,

Code: Select all

===PDO MESSAGE===

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

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

SELECT COUNT(*) FROM (SELECT idProperties,BuildingName,UnitNo,UnitName,HouseNo,RoadName,Parish,Postcode
 FROM ___nu163ea16ab4b751___
WHERE 1) nuTCount

the 3rd is suggesting a syntax error. Any thoughts on this?

Code: Select all

===PDO MESSAGE===

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/ WHERE (1 = 1)  -- your where clause, if there is any

 AND (1 = 1)' at line 14

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

CREATE TABLE ___nu163ea16ab4b751___ 

SELECT 

    BuildingName,
    UnitNo,
    UnitName,
    HouseNo,
    RoadName,
    Postcode

FROM tblProperties LEFT JOIN tblRoads ON tblRoads.idRoads = tbpProperties.id_Roads

// WHERE (1 = 1)  -- your where clause, if there is any

 AND (1 = 1) 

===BACK TRACE====

[kev1n]

Remove // in front of WHERE (1 = 1)

[Keith-i]

Hmm, still getting the same SQL syntax complaint

Code: Select all

===PDO MESSAGE===

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/-- your where clause, if there is any

 AND (1 = 1)' at line 15

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

CREATE TABLE ___nu163ea1b9f5249e___ 

SELECT 

    BuildingName,
    UnitNo,
    UnitName,
    HouseNo,
    RoadName,
    Postcode

FROM tblProperties LEFT JOIN tblRoads ON tblRoads.idRoads = tblProperties.id_Roads

WHERE (1 = 1)  
//-- your where clause, if there is any

 AND (1 = 1) 

[kev1n]

this should be in your SQL

Code: Select all

//-- your where clause, if there is any

[Keith-i]

Did you mean should not be in my SQL? Sorry if these are basic questions, I appreciate your help.

I've removed that line and the syntax and missing table errors have gone but I am now getting this.

Code: Select all

===PDO MESSAGE===

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'idProperties' in 'field list'

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

SELECT idProperties,BuildingName,UnitNo,UnitName,HouseNo,RoadName,Parish,Postcode
 FROM ___nu163ea2209e52c8___
WHERE 1 LIMIT 0, 20

The idProperties is the primary key in my table.

[kev1n]

Add the column idProperties in your SQL statement.

[Keith-i]

No joy, still the same debug error.

Code: Select all

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'idProperties' in 'field list'

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

SELECT idProperties,BuildingName,UnitNo,UnitName,HouseNo,RoadName,Parish,Postcode
 FROM ___nu163ea265e89aa5___
WHERE 1 LIMIT 0, 20

[Keith-i]

My mistake, I missed a comma off after the field name in the SQL. Trying again...

[Keith-i]

Okey dokey; that's the error messages out of the way but my 'Search' button doesn't do anything! Looks like I'll be back soon for more help.

[kev1n]

If you want, you can send me a dump of your form (also by PM). You can use the cloner for this.