zend framework where query with regexp

Question

I want to search for data within the database with these conditions

SELECT * 
FROM SMS_Reports 
WHERE AWB_NO REGEXP '1500' 
   OR SMS_TEXT REGEXP 'SOME STRING' 
   OR Update_at LIKE '%some string%';

I want to query in the database with the above criteria please help me with the query for Zend framework

Use raw query method.. -> stackoverflow.com/questions/6161370/… most easy way of doing this.. — Raymond Nijland
– Raymond Nijland, Commented Aug 2, 2019 at 12:03
@RaymondNijland I want to write it with SQL object $sql = new Sql($this->adapter); $select = $sql->select(array('t1' => $this->table)); — Shailendra Gupta
– Shailendra Gupta, Commented Aug 2, 2019 at 12:05
Zend does not support MySQL's keyword REGEXP native -> stackoverflow.com/questions/27709016/… — Raymond Nijland
– Raymond Nijland, Commented Aug 2, 2019 at 12:32

Shailendra Gupta · Accepted Answer · 2019-08-02 14:13:49Z

here what I found and its work for me

$sql = new Sql($this->adapter);
    $select = $sql->select(array('t1' => $this->table));
    $select->columns(array(
        'From' => 'SMS_From',
        'AWB_NO',
        'Text' => 'SMS_TEXT',
        'Status',
        'Send_at' => new Expression('IF(t1.Update_at IS NOT NULL,t1.Update_at,"")')
    ));
    if (isset($data['search_string']) && !empty($data['search_string'])) {
        $date = date('Y-m-d',strtotime($data['search_string']));
        $select->where(array(
            new PredicateSet(
                array(
                    new PredicateExpression("Update_at = '".$date."'"),
                    new PredicateExpression("SMS_TEXT REGEXP '".$data['search_string']."'"),
                    new PredicateExpression("AWB_NO REGEXP '".$data['search_string']."'"),
                ),
                PredicateSet::COMBINED_BY_OR
            ),
        ));
    }
    $select->join(array('t2' => 'MST_Clients'),'t1.Client_ID = t2.Client_ID',
        array(
            'Client_Name' => new Expression('IF(t2.Client_Name IS NOT NULL,t2.Client_Name,"")'),
        ),'LEFT');
    // echo $sql->getSqlStringForSqlObject($select);die; 
    if($paging) {
        $dbAdapter = new DbSelect($select, $this->getAdapter ());
        $paginator = new Paginator($dbAdapter);
        return $paginator;
    }else {
        $smt = $sql->prepareStatementForSqlObject($select);
        $result = $this->resultSetPrototype->initialize($smt->execute())->toArray();
        return $result;
    }

Collectives™ on Stack Overflow

zend framework where query with regexp

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related