How to print exact sql query before execute in Zend Framework 2

Question

I am working on an application using Zend framework 2. I'm using TableGateway to select, insert, update and delete query.

1. My question is how to print exact sql query before executing INSERT, UPDATE and DELETE statement? For SELECT statement here is my code which is working for me.

$selectedTable = new TableGateway($this->tblName, $this->dbAdapter);
$sql = $selectedTable->getSql();
$select = $sql->select();

if ($trace) {
    echo "<br>" . $sql->getSqlstringForSqlObject($select) . "<br>";
    exit;
} 
else {
    $resultSet = $selectedTable->selectWith($select);
    unset($selectedTable);
    return $resultSet;
}

2. For last inserted id I'm using this code and working fine.

$selectedTable = new TableGateway($this->tblName, $this->dbAdapter);
$selectedTable->insert($dataArray); 
$insertId = $selectedTable->adapter->getDriver()->getConnection()->getLastGeneratedValue();
unset($selectedTable);
return $insertId;

But for UPDATE how to get last updated id? and for DELETE how to get affected row? Because for UPDATE and DELETE this code is not working.

Can anyone suggest how to do these job?

Al Foиce ѫ · Accepted Answer · 2016-12-09 09:55:17Z

3

1. There should be no difference nor difficulties, do it exaclty the same way on your $insert object. Here how I perform Sql insert and get the SQL string:

$sql = new Sql($this->dbAdapter);
$insert = $sql->insert('table');
[...]
$sqlString = $insert->getSqlString($this->dbAdapter->getPlatform());

2. When you insert a value, you do not know what will be the generated value id before insertion, but you will only know it ater insertion. That's why there is the getLastGeneratedValue-) method for inserted values.

But when you update or delete a value, its id is already defined and you can read it. So all you have to do is to read it from your database. Perform a select before updating or deleting your(s) objetct(s) and you will know all the ids you want.

edited Dec 9, 2016 at 9:55

answered Dec 9, 2016 at 9:50

Al Foиce ѫ

4,33112 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Katty Over a year ago

Thanks for your reply. Can you tell me how to use getaffectedrows() with update and delete using TableGateway?

Collectives™ on Stack Overflow

How to print exact sql query before execute in Zend Framework 2

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related