0

I am getting this error 

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 '' at line 1
Stack trace:
#0 H:\Documents\IIS_Server_Root\zendframework\Zend\Db\Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 H:\Documents\IIS_Server_Root\zendframework\Zend\Db\Adapter\Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 H:\Documents\IIS_Server_Root\zendframework\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Table_Select), Array)
#3 H:\Documents\IIS_Server_Root\zendframework\Zend\Db\Table\Abstract.php(1526): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Table_Select))
#4 H:\Documents\IIS_Server_Root\zendframework\Zend\Db\Table\Abstract.php(1342): Zend_Db_Table_Abstract->_fetch(Object(Zend_Db_Table_Select))
#5 H:\Documents\IIS_Server_Root\my.localhost\ahaweb\application\models\Tagjoin.php(28): Zend_Db_Table_Abstract->fetchAll(Object(Zend_Db_Statement_Pdo))
#6 H:\Documents\IIS_Server_Root\my.localhost\ahaweb\application\models\Tag.php(44): Model_Tagjoin->getTags('12')
#7 H:\Documents\IIS_Server_Root\my.localhost\ahaweb\application\models\Bookmark.php(30): Model_Tag->getTags('12')
#8 H:\Documents\IIS_Server_Root\my.localhost\ahaweb\application\controllers\UserController.php(69): Model_Bookmark->getUserBookmark(1, '12')
#9 H:\Documents\IIS_Server_Root\zendframework\Zend\Controller\Action.php(513): UserController->editAction()
#10 H:\Documents\IIS_Server_Root\zendframework\Zend\Controller\Dispatcher\Standard.php(295): Zend_Controller_Action->dispatch('editAction')
#11 H:\Documents\IIS_Server_Root\zendframework\Zend\Controller\Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#12 H:\Documents\IIS_Server_Root\zendframework\Zend\Application\Bootstrap\Bootstrap.php(97): Zend_Controller_Front->dispatch()
#13 H:\Documents\IIS_Server_Root\zendframework\Zend\Application.php(366): Zend_Application_Bootstrap_Bootstrap->run()
#14 H:\Documents\IIS_Server_Root\my.localhost\ahaweb\public\index.php(26): Zend_Application->run()

In this code 

    $tj = new Model_Tagjoin();                
    $stmt = $tj->select('*')->setIntegrityCheck(false)
            ->joinInner("tags", "tags.tag_id = ".$this->_name.".tag_id",array("tag_name"))
            ->where($this->_name.".bmk_id = ?", $bmk_id)->query();                
    $r = $tj->fetchAll($stmt);

When I print_f the value of $stmt I get 

SELECT `tagjoins`.*, `tags`.`tag_name` FROM `tagjoins`
INNER JOIN `tags` ON tags.tag_id = tagjoins.tag_id WHERE (tagjoins.bmk_id = '12')

which works fine if I use it directly with mysql.

Please help. Whats wrong with my code?

Glen Solsberry
  • 11,960
  • 15
  • 69
  • 94
Future King
  • 3,819
  • 6
  • 28
  • 37
  • Check grants of your Zend db configuration user? – AJ. May 09 '11 at 19:36
  • How to check "grants" of Zend Db Configuration user? I am using mysql root password to connect to mysql. – Future King May 09 '11 at 19:43
  • run `SHOW GRANTS FOR 'user'@'hostname';` with the value specified in your Zend db config for user and the hostname from which your application is connecting. – AJ. May 09 '11 at 19:52
  • I got this information: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7' WITH GRANT OPTION – Future King May 09 '11 at 20:02
  • even if I run this code: SELECT `tagjoins`.*, `tags`.`tag_name` FROM `tagjoins` INNER JOIN `tags` ON tags.tag_id = tagjoins.tag_id WHERE tagjoins.bmk_id = '12' in zend framework getadapter->query() I get the same error – Future King May 09 '11 at 20:24
  • Are you **really** using root as your Zend db configured user? This should be a different user that only has the minimal privileges for what the application needs to do in the database. Double-check your config.ini and make sure this is not the case. – AJ. May 09 '11 at 20:49
  • this is what I have in application.ini resources.db.adapter = "pdo_mysql" resources.db.params.host = "localhost" resources.db.params.username = "root" resources.db.params.password = "pass" resources.db.params.dbname = "my_ahaweb" resources.db.isDefaultTableAdapter = true – Future King May 10 '11 at 05:52
  • an strange thing that I found is if I run same query using mysql_query() function. it works perfectly. – Future King May 10 '11 at 10:24
  • the above query works if I use PDO objects. But using Zend_Db it doesn't work. Please help please help – Future King May 10 '11 at 10:36

1 Answers1

0

I don't know about using printf but as I have read it in the manual you should use the assemble() method to see your final sql query.

something like this.

$tj = new Model_Tagjoin();                
$stmt = $tj->select('*')->setIntegrityCheck(false)
        ->joinInner("tags", "tags.tag_id = ".$this->_name.".tag_id",array("tag_name"))
        ->where($this->_name.".bmk_id = ?", $bmk_id)->query()->assemble();
exit($stmt);                
$r = $tj->fetchAll($stmt);

Then try the query in phpMyAdmin and the like. As a note if this call fails you might wanna remove the ->query() part of the line.

Perfection
  • 199
  • 1
  • 10
  • assemble() gave me this: SELECT `tagjoins`.*, `tags`.`tag_name` FROM `tagjoins` INNER JOIN `tags` ON tags.tag_id = tagjoins.tag_id WHERE (tagjoins.bmk_id = '12') – Future King May 09 '11 at 19:53