Test

[Gustave 0]

Scotty,

The wonderful code you provided three years ago stopped working yesterday morning.

When I run the report now, I suddenly get

Error: The syntax of the query run with this report (ID: 42) was incorrect, and as a result the report could not be run. Please contact the system administrator at suggest@sprague-database.org.

Query: SELECT id, pl.gedcom, concat('', pl.place, '') as Places, notes, concat('', evcount, '') as eventcount FROM tng_places as pl join ( select gedcom, place, count(*) as evcount from ( SELECT gedcom, birthplace AS place FROM `tng_people` WHERE gedcom = 'spragueproject' UNION all SELECT gedcom, altbirthplace FROM `tng_people` WHERE gedcom = 'spragueproject' UNION all SELECT gedcom, marrplace FROM `tng_families` WHERE gedcom = 'spragueproject' UNION all SELECT gedcom, deathplace FROM `tng_people` WHERE gedcom = 'spragueproject' UNION all SELECT gedcom, burialplace FROM `tng_people` WHERE gedcom = 'spragueproject' UNION all SELECT gedcom, eventplace FROM tng_events WHERE gedcom = 'spragueproject' ) as p group by gedcom, place ) as plc using ( gedcom, place ) where (isnull(longitude) or isnull(latitude) or longitude='' or latitude = '') order by evcount desc, gedcom, pl.place

Error Message:

Here is what I am using in TNG Reports:

OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:

SELECT id, pl.gedcom,

concat('<a href="admin/editplace.php?ID=',pl.id,'&tree=', pl.gedcom,'", target=_blank>', pl.place, '</a>') as Places, notes, concat('<a href="placesearch.php?psearch=',replace(pl.place,' ','+'), '">', evcount, '</a>') as eventcount

FROM tng_places as pl

join

( select gedcom, place, count(*) as evcount from

( SELECT gedcom, birthplace AS place

FROM `tng_people`

WHERE gedcom = 'spragueproject'

UNION all SELECT gedcom, altbirthplace

FROM `tng_people`

WHERE gedcom = 'spragueproject'

UNION all SELECT gedcom, marrplace

FROM `tng_families`

WHERE gedcom = 'spragueproject'

UNION all SELECT gedcom, deathplace

FROM `tng_people`

WHERE gedcom = 'spragueproject'

UNION all SELECT gedcom, burialplace

FROM `tng_people`

WHERE gedcom = 'spragueproject'

UNION all SELECT gedcom, eventplace

FROM tng_events

WHERE gedcom = 'spragueproject'

) as p group by gedcom, place

) as plc using ( gedcom, place )

where (isnull(longitude) or isnull(latitude) or longitude='' or latitude = '')

order by evcount desc, gedcom, pl.place

To run the report yourself, please go to

http://www.sprague-database.org/genealogy/reports.php.

scroll down to #36, "xx-Places - No Coordinates" and click on it.

Many, many thanks,

Arnold