ZF2 - bind array to single database query parameter

Question

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

Answer 1

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);