I am trying to display my new `cemetery-records' table in Ari Data tables. It is working with a CSV file on page cfgs.org/index.php/local-resource/cemetery-records so I believe the component is setup OK.

I am getting an error when I try to open the page. The fields are: cemetery, surname, given-name, birth, death and notes. It has been along time since I worked with Select statements and then only in FoxPro and MS Access so I am sure my syntax is wrong.

The Select statement I am using on the test page at cfgs.org/index.php/local-resource/cemetery-records-2 is

SELECT Cemetery-records.[cemetery], Cemetery-records.[surname], Cemetery-records.[given-name], Cemetery-records.[birth], Cemetery-records.[death], Cemetery-records.[notes]
FROM Cemetery-records
ORDER BY Cemetery-records.[cemetery], Cemetery-records.[surname], Cemetery-records.[given-name];

The ERROR message I am getting is:

Error: 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 '[cemetery], Cemetery-records.[surname], Cemetery-records.[given-name], Cemetery-' at line 1 SQL=SELECT Cemetery-records.[cemetery], Cemetery-records.[surname], Cemetery-records.[given-name], Cemetery-records.[birth], Cemetery-records.[death], Cemetery-records.[notes] FROM Cemetery-records ORDER BY Cemetery-records.[cemetery], Cemetery-records.[surname], Cemetery-records.[given-name];

What do I need to correct to get this to work.

Many thanks,
Walter
cfgs.org

Hello,

Use the following SQL query:

SELECT
cemetery,
surname,
given-name,
birth,
death,
notes
FROM
Cemetery-records
ORDER BY
cemetery,
surname,
given-name

Regards,
ARI Soft

Still not working as I am getting the following error with the new select statement:

Error: 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 '-records ORDER BY cemetery, surname, given-name' at line 9 SQL=SELECT cemetery, surname, given-name, birth, death, notes FROM cemetery-records ORDER BY cemetery, surname, given-name

Attaching the SQL table structure and the Data Table component column settings.

Does the table need indexes created?

Link: This is the table settings in Ari component

Link: Table Structure

Use the following:

SELECT
cemetery,
surname,
`given-name`,
birth,
death,
notes
FROM
`Cemetery-records`
ORDER BY
cemetery,
surname,
`given-name`

Regards,
ARI Soft

This is what finally worked.

SELECT `cemetery`, `surname`, `given-name`, `birth`, `death`, `notes` FROM `cemetery_records` ORDER BY cemetery, surname, `given-name`

Note that I renamed the table name while trying to get this to work but that is not what made the difference.

For future reference the SQL SELECT syntax can be found at dev.mysql.com/doc/refman/5.5/en/select.html.

Thanks for the timely assistance.
Walter