Zend Framework 2 : Getting real Sql String of Zend\Db
I know, this is maybe a simple post for you, but it useful for me :). Hope it useful for someone. When we build a query using Zend\Db, sometime, complex query need checking for debugging to make sure our query is right. For example, we need to make sure we build a query like this :
SELECT `album`.* FROM `album` WHERE `title` = 'abracadabra' AND (`id` = '1' OR `artist` = 'Tony') LIMIT 1
And we already have an \Zend\Db\Sql\Select instance. In case if you have Model class like zf2 docs provide
$sql = $this->tableGateway->getSql();
$select = $sql->select();
$select->where(array('title' => 'abracadabra'));
$select->where
->NEST->
equalTo('id', 1)
->OR->
equalTo('artist', 'Tony')
->UNNEST;
$select->limit(1);
So, Before we return it with :
return $this->tableGateway->selectWith($select);
We can check it with :
echo $sql->getSqlstringForSqlObject($select); die ; // ( die/exit to debugging purpose ) //it will print sql string : // SELECT `album`.* FROM `album` WHERE `title` = 'abracadabra' AND (`id` = '1' OR `artist` = 'Tony') LIMIT 1
That will return the real sql build for our specific platform.
references :
https://github.com/zendframework/zf2/issues/3224
Welcome to Abdul Malik Ikhsan's Blog 
How to output in Doctrine case?
$this->queryBuilder = $this->getEntityManager()->createQueryBuilder();
$this->queryBuilder->select(“c”)->from($this->getEntityName(), “c”);
Then i’m build some query and then get it
$query = $this->queryBuilder->getQuery();
Now i’m can output it like this
\Doctrine\Common\Util\Debug::dump($query->getSQL());
But there i’m will see ? in places, where i placed params.
How to see query with binded param values?
that’s not related with zf2 i think 🙂
I normally use $select->getSqlString(). Is there a difference?
answered on fb 😉
Hi Samsonasik,
Thanks for the useful post. Waiting for the post about Apigility…. Hope it would be coming soon
thanks. I hope I have time to do it 😉
sir, i have been searching calender or date code, alongwith upload fiels which shows progress meter am not getting it
1) in one form i should have fields which have date field but when u click inside text box calendar should open 2) upload form for all types doc/pdf/image/txt with multiple which also shows progress in percentage 3) when u click submit or save button all above function should save to database and upload to particular path 5) i have tried zf2fileupload examples but not getting progress bar 6) date i have used combo box
Thanks for these great tutorials! These have saved me many hours.
Thanks. :)))
Hi Adbul,
How do you get the sql for an insert? (Except from using a debugger)
Hi Bart,
As far as I know, the way to do for insert is by define insert object:
use Zend\Db\Sql\Insert; $insertObj = new Insert; $insertObj->into('test'); $insertObj->values(array('name' => 'samsonasik')); echo $this->tableGateway->getSql()->getSqlStringForSqlObject($insertObj);;
Nice one! I ended up using the debugger to step through and find the generated sql that way.
just like this blog how to use the following line in zend.i am stuck with it.
“SELECT * FROM blogdata WHERE description LIKE ‘%” . $term . “%'”;
this is my method in model
public function getBlogsWithSearch($term){
try{
$sql = $this->getSql();
$query = $sql->select()
->from($this->tableName)
->where(“description LIKE ?”, “%{$term}%”)
->columns(array(“title”,”description”));
$blogs = array();
$resultSet = $sql->prepareStatementForSqlObject($query)->execute();
foreach($resultSet as $row){
$blogs[] = $row;
}
return $blogs;
}catch(\Exception $e){
return array();
}
}
it is always returing zero.please help me brother.thanks in advance
you can do something like this:
$select->where->like("description", "%$term%");