zend-dbzend-framework3

string with wildcards using in select


I have some syntax trouble using zend-select,

I get fields and values out of a searchform, I'm using an array which comes out for example like this (everything here ist out of my model):

 shot

Because of the fact that the array-parameters (vorname and nachname (mandant will be integer)) are type string, I want to use wildcards in my select, if the fields vorname or/and nachname are filled by the user.

I tried this one to learn how to write it properly, which works:

$select->where(['nachname LIKE ?'=>'%idt%']); 
$select->where(['vorname LIKE ?'=>'%tan%']); 

To get the string right for using in my select, I uses this quite uncomfortable and besides coming up with an error, code:

foreach ($suche as $key=>$val) {
    if (($key=="vorname") && (strlen($suche['vorname'])>0)){
        $kritvn = "['" . $key . " LIKE " . chr(63) . " => '%" . $val . "%']";
    }
    if (($key=='nachname') && (strlen($suche['nachname'])>0)){
        $kritnn = "['" . $key . " LIKE ?'=>'%" . $val ."%']";
    }
}

In the first one, I played a bit around with the syntax for "?", if I use the next one, I geht the following error;

$kritvn = "['" . $key . " LIKE ?'=>'%" . $val ."%']";

for the right syntax to use near '['vorname LIKE '=>'%%tan%%'])

I tried with the function chr() and before with \?, same problem will be with the "%".

So in the end I have indeed two questions:

What would be the proper technik to reach the result?

If there isn't a better solution, what I don't think, so how should I write it properly?

if $key = 'vorname' and the value is perhaps tan, I would appreciate a where kondition which looks like this one:

 $select->where(['vorname LIKE ?'=>'%tan%']); 

And perhaps, there ist a better way, the problem occurred because now I wanted wildcards ;-)


Solution

  • The array you can send when using select->where() Zend will just transform that in to a where statement with equalto's

    for example

    $select->where(['vorname LIKE ?'=>'%tan%']); 
    

    becomes the

    WHERE `vorname LIKE ?` = "%tan%"
    

    to use it like you have to use the where objects methods like this

    $where = new \Zend\Db\Sql\Where();
    $where->like("vorname", "%tan%");
    $select->where($where);
    

    or you can use the sorter version like this where Zend creates the where object for you.

     $select->where->like("vorname", "%tan%");