SQL error for field value with " from " (v2024)

PHPMaker General Discussion (PHPMaker)
1

I have a responses table, with filter set on the Quesion field. Whenever I filter on the Question ‘Apart from the library, where do you like to study on campus? Be it for 5 minutes of 5 hours, we would like to understand what areas you use.’ the sql errors with a syntax error

[2025-02-03T02:33:26.620899+00:00] log.DEBUG: SELECT COUNT(*) FROM the library, where do you study on campus? Be it for 5 minutes or 5 hours we’d like to understand what areas you use.' {"params":[],"types":[],"executionMS":0.0011131763458251953} []

A similar filter for the question ‘Agree or disagree? There are suitable parks and green spaces on or near campus for me to access.’ is fine and logs

[2025-02-03T02:12:49.804239+00:00] log.DEBUG: Executing query: SELECT * FROM Responses WHERE `Question` = 'Agree or disagree? There are suitable parks and green spaces on or near campus for me to access.' ORDER BY `SurveyID` DESC, `RespondentID` ASC, `ResponseID` ASC LIMIT 20 {"params":[],"types":[]} []
[2025-02-03T02:12:50.064853+00:00] log.DEBUG: SELECT * FROM Responses WHERE `Question` = 'Agree or disagree? There are suitable parks and green spaces on or near campus for me to access.' ORDER BY `SurveyID` DESC, `RespondentID` ASC, `ResponseID` ASC LIMIT 20 {"params":[],"types":[],"executionMS":0.26059699058532715}  .......

So I figure it’s not the length of the question. Other questions with commas also are fine?
I’m not sure how to further debug this, any thoughts appreciated.

2
  1. Which version are you using?
  2. If you use Custom View, you should convert it to database view.
3

Thanks for the reply.
It is 2024 version, and Responses is a table not a view.

4

You may enable Debug and other related settings and post the complete error stack trace for discussion.

5

Do you mean this ?

Error

/opt/bitnami/apache/htdocs/intuition/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php(86): An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘where do you like to study on campus? Be it for 5 minutes of 5 hours, we woul…’ at line 1

Debug

#0 /opt/bitnami/apache/htdocs/intuition/vendor/doctrine/dbal/src/Connection.php(1939): Doctrine\DBAL\Driver\API\MySQL\ExceptionConverter->convert() #1 /opt/bitnami/apache/htdocs/intuition/vendor/doctrine/dbal/src/Connection.php(1881): Doctrine\DBAL\Connection->handleDriverException() #2 /opt/bitnami/apache/htdocs/intuition/vendor/doctrine/dbal/src/Connection.php(1106): Doctrine\DBAL\Connection->convertExceptionDuringQuery() #3 /opt/bitnami/apache/htdocs/intuition/vendor/doctrine/dbal/src/Connection.php(622): Doctrine\DBAL\Connection->executeQuery() #4 /opt/bitnami/apache/htdocs/intuition/models/Responses.php(1036): Doctrine\DBAL\Connection->fetchOne() #5 /opt/bitnami/apache/htdocs/intuition/models/Responses.php(1149): PHPMaker2024\intuition\Responses->getRecordCount() #6 /opt/bitnami/apache/htdocs/intuition/models/ResponsesList.php(1025): PHPMaker2024\intuition\Responses->listRecordCount() #7 /opt/bitnami/apache/htdocs/intuition/controllers/ControllerBase.php(34): PHPMaker2024\intuition\ResponsesList->run() #8 /opt/bitnami/apache/htdocs/intuition/controllers/ResponsesController.php(22): PHPMaker2024\intuition\ControllerBase->runPage() #9 /opt/bitnami/apache/htdocs/intuition/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(38): PHPMaker2024\intuition\ResponsesController->list()
…

6

Post code near line 1025.

8

Line 1025 is in this bit specifically

        } else {
            **$this->TotalRecords = $this->listRecordCount();**
            $this->StartRecord = 1;
            if ($this->DisplayRecords <= 0 || ($this->isExport() && $this->ExportAll)) { // Display all records
                $this->DisplayRecords = $this->TotalRecords;
            }
            if (!($this->isExport() && $this->ExportAll)) { // Set up start record position
                $this->setupStartRecord();
            }
...
9

Post generated code of listRecordCount(). If you have Recordset_Selecting server event, post it too.

10 
    // Get record count (for current List page)
    public function listRecordCount()
    {
        $filter = $this->getSessionWhere();
        AddFilter($filter, $this->CurrentFilter);
        $filter = $this->applyUserIDFilters($filter);
        $this->recordsetSelecting($filter);
        $isCustomView = $this->TableType == "CUSTOMVIEW";
        $select = $isCustomView ? $this->getSqlSelect() : $this->getQueryBuilder()->select("*");
        $groupBy = $isCustomView ? $this->getSqlGroupBy() : "";
        $having = $isCustomView ? $this->getSqlHaving() : "";
        $sql = $this->buildSelectSql($select, $this->getSqlFrom(), $this->getSqlWhere(), $groupBy, $having, "", $filter, "");
        $cnt = $this->getRecordCount($sql);
        return $cnt;
    }

This is empty

// Recordset Selecting event
function Recordset_Selecting(&$filter)
{
    // Enter your code here
}
11

In function getRecordCount(), try change:

$pattern = '/^SELECT\s([\s\S]+)\sFROM\s/i';

to

$pattern = '/^SELECT\s([\s\S]+?)\sFROM\s/i';
12

Well done! That has worked - Can you explain why?

public function getRecordCount($sql, $c = null)
{
    $cnt = -1;
    $sqlwrk = $sql instanceof QueryBuilder // Query builder
        ? (clone $sql)->resetQueryPart("orderBy")->getSQL()
        : $sql;
    // $pattern = '/^SELECT\s([\s\S]+)\sFROM\s/i';
	$pattern = '/^SELECT\s([\s\S]+?)\sFROM\s/i';
    // Skip Custom View / SubQuery / SELECT DISTINCT / ORDER BY
13

The greedy regular expression gets more characters than it needs from the SQL.

14

Si I presume I need to apply the change every time I regenerate, or is there a way to generate it?