Error 1064 <42000> on mysql when selecting using double quoted strings

Question

The query produced by Zend Framework 2:

SELECT "uc".*, "c".* FROM "user_contacts" AS "uc" INNER JOIN "contacts" AS "c" ON "uc"."contact_id" = "c"."contact_id" WHERE "uc"."user_id" = '2' AND "c"."user_id" = '1';

results in this error (when run on command line):

ERROR 1064 (42000): 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 '.*, "c".* FROM "user_contacts" AS "uc" INNER JOIN "contacts" AS "c" ON "uc"."con' at line 1

This query (exact same query minus double quotes) runs fine:

SELECT uc.*, c.* FROM user_contacts AS uc INNER JOIN contacts AS c ON uc.contact_id = c.contact_id WHERE c.user_id = 2 AND uc.user_id = 1;

+---------+------------+------------+---------+
| user_id | contact_id | contact_id | user_id |
+---------+------------+------------+---------+
|       1 |          7 |          7 |       2 |
+---------+------------+------------+---------+
1 row in set (0.00 sec)

Why is this so and how can I fix this?

Using AMP stack on Ubuntu 12.10.

Tables look like so:

CREATE TABLE `contacts` (
    `contact_id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    PRIMARY KEY (`contact_id`),
    UNIQUE KEY `contact_id_UNIQUE` (`contact_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

CREATE TABLE `user_contacts` (
    `user_id` int(11) NOT NULL,
    `contact_id` int(11) NOT NULL,
    PRIMARY KEY (`user_id`,`contact_id`),
    KEY `user_contacts_user_id_fkey_idx` (`user_id`),
    KEY `user_contacts_contact_id_idx` (`contact_id`),
    CONSTRAINT `user_contacts_contact_id_fkey` FOREIGN KEY (`contact_id`) REFERENCES    `contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `user_contacts_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Zend db adapter code:

return array(
    'db' => array(
        'driver'         => 'Pdo',
        'dsn'            => 'mysql:dbname=' . $dbName . ';host=' . $host,
        'driver_options' => array(
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
        ),
    ),
    'service_manager' => array(
        'factories' => array(
            'Zend\Db\Adapter\Adapter'
                => 'Zend\Db\Adapter\AdapterServiceFactory',
        ),
    ),
);

Select code:

public function checkIfFriends($currentUserId,$requestedUserId) {
    $currentUserId      = (int) $currentUserId;
    $requestedUserId    = (int) $requestedUserId;

    $sql = new Sql($this->tableGateway->getAdapter());

    $select = $sql->select();
    $select->from(array('uc' => $this->tableGateway->getTable()))
        ->join(array('c' => 'contacts'), 'uc.contact_id = c.contact_id');

    $where = new Where();
    $where
        ->equalTo('uc.user_id', $currentUserId)
        ->equalTo('c.user_id', $requestedUserId);
    $select->where($where);


    //echo $select->getSqlString();
    $rowSet = $this->tableGateway->selectWith($select);
    $row = $rowSet->current();
    return ($row) ? true: false;  
}

Why has this been closed as a exact duplicate? it isn't. I understand the problem might be the same but ZF2 is producing a query which doesn't run due to the quoting.

Does ZF know you are working with MySQL? You must not have ANSI_QUOTES enabled, though it expects you to. — Michael Berkowski, Jan 01 '13 at 19:54
`SET sql_mode=ANSI_QUOTES` will enable it, but it is peculiar that ZF is quoting that way (to me anyway, being unfamiliar with ZF) — Michael Berkowski, Jan 01 '13 at 19:55
Yeah it does. I've updated the question with my adapter code — Aydin Hassan, Jan 01 '13 at 19:58
Shouldn't you use Pdo_Mysql instead of just Pdo as driver? Because to me it looks like it's using regular SQL escaping (double quotes) instead of MySQL (back quotes). — joostdevries, Jan 01 '13 at 20:02
Well that's what was on Zend Framework 2 Getting Started guide. Tried it though and it is still the same. — Aydin Hassan, Jan 01 '13 at 20:04
Btw I have lots of other queries which run fine from Zend. It is just this one with the Join. — Aydin Hassan, Jan 01 '13 at 20:06
The dupe CV was a mistake, and flagged. Please don't CV as a dupe to that one... — Michael Berkowski, Jan 01 '13 at 20:06
I just found [this ZF forum post](http://zend-framework-community.634137.n4.nabble.com/ZF2-PDO-mysql-quoting-problem-td4656047.html) describing the same issue and a solution. — Michael Berkowski, Jan 01 '13 at 20:09
@MichaelBerkowski Thanks, that solution does work, it however isn't a very clean one :( — Aydin Hassan, Jan 01 '13 at 20:21
@AydinHassan Since I didn't find a comparable question already here on Stack Overflow, I would suggest you post the solution that did work for you as an answer below, even though it is ugly. — Michael Berkowski, Jan 01 '13 at 20:24
Why do you want to quote your column and table names anyway? — Andy Lester, Jan 02 '13 at 17:49
Have you even read the question? I don't. ZF2 is producing this query which doesn't execute. It should execute. It executes fine without quotes on the command line. If it shoudln't be quoted in the way it is. Why is it? — Aydin Hassan, Jan 02 '13 at 19:14

score 1 · Accepted Answer · edited Jan 01 '13 at 20:33

Solution as found in this Zend Framework forum post:

Works but is not really an acceptable solution, a better solution would be much appreciated.

public function checkIfFriends($currentUserId,$requestedUserId) {
    $currentUserId      = (int) $currentUserId;
    $requestedUserId    = (int) $requestedUserId;

    $sql = new Sql($this->tableGateway->getAdapter());

    $select = $sql->select();
    $select->from(array('uc' => $this->tableGateway->getTable()))
        ->join(array('c' => 'contacts'), 'uc.contact_id = c.contact_id');

    $where = new Where();
    $where
        ->equalTo('uc.user_id', $currentUserId)
        ->equalTo('c.user_id', $requestedUserId);
    $select->where($where);

    $dbAdapter = $this->tableGateway->getAdapter();
    $string = $sql->getSqlStringForSqlObject($select);
    $rowSet = $dbAdapter->query($string, $dbAdapter::QUERY_MODE_EXECUTE);

    $row = $rowSet->current();
    return ($row) ? true: false;  
}

score 0 · Answer 2 · answered Apr 19 '14 at 11:47

0

I solved this problem by turning off the dead key settings in the OS. How to do this in Linux Mint: http://forums.linuxmint.com/viewtopic.php?f=55&t=97812

answered Apr 19 '14 at 11:47

user3127145

19
2

Error 1064 <42000> on mysql when selecting using double quoted strings

2 Answers2