Help with a SQL query..

[eluser]Zack Kitzmiller[/eluser]
Here what I need to do. Select some information for a location of a store, where certain options are true.

For example, all locations within 5 miles of 61108 where UPS shipping is offered. I have a geocoded Zip Code Library, so i've got an array of locations close to the zipcode I need.

And here in the code in my model:

Code:

function advanced_search($zip_codes, $options) {
    $data = array();
    
    $this->db->select('*')->from('db_stores');
    $this->db->like('store_zip', $this->input->post('zip_code'), 'after');
    
    if (count($options)) {
      foreach ($options as $key => $option) {
        $this->db->where($key, '1');
      }
    }

    if ($zip_codes != NULL) {
      foreach ($zip_codes as $zip_code => $mileage) {
        $this->db->where('store_zip', $zip_code);
      }
    }
    
    $Q = $this->db->get();
    if ($Q->num_rows > 0) {
      foreach($Q->result_array() as $row) {
        $data[] = $row;
      }
    }
    
    $Q->free_result();
    return $data;
  
  }

The loops are running as expected, by results are not as expected. any SQL ninja can help?

[eluser]Zack Kitzmiller[/eluser]

Code:

SELECT *
FROM (`db_stores`)
WHERE `UPS` = '1'
AND  `store_zip`  LIKE '90266%'
OR  `store_zip`  LIKE '�266%'
OR  `store_zip`  LIKE '�267%'
OR  `store_zip`  LIKE '�254%'
OR  `store_zip`  LIKE '�278%'
OR  `store_zip`  LIKE '�245%'
OR  `store_zip`  LIKE '�261%'
OR  `store_zip`  LIKE '�260%'
OR  `store_zip`  LIKE '�251%'
OR  `store_zip`  LIKE '�250%'
OR  `store_zip`  LIKE '�506%'
OR  `store_zip`  LIKE '�503%'
OR  `store_zip`  LIKE '�304%'
OR  `store_zip`  LIKE '�009%'
OR  `store_zip`  LIKE '�277%'
OR  `store_zip`  LIKE '�504%'
OR  `store_zip`  LIKE '�083%'
OR  `store_zip`  LIKE '�080%'
OR  `store_zip`  LIKE '�249%'
OR  `store_zip`  LIKE '�045%'
OR  `store_zip`  LIKE '�307%'

that is the output I'm getting, is there anyway to add parenthesis to what active record is generating to I can fix this, or am I gonna have to write the sql by hand?

the expected output would be:

Code:

SELECT *
FROM (`db_stores`)
WHERE `UPS` = '1'
AND  (`store_zip`  LIKE '90266%'
OR  `store_zip`  LIKE '�266%'
OR  `store_zip`  LIKE '�267%'
OR  `store_zip`  LIKE '�254%'
OR  `store_zip`  LIKE '�278%'
OR  `store_zip`  LIKE '�245%'
OR  `store_zip`  LIKE '�261%'
OR  `store_zip`  LIKE '�260%'
OR  `store_zip`  LIKE '�251%'
OR  `store_zip`  LIKE '�250%'
OR  `store_zip`  LIKE '�506%'
OR  `store_zip`  LIKE '�503%'
OR  `store_zip`  LIKE '�304%'
OR  `store_zip`  LIKE '�009%'
OR  `store_zip`  LIKE '�277%'
OR  `store_zip`  LIKE '�504%'
OR  `store_zip`  LIKE '�083%'
OR  `store_zip`  LIKE '�080%'
OR  `store_zip`  LIKE '�249%'
OR  `store_zip`  LIKE '�045%'
OR  `store_zip`  LIKE '�307%')
[/code]

[eluser]Hakkam[/eluser]
How about if you print out the $this->db->last_query() ??

[quote author="techneke" date="1248121017"]Here what I need to do. Select some information for a location of a store, where certain options are true.

For example, all locations within 5 miles of 61108 where UPS shipping is offered. I have a geocoded Zip Code Library, so i've got an array of locations close to the zipcode I need.

And here in the code in my model:

Code:

function advanced_search($zip_codes, $options) {
    $data = array();
    
    $this->db->select('*')->from('db_stores');
    $this->db->like('store_zip', $this->input->post('zip_code'), 'after');
    
    if (count($options)) {
      foreach ($options as $key => $option) {
        $this->db->where($key, '1');
      }
    }

    if ($zip_codes != NULL) {
      foreach ($zip_codes as $zip_code => $mileage) {
        $this->db->where('store_zip', $zip_code);
      }
    }
    
    $Q = $this->db->get();
    if ($Q->num_rows > 0) {
      foreach($Q->result_array() as $row) {
        $data[] = $row;
      }
    }
    
    $Q->free_result();
    return $data;
  
  }

The loops are running as expected, by results are not as expected. any SQL ninja can help?[/quote]

[eluser]Zack Kitzmiller[/eluser]
Look Above...

[eluser]Hakkam[/eluser]
How should the result ? Please write, the expected result and the query result.

[eluser]Zack Kitzmiller[/eluser]
[quote author="Hakkam" date="1248126127"]How should the result ? Please write, the expected result and the query result.[/quote]

Please look above. The request information was posted a while ago.

Thank You.

[eluser]slowgary[/eluser]
All those OR clauses are VERY VERY inefficient. I hope you at least have an index on the store_zip column. I don't think active record will add that for you. You'll probably need to generate the query manually.

[eluser]slowgary[/eluser]
You can generate the query manually, but still using CodeIgniter. Do this like so:

Code:

$this->db->query("
SELECT *
FROM (`db_stores`)
WHERE `UPS` = '1'
AND  (`store_zip`  LIKE '90266%'
OR  `store_zip`  LIKE '�266%'
OR  `store_zip`  LIKE '�267%'
OR  `store_zip`  LIKE '�254%'
OR  `store_zip`  LIKE '�278%'
OR  `store_zip`  LIKE '�245%'
OR  `store_zip`  LIKE '�261%'
OR  `store_zip`  LIKE '�260%'
OR  `store_zip`  LIKE '�251%'
OR  `store_zip`  LIKE '�250%'
OR  `store_zip`  LIKE '�506%'
OR  `store_zip`  LIKE '�503%'
OR  `store_zip`  LIKE '�304%'
OR  `store_zip`  LIKE '�009%'
OR  `store_zip`  LIKE '�277%'
OR  `store_zip`  LIKE '�504%'
OR  `store_zip`  LIKE '�083%'
OR  `store_zip`  LIKE '�080%'
OR  `store_zip`  LIKE '�249%'
OR  `store_zip`  LIKE '�045%'
OR  `store_zip`  LIKE '�307%')"
)->result();

How do you determine which numbers to put into your OR clauses?

[eluser]Zack Kitzmiller[/eluser]
I have a query that runs from a zipcode DB, provides closest locations.

[eluser]slowgary[/eluser]
If you're trying to get stores within a proximity, couldn't you just do it like this:

Code:

SELECT * FROM `db_stores` WHERE `UPS` = '1' AND `store_zip` LIKE '90%';

Wouldn't that be more like what you're looking for? Otherwise, some of these OR clauses are going to be looking for results that are in a completely different state. The last 3 digits are likely duplicated and much less important to the general proximity than the first 2 digits.