Zend framework SQL select query construction (WHERE CLAUSE)

Question

I am trying to generate a query that selects all from a user table where any combination of the last name of first name matches a particular search term

$select = $select->where('last_name LIKE ?', '%'.$term.'%')->orWhere('first_name LIKE ?', '%'.$term.'%')
                        ->orWhere("CONCAT(first_name,' ', last_name) LIKE ?", '%'.$term.'%')
                        ->orWhere("CONCAT(last_name,' ', first_name) LIKE ?", '%'.$term.'%');

There is another condition that has to also has to be met which is specified in another where clause

$select = $select->where("deleted = 0 AND scholar = 0");

The Following SQL statement is generated

SELECT `user`.* FROM `user` WHERE (last_name LIKE '%frank%') OR (first_name LIKE '%frank%') OR (CONCAT(first_name,' ', last_name) LIKE '%frank%') OR (CONCAT(last_name,' ', first_name) LIKE '%frank%') AND (deleted = 0 AND scholar = 0) ORDER BY `date_created` desc LIMIT 25

This doesnt return the desired result as i get rows where scholar = 1;

I figured the query should be

SELECT `user`.* FROM `user` WHERE ((last_name LIKE '%frank%') OR (first_name LIKE '%frank%') OR (CONCAT(first_name,' ', last_name) LIKE '%frank%') OR (CONCAT(last_name,' ', first_name) LIKE '%frank%')) AND  (deleted = 0 AND scholar = 0) ORDER BY `date_created` DESC LIMIT 25

What the right syntax to achieve this using the $select object.

Mouna Cheikhna · Accepted Answer · 2012-04-08 10:50:23Z

3

You can use quoteInto to prepare your conditions and then use them like this :

    $first_name_cond = $db->quoteInto('first_name LIKE ?', '%'.$term.'%');
    $last_name_cond = $db->quoteInto('last_name LIKE ?', '%'.$term.'%');

    $concat_cond1 = $db->quoteInto("CONCAT(first_name,' ', last_name) LIKE ?", '%'.$term.'%');

    $concat_cond2 = $db->quoteInto("CONCAT(last_name,' ', first_name) LIKE ?", '%'.$term.'%');


    $select = $select->where($first_name_cond.' OR '.$last_name_cond.' OR '.

             $concat_cond1.' OR '.$concat_cond2)->where("deleted = 0 AND scholar = 0");

answered Apr 8, 2012 at 10:50

Mouna Cheikhna

39.8k10 gold badges50 silver badges69 bronze badges

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

Comments

RockyFord · Accepted Answer · 2012-04-09 03:48:13Z

I assume deleted and scholar are separate columns. So the easiest way is to just break:

$select = $select->where("deleted = 0 AND scholar = 0");

into two statements, like:

$select->where("deleted = ?", 0);
$select->where("scholar = ?", 0);

this change should result in a sql string like:

SELECT `user`.* FROM `user` WHERE (last_name LIKE '%frank%')
OR (first_name LIKE '%frank%') 
OR (CONCAT(first_name,' ', last_name) LIKE '%frank%') 
OR (CONCAT(last_name,' ', first_name) LIKE '%frank%') 
AND deleted = 0 AND scholar = 0 ORDER BY `date_created` desc LIMIT 25

also remove the extra $select =. Your whole select should likly look something like:

//first line initializes the select object
//The select object will handle most quoting needs for you

$select = $this->select();

//I like to add expressions this way just to keep things easy to read and easy to edit
//you can string multiple statements together, but I find that harder to edit.

$select->where('last_name LIKE ?', '%'.$term.'%');
$select->orWhere('first_name LIKE ?', '%'.$term.'%');
$select->orWhere("CONCAT(first_name,' ', last_name) LIKE ?", '%'.$term.'%');
$select->orWhere("CONCAT(last_name,' ', first_name) LIKE ?", '%'.$term.'%');
$select->where("deleted = ?", 0);
$select->where("scholar = ?", 0);
$select->order('date_created DESC');
$select->limit(25);

Pang · Accepted Answer · 2015-03-19 05:07:42Z

0

$user = new Application_Model_DbTable_User();
            // User List

$uname=$_POST['uname'];

echo $query = $user->select()->where('firstname LIKE ?', $uname.'%')->ORwhere('lastname LIKE ?', $_POST['lname'].'%')->ORwhere('emailid LIKE ?', $_POST['email'].'%');

$userlist = $user->fetchAll($query);

edited Mar 19, 2015 at 5:07

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Feb 26, 2014 at 12:27

dev4092

2,9111 gold badge18 silver badges15 bronze badges

Collectives™ on Stack Overflow

Zend framework SQL select query construction (WHERE CLAUSE)

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related