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

+1 just for signing off your post with :wq. How do you know that the var is being added as 'Array'? — Tim Fountain
– Tim Fountain, Commented Dec 16, 2013 at 16:43

Mubo · Accepted Answer · 2013-12-16 18:25:43Z

3

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

answered Dec 16, 2013 at 18:25

Mubo

1,0808 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ZF2 - bind array to single database query parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related