We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Mysql error Syntax error, unexpected token COMMA ...

I get error: Syntax error, unexpected token COMMA, near to 'col2) IN (("4081","8510"),("8510","4081"),...

My PHP-code:

$where = [];

foreach ($ids as $item) {
      $where[] = '("' . $item['col1'] . '","' . $item['col2'] . '")';
}

$sql = 'SELECT * FROM ' . Models\LocalRoute::class . ' WHERE (col1,col2) IN (' . implode(',', $where) . ')';

$di->get('modelsManager')->executeQuery($sql);

I get sql:

SELECT * FROM App\Models\LocalRoute WHERE (col1,col2) IN (("4081","8510"),("8510","4081"),("4081","3845"),("5563","3845"),("3505","3845"),("3845","5563"),("3845","4081"))

Why phalcon can't execute this code?



43.9k

Hi,

does the query work if executed from the mysql shell ?



2.6k
in reply to le51

Hi, yes

Hi,

does the query work if executed from the mysql shell ?



43.9k

that's strange.

This is maybe not directly phalcon related because the framework query databases trough pdo:

https://php.net/manual/en/book.pdo.php

https://docs.phalcon.io/en/3.3/api/Phalcon_Db_Adapter_Pdo



2.6k
in reply to le51

The PDO works.

that's strange.

This is maybe not directly phalcon related because the framework query databases trough pdo:

https://php.net/manual/en/book.pdo.php

https://docs.phalcon.io/en/3.3/api/Phalcon_Db_Adapter_Pdo



145.0k

Well, most likely phalcon doesn't expect multiple columns in WHERE syntax. I guess you would need to make dialect extension.



2.6k

I found one solution:

            use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

            $myModel = new Models\MyModel();

            $sql = "SELECT * FROM " . $myModel->getSource();

            if ($ids) {
                $where = [];

                foreach ($ids as $item) {
                    $where[] = '("' . $item['col1'] . '","' . $item['col2'] . '")';
                }

                $sql .= ' WHERE (col1,col2) IN (' . implode(',', $where) . ')';
            }

            $items = new Resultset(
                null,
                $myModel,
                $myModel->getReadConnection()->query($sql)
            );