Implementing where IN clause using Zend_Db Api's

Question

I am trying to code the following query using Zend_Db api's:

select * from tableA where id not in (select pid from tableB where x =46 and y='participated');

I wrote the following code to get this to work:

First I am getting the list of pid's in array format from tableB:

    $select = $this->select()
    ->from(array('tb' =>'tableB'), array('mylist' => new Zend_Db_Expr('group_concat(tb.pid)' )))
    ->where('x = ?', $xval) //$xval is coming as 46
    ->where('type = ?', 'participated');

    $result = $this->getAdapter()->fetchAll($select);
    error_log("Result of query1 is " . print_r($result, true));

    //Convert to array
    $mylistArr = preg_split('/,/' , $result[0]['mylist'], PREG_SPLIT_NO_EMPTY);
    error_log("value of mylistArr is " . print_r($mylistArr, true));


    //Now get the results from tableA
    $selectta = $this->select()
    ->setIntegrityCheck(false)
    ->from(array('ta' => 'tableA'), array('ta.id', 'ta.first_name', 'ta.last_name'))
    ->where('ta.id not in (?)', $mylistArr[0]);

    $result = $this->fetchAll($selectta);
    error_log("db query result is " . print_r($result, true));

Now, the problem is this: The final query being formed is

SELECT `ta`.`id`, `ta`.`first_name`, `ta`.`last_name` FROM `tableA` AS `ta` WHERE (ta.id not in ('197,198,199,200,106,201,202,204,203,205'))

However, I want the query to look as follows (that is, the list of id's from tableB should not be enclosed in inverted commas):

SELECT `ta`.`id`, `ta`.`first_name`, `ta`.`last_name` FROM `tableA` AS `ta` WHERE (ta.id not in (197,198,199,200,106,201,202,204,203,205))

The reason is that when inverted commas are being passed in the IN clause, only the first id ie 197 is being picked up to filter the results.

Any help is really appreciated.

Thanks

can you put the output only of $mylistArr[0]?, why don't you use implode instead of preg_split? — jcho360
– jcho360, Commented Jun 13, 2012 at 18:32
output of $mylistArr[0] is [0] => 197,198,199,200,106,201,202,204,203,205 There is no particular reason for not using implode. Will it make a difference? — user_stackoverflow
– user_stackoverflow, Commented Jun 13, 2012 at 18:42

Community · Accepted Answer · 2017-05-23 10:34:48Z

2

please take a look to this site where they are using ZEND and make a select... where.. IN...

How to do MySQL IN clauses using Zend DB?

let us know how it goes.

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Jun 13, 2012 at 18:45

jcho360

3,7491 gold badge18 silver badges25 bronze badges

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

1 Comment

user_stackoverflow Over a year ago

Thank you for pointing out in the right direction. I needed to use explode (not implode) to pass the parameter as an array. As per my original code, I was passing a string of id's delimited by comma's.

user_stackoverflow · Accepted Answer · 2012-06-13 18:46:12Z

1

I found one way of implementing this. But not sure if this is the best way: I modified the following query:

   $selectta = $this->select()
    ->setIntegrityCheck(false)
    ->from(array('ta' => 'tableA'), array('ta.id', 'ta.first_name', 'ta.last_name'))
    ->where('ta.id not in (?)', $mylistArr[0]);

To:

  $selectta = $this->select()
    ->setIntegrityCheck(false)
    ->from(array('ta' => 'tableA'), array('ta.id', 'ta.first_name', 'ta.last_name'))
    ->where('not find_in_set(ta.id , ?)', $mylistArr[0]);

And it gives me the appropriate results.

Any suggestions on improving this answer are most welcome.

answered Jun 13, 2012 at 18:46

user_stackoverflow

7742 gold badges10 silver badges19 bronze badges

Comments

user_stackoverflow · Accepted Answer · 2012-06-13 18:59:18Z

Instead of using preg_split, I needed to use explode and convert the comma delimited string to an array of id's that could be passed to the where IN clause.

The following is the final implementation I did:

 $select = $this->select()
    ->from(array('tb' =>'tableB'), array('mylist' => new Zend_Db_Expr('group_concat(tb.pid)' )))
    ->where('x = ?', $xval) //$xval is coming as 46
    ->where('type = ?', 'participated');

    $result = $this->getAdapter()->fetchAll($select);
    error_log("Result of query1 is " . print_r($result, true));

    //Convert to array
    $mylistArr = explode(",", $result[0]['mylist']);
    error_log("value of mylistArr is " . print_r($mylistArr, true));


    //Now get the results from tableA
    $selectta = $this->select()
    ->setIntegrityCheck(false)
    ->from(array('ta' => 'tableA'), array('ta.id', 'ta.first_name', 'ta.last_name'))
    ->where('ta.id not in (?)', $mylistArr);

    $result = $this->fetchAll($selectta);
    error_log("db query result is " . print_r($result, true));

Collectives™ on Stack Overflow

Implementing where IN clause using Zend_Db Api's

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related