How to print exact sql query before executing

For programming and general questions on Zend Framework

How to print exact sql query before executing

Postby anil6976 on Thu Dec 08, 2016 7:38 am

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

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.

Code: Select all
$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.

Code: Select all
$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?

Thank you!
anil6976
 
Posts: 1
Joined: Thu Dec 08, 2016 7:34 am

Re: How to print exact sql query before executing

Postby mehm8471 on Tue Jan 03, 2017 10:54 am

UPDATE and DELETE requires you to provide the ID up-front - meaning you already should know the ID.

For UPDATE, DELETE and INSERT - you can still have Delete(), Insert() and Update() objects like you have Select() and all these have getSqlString() method returns sql string. Like:

Code: Select all
print $insert->getSqlString();


Additionally: Create select (and even others) as below instead:
Code: Select all
$select = new Select($this->tableGateway->table);
mehm8471
 
Posts: 20
Joined: Sat Jun 21, 2014 11:39 pm

Re: How to print exact sql query before executing

Postby dhar9669 on Mon Jan 16, 2017 9:15 am

1. You can use $sql->getSqlStringForSqlObject($select) or $select->getSqlString() function to print the sql query.
2. To get the affected Rows of Delete Query, You can use getAffectedRows() function.
Let me know if it helps.
dhar9669
 
Posts: 5
Joined: Fri Jan 13, 2017 2:58 pm


Return to Zend Framework

Who is online

Users browsing this forum: No registered users and 4 guests