CODEWITHSUNDEEP
mysqlzend-framework2
nopaws
nopaws

Reputation: 245

How to run raw SQL Query with Zend Framework 2

Is there a way to execute a SQL String as a query in Zend Framework 2?

I have a string like that:

$sql = "SELECT * FROM testTable WHERE myColumn = 5"

now I want to execute this string directly.

Upvotes: 17

Views: 33896

Answers (4)

FlameStorm
FlameStorm

Reputation: 1004

If you have EntityManager $em on your hands, you can do something like this:

   $select = $em->getConnection()->executeQuery("
        SELECT a.id, a.title, a.announcement, asvc.service_id, COUNT(*) AS cnt,
            GROUP_CONCAT(asvc.service_id SEPARATOR \", \") AS svc_ids
        FROM article AS a
        JOIN articles_services AS asvc ON asvc.article_id = a.id
        WHERE
        asvc.service_id IN (
            SELECT tsvc.service_id
            FROM tender AS t
            JOIN tenders_services AS tsvc ON tsvc.tender_id = t.id
            WHERE t.id = :tenderId
        )
        GROUP BY a.id
        ORDER BY cnt DESC, a.id DESC
        LIMIT :articlesCount
    ", [
        'articlesCount' => 5,
        'tenderId' => $tenderId,
    ], [
        'articlesCount' => \PDO::PARAM_INT,
    ]);

    $result = $select->fetchAll(); // <-- here are array of wanted rows

I think this way to execute complex queries is best for Zend. But may be I'm not very smart in Zend still. Will glad to see if it helps to someone.

Upvotes: 1

user2897139
user2897139

Reputation:

If you are using tableGateway, you can run your raw SQL query using this statement, 

$this->tableGateway->getAdapter()->driver->getConnection()->execute($sql);

where $sql pertains to your raw query. This can be useful for queries that do not have native ZF2 counterpart like TRUNCATE / INSERT SELECT statements.

Upvotes: 4

Weteef
Weteef

Reputation: 1002

Just pass the sql string to your db adapter like this:

$resultSet = $adapter->query($sql, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);

And if you want to pass parameters:

$sql = "SELECT * FROM testTable WHERE myColumn = ?";
$resultSet = $adapter->query($sql, array(5));

EDIT: Please note that the query method does not always returns a resultset. When its a resultset producing query(SELECT) it returns a \Zend\Db\ResultSet\ResultSet otherwise(INSERT, UPDATE, DELETE, ...) it will return a \Zend\Db\Adapter\Driver\ResultInterface.

And when you leave the second Parameter empty you will get a \Zend\Db\Adapter\Driver\StatementInterface which you can execute.

Upvotes: 55

automatix
automatix

Reputation: 14532

use Zend\Db\Sql\Sql;
use Zend\Db\Adapter\Adapter;

$dbAdapterConfig = array(
    'driver'   => 'Mysqli',
    'database' => 'dbname',
    'username' => 'dbusername',
    'password' => 'dbuserpassword'
);
$dbAdapter = new Adapter($dbAdapterConfig);

$sql = new Sql($dbAdapter);
$select = $sql->select();
$select->from('testTable');
$select->where(array('myColumn' => 5));

$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

S. docu: Zend\Db → Zend\Db\Sql

Upvotes: 7

Related Questions