ZF2 PDO mysql quoting problem

[GJ Bogaerts]

Hi all,

I've got this code:
        $select = new Select(array('m' => 'menus'));
        $select
            ->join(array('i' => 'menu_items'), 'i.menuId = m.id', $select::SQL_STAR, $select::JOIN_LEFT)->order('m.position');
        $string = $select->getSqlString();
        $resultSet = $adapter->query($string, $adapter::QUERY_MODE_EXECUTE);

However, upon execution I get an Exception with the 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 '.*, "i".* FROM "menus" AS "m" LEFT JOIN "menu_items" AS "i" ON "i"."menuId" = "m' at line 1

The problem seems to be that not the proper quote-identifiers are used. The querystring that is produced is:
SELECT "m".*, "i".* FROM "menus" AS "m" LEFT JOIN "menu_items" AS "i" ON "i"."menuId" = "m"."id" ORDER BY "m"."position" ASC

Notice the double quotes...

Can anybody shed some light on what's wrong here?

Thanks for any help!

GJ

[GJ Bogaerts]

Solved...

Instead of this:

        $select = new Select(array('m' => 'menus'));
        $select
            ->join(array('i' => 'menu_items'), 'i.menuId = m.id', $select::SQL_STAR, $select::JOIN_LEFT)->order('m.position');
        $string = $select->getSqlString();
        $resultSet = $adapter->query($string, $adapter::QUERY_MODE_EXECUTE);

I needed to do this:
        $sql = new Sql($dbAdapter);
        $select = new Select(array('i'=>'menu_items'));
        $select->join(array('m'=>'menus'), 'm.id=i.menuId', array('name'), $select::JOIN_LEFT)->order('m.position asc, i.position asc');
        $string = $sql->getSqlStringForSqlObject($select); // I get the string of the Sql, instead of the Select-instance
        $resultSet = $dbAdapter->query($string, $dbAdapter::QUERY_MODE_EXECUTE);