CODEWITHSUNDEEP
phpzend-framework2
familymangreg
familymangreg

Reputation: 1282

ZF2 - bind array to single database query parameter

I'm trying to run a simple query against a Zend\Db\Adapter\Adapter instance. Some sample code:

    $sql = "DELETE FROM Goals WHERE match_no = ? AND event_id NOT IN (?) ";

    $res = $this->adapter->query($sql, array($matchNo, $goalIds));
    return $res->getAffectedRows();

This won't work unfortunately, as the array $goalIds isn't quoted as a list for the IN () part of the SQL, but instead is placed in the SQL as 'Array'.

I've searched and search, and also played with the ZF2 ParameterContainer, but can't work out how to quote an array into a single parameter of comma separated values like this.

I'm pretty sure this could work if I used DB Select functionality, but I'd rather just keep to plain old SQL and parameter for these type of quick queries.

Any help much appreciated.

:wq

Upvotes: 3

Views: 1777

Answers (1)

Mubo
Mubo

Reputation: 1070

Assuming you are using MySQl Adapter, You can do things in the Zend way by writing this:-

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



$config = array(
    'driver'  => 'Pdo',
    'dsn'     => 'mysql:dbname=database;host=localhost;charset=utf8',
    'user'    => 'root',
    'pass'    => 'password',
);
$adapter= new Adapter($config);



$sql = new Sql($adapter);
$select = $sql->select();
$select->from('Goals');
$select->where->notin('match_no',  array($matchNo, $goalIds));
$select->where->notin('event_id',  array($matchNo, $goalIds));

$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
Debug::dump($results);

Upvotes: 3

Related Questions