0

I am creating a website using PHP and Zend Framework. I am uisng the sqlsrv adaptor to talk to my database. I am trying to write an insert query and also store the ID of the new row. I have written an SQL query like this:

$db = Zend_Db_Table::getDefaultAdapter();               
$id = $db->fetchOne("
    INSERT INTO    MyTable
    (Title, CreatedBy, Created, LastUpdatedBy, LastUpdated)
    SELECT  'MY Title',
        UserTable.UserId,
        CONVERT(datetime, '".$this->details["Created"]."', 120),
        UserTable.UserId,
        CONVERT(datetime, '".$this->details["LastUpdated"]."', 120)
    FROM    UserTable
    WHERE   UserName = '".$this->details["CreatedBy"]."'
    SELECT  CAST(SCOPE_IDENTITY() AS int) AS Id"
);

but it generates this error:

'Zend_Db_Statement_Sqlsrv_Exception' with message 'The active result for the query contains no fields.

How can I avoid this error and run this query and retreive the inserted row Id?

Random Developer
  • 175
  • 5
  • 17
  • if you really want to use SQL you can use [Zend_Db_Statement](http://framework.zend.com/manual/en/zend.db.statement.html) – RockyFord Jun 07 '12 at 10:43

1 Answers1

1

You seem to be using the wrong method here.

You should be using the insert() method of Zend_Db_Table:-

$table = new Zend_DB_Table(array('name' => 'mytable', primary => 'myPrimaryKey'));
$data = array()// of data to be inserted
$id = $table->insert($data);
vascowhite
  • 18,120
  • 9
  • 61
  • 77
  • I wanted to avoid using methods like insert() as I am much more confident using pure MS SQL statements. Is it really necesary to use the insert function? How can I replicate the query I posted? – Random Developer Jun 06 '12 at 11:51
  • Zend_Db isn't designed to be used like that. I'm sure there is a way around it, but it isn't good practice to bypass these methods and I've never needed to, so I can't help you with that sorry. Why use a framework and them not learn to use it properly though? – vascowhite Jun 06 '12 at 11:57