MySQL Query Of Multiple Fields With One Variable

Question

I am using the code below in a Code Igniter model to pull data from a database. However my data base has 2 Cust_Phone fields. I would like to set it up so that when the user enters a number phone fields are searched. I think there is a way to do this in the mysql query using sime thing like

select distinct Phone from 
(SELECT Cust_Phone1 AS Phone 
  FROM Customer

UNION

SELECT Cust_Phone2 AS Phone 
  FROM Customer) as t where Phone <> ''

Inside my other select query, but I am unsure how to go about this or if I am even doing so in the proper way.

--- CodeIgniter Model Function ---

public function customerQuery($First,$Last,$Zip,$Phone) {
    $sql = "SELECT * FROM Customer WHERE Cust_First LIKE ? AND Cust_Last LIKE ? AND Cust_Zip LIKE ? AND Cust_Phone1 Like ?"; 
    if($First == null || $First == '') {
        $First = '%';
    }else{
        $First = '%' . $First . '%';
    }
    if($Last == null || $Last == '') {
        $Last = '%';
    }else{
        $Last = '%' . $Last . '%';
    }
    if($Zip == null || $Zip == '') {
        $Zip = '%';
    }else{
        $Zip = '%' . $Zip . '%';
    }
    if($Phone == null || $Phone == '') {
        $Phone = '%';
    }else{
        $Phone = '%' . $Phone . '%';
    }
    $query = $this->db->query($sql, array($First,$Last,$Zip,$Phone));
    return $query->result();
}

RandomSeed · Accepted Answer · 2013-03-15 17:13:01Z

1

Try this:

$sql = "SELECT * FROM Customer WHERE Cust_First LIKE ? AND Cust_Last LIKE ? AND Cust_Zip LIKE ? AND (Cust_Phone1 LIKE ? OR Cust_Phone2 LIKE ?)";
...
...
if($Phone == null || $Phone == '') {
    $Phone = '%';
}else{
    $Phone = '%' . $Phone . '%';
}
$query = $this->db->query($sql, array($First,$Last,$Zip,$Phone,$Phone));

answered Mar 15, 2013 at 17:13

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

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

Comments

Bafsky · Accepted Answer · 2013-03-15 17:12:43Z

0

You can check both phone fields like so...

$sql = "SELECT * FROM Customer WHERE Cust_First LIKE ? AND Cust_Last LIKE ? AND Cust_Zip LIKE ? AND (Cust_Phone1 Like ? OR Cust_Phone2 Like ?)";

...

$query = $this->db->query($sql, array($First,$Last,$Zip,$Phone,$Phone));

answered Mar 15, 2013 at 17:12

Bafsky

7811 gold badge7 silver badges16 bronze badges

Collectives™ on Stack Overflow

MySQL Query Of Multiple Fields With One Variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related