[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>
<?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
?>
<?php echo form_open('welcome/takeaway_view'); ?>
<table cellspacing="0" id="results_table">
<tr>
<td><?php echo $takeaway->name;?><br /><span><?php echo $takeaway->address.', '.$takeaway->postcode;?></span></td>
<td><?php echo $type;?></td>
<td><?php echo $takeaway->delivery_first;?></td>
<td>
<input type="hidden" value="<?php echo $location?>" name="_postcode" />
<input type="hidden" value="<?php echo $takeaway->id;?>" name="_takeaway_id" />
<input type="hidden" value="<?php echo $distance;?>" name="_distance" />
<input type="submit" value="Go" />
</td>
</tr>
</table>
<?php echo form_close();?>
<?php
}