Welcome Guest, Not a member yet? Register   Sign In
order the results of this query by distance help
#1

[eluser]Andy78[/eluser]
I am using a library that returns a list of postcodes ordered by distance ascending so the closest to the entered postcode comes up first which is what I want but I am also using this array of postcodes to perform a where_in query so that I can pull out the nearest 4 takeaways of each type to the supplied postcode.

I assumed that the results from the query would be returned in the same order as the array of postcodes but this is not the case.

How do I change this query in my model to only return the single nearest takeaway of each takeaway type?

Model:

Quote:<?PHP

//Using standard CI model no datamapper involved
class Search_results extends CI_Model {

function __construct()
{
parent::__construct();
}

//Returns the closest individual takeaway of each type to the entered postcode.
function get_takeaways_inrange($returned_postcodes){

foreach($returned_postcodes as $key => $value){

$valid_postcodes[] = "'$key'";
}

$valid_postcode_list = implode(", ", $valid_postcodes);


//Get takeaway types

$types = $this->db->query("SELECT * FROM takeaway_types");

foreach($types->result() as $row){

$rest_type[$row->id] = $row->type_name;

// $rest_type[1] = "Indian";
// $rest_type[2] = "Chinese";
// $rest_type[3] = "Italian (Pizza)";
// $rest_type[4] = "Turkish (Kebab)";

}


for ($x=1; $x<=4; $x++) {

$results = $this->db->query("SELECT * FROM takeaways WHERE postcode IN ({$valid_postcode_list}) AND takeaway_type_id= '$x' LIMIT 0,1;");

foreach($results->result() as $row){

$data[] = $row;


}

}

return $data;
}

}


Controller:

Quote:function search(){

//Load standard ci model
$this->load->model('Search_results');

//get the user's postcode
$postcode = $this->input->post('postcode');
//make sure post upper case and has no spaces
$location = strip_tags($postcode);
$location = strtoupper($postcode);
$location = str_replace(" ", "", $postcode);
$d = 10;//range within a radius of

//get all postcodes in a radius of a postcode-location sorted by distance
$returned_postcodes = $this->geozip->get_zips_in_range($location, 10, SORT_BY_DISTANCE_ASC, TRUE);

//get the closest takeaway of each type found witbin the list of postcodes
$takeaways = $this->Search_results->get_takeaways_inrange($returned_postcodes);

//Get footer menu items
$footer_menu = new Menu_item();
$footer_menu->where('menu_id', 2);
$footer_menu->where('published', 1);//make sure item is published
$footer_menu->order_by('order', 'asc')->get();

$content_data['footer_menu'] = $footer_menu;
$data['post'] = $returned_postcodes;
$data['footer_m'] = $this->load->view('footer_menu', $content_data, TRUE); //pull in footer menu view
$data['location'] = $location;
$data['takeaways'] = $takeaways;

// $this->load->view('results', $data);
}


Code:
<div>
    <h3>Takeaways near you:</h3>
</div>
&lt;?php
foreach($takeaways as $takeaway){
    
    
        if($takeaway->takeaway_type_id == 1){$type = 'Indian';}
        if($takeaway->takeaway_type_id == 2){$type = 'Chinese';}
        if($takeaway->takeaway_type_id == 3){$type = 'Pizza';}
        if($takeaway->takeaway_type_id == 4){$type = 'Kebab';}
                
            
            $distance = $this->geozip->get_distance($location, $takeaway->postcode);//get the distance between the two postcodes
                  
                 ?&gt;
                 &lt;?php echo form_open('welcome/takeaway_view'); ?&gt;
                   <table cellspacing="0" id="results_table">
                       <tr>
                        <td>&lt;?php echo $takeaway->name;?&gt;<br /><span>&lt;?php echo $takeaway->address.', '.$takeaway->postcode;?&gt;</span></td>
                        <td>&lt;?php echo $type;?&gt;</td>
                        <td>&lt;?php echo $takeaway->delivery_first;?&gt;</td>
                        <td>
                            &lt;input type="hidden" value="&lt;?php echo $location?&gt;" name="_postcode" /&gt;
                            &lt;input type="hidden" value="&lt;?php echo $takeaway-&gt;id;?&gt;" name="_takeaway_id" /&gt;
                            &lt;input type="hidden" value="&lt;?php  echo $distance;?&gt;" name="_distance" /&gt;
                            &lt;input type="submit" value="Go" /&gt;
                        </td>
                       </tr>
                   </table>
                   &lt;?php echo form_close();?&gt;  
                              
&lt;?php  
        
}




Theme © iAndrew 2016 - Forum software by © MyBB