Searching by Category and Zip code

[eluser]derekmichaeljohnson[/eluser]
I have a search form with 2 fields: category (dropdown) and zip code. If they click "Find" without entering anything, they get all "users" currently in DB. If they select a category, they get all "user" records assigned to that category. Pretty simple stuff. Here's the code so far:

Code:

// Join users and cats
$this->db->select('users.*, users_cats.cat_id, cats.name AS cat');
$this->db->from('users');
$this->db->join('users_cats', 'users.id = users_cats.user_id','left');
$this->db->join('cats', 'users_cats.cat_id = cats.id','left');

// If they've selected a cat, include WHERE IN clause
$cat = $this->input->post('cat');
if($cat) { $this->db->where_in('users_cats.cat_id',$cat); }

$query = $this->db->get();

And now the part where I'm stuck. I'm using a Zip Code class which returns an array containing zip codes within 20 miles (default) of the entered zip code. Example result array:

Code:

array
(
  '34231' => '0 miles', // this is the zip code they entered
  '34233' => '1.5 miles',
  '34236' => '2.8 miles'
  // etc.
)

So, if they enter a zip, what's the best way to add an ARRAY of zip codes to a WHERE IN? And if they enter BOTH a zip code and category, I want to make sure they ONLY get results that match the selected category AND the zip codes in the returned array.

I tried this (see below), but it gives me results that EITHER match the selected cat OR the zip codes in the array... which is no good...

Code:

$zip = $this->input->post('zip');

if($zip)
{
    $zips = $this->zipcode->get_zips_in_range($zip); // returns array of zip codes like above example
    foreach($zips as $zip => $distance)
    {
        $this->db->or_where_in('zip',$zip);
    }
}

[eluser]Jbeasley6651[/eluser]
don't use or where in, use where in again...

[eluser]derekmichaeljohnson[/eluser]
[quote author="Jbeasley6651" date="1247905504"]don't use or where in, use where in again...[/quote]
But if I use WHERE IN in the foreach loop, I'll end up with:

Code:

$this->db->where_in('cat','2');
$this->db->where_in('zip','34231');
$this->db->where_in('zip','34233');
$this->db->where_in('zip','34236');
// etc.

Something tells me that won't work. What I need the query to look like is:

Code:

$this->db->where_in('cat','2');
    // AND
$this->db->where_in('zip','34231');
    // OR
$this->db->where_in('zip','34233');
    // OR
$this->db->where_in('zip','34236');
    // etc.

In other words, find all user records with a category of "2" that reside in any of the zip codes in the array.

[eluser]derekmichaeljohnson[/eluser]
No one has any input?

[eluser]Nicholai[/eluser]
Couldn't you do something like this? Lose the foreach loop and use a where_in statement with the zip code field of your array. My syntax may be off but the logic feels correct. I still have trouble with the right syntax to pull one field out of an array.

Code:

$this->db->select('users.*, users_cats.cat_id, cats.name AS cat');
$this->db->from('users');
$this->db->join('users_cats', 'users.id = users_cats.user_id','left');
$this->db->join('cats', 'users_cats.cat_id = cats.id','left');

$cat = $this->input->post('cat');
if($cat) { $this->db->where_in('users_cats.cat_id',$cat); }

if($zip)
{
    $zips = $this->zipcode->get_zips_in_range($zip);
    $this->db->where_in('zip',$zips['zip_field']);    
}

[eluser]derekmichaeljohnson[/eluser]
[quote author="narayanis" date="1248049101"]

Code:

if($zip)
{
    $zips = $this->zipcode->get_zips_in_range($zip);
    $this->db->where_in('zip',$zips['zip_field']);    
}

[/quote]
Won't calling $zips['zip_field'] return just ONE of the zip codes?

Also, the problem is that the returned zip codes are the KEYS of the array, not the VALUES.

[eluser]Nicholai[/eluser]
No, it would return all of the zip codes, separated by commas. The user guide shows here what happens when you send an array into a where_in():

Code:

$names = array('Frank', 'Todd', 'James');
$this->db->where_in('username', $names);
// Produces: WHERE username IN ('Frank', 'Todd', 'James')

If the ZIPs are keys instead of values, use the PHP function array_keys():

Code:

if($zip)
{
    $zips = $this->zipcode->get_zips_in_range($zip);
    $keys = array_keys($zips);
    $this->db->where_in('zip',$keys);    
}