[eluser]JamesTaylor[/eluser]
I'm running the following code which is now very close to working as i want but in order to fix my last issue i have resorted to enabling the output profiler to see what is happening. I think i have found the cause of my issue but i have no idea what is causing it???
My Code is:
Controller
Code: <?php
class Search extends Controller {
function Search()
{
parent::Controller();
}
function index()
{
$data['MainContent'] = 'home_view';
$data['h1'] = 'The UK Golf Course Guide';
$data['h2'] = 'Welcome to The UK Golf Course Guide';
// Load our view
$this->load->view('template', $data);
}
function golf_club_search()
{
$this->output->enable_profiler(TRUE);
//Loads Form Validation Library
$this->load->library('form_validation');
//If search Form submitted
if ($_POST)
{
//Setting Validation Rules - in order of: Field Name - Error Message - Validation Rules
$this->form_validation->set_rules('postcode', 'Postcode', 'trim|required');
$this->form_validation->set_rules('lat', 'Latitude', 'trim|required');
$this->form_validation->set_rules('lng', 'Longitude', 'trim|required');
$this->form_validation->set_rules('Distance', 'Distance', 'trim|required|numeric');
//If Validation Fails
if($this->form_validation->run() == FALSE)
{
$this->check_search_details();
}
//Validation Passed
else
{
$SessionData = array(
'Lng' => $this->input->post('lng'),
'Lat' => $this->input->post('lat'),
'Distance' => $this->input->post('Distance'),
'postcode' => $this->input->post('postcode')
);
$this->session->set_userdata($SessionData);
$this->form_validation->run() == TRUE;
}
}
if ($this->session->userdata('Lng') && $this->session->userdata('Lat') && $this->session->userdata('Distance') && !$_POST || $this->form_validation->run() == TRUE)
{
//Loads Pagination
$this->load->library('pagination');
//Configuration of Pagination
$config['base_url'] = base_url().'index.php/search/golf_club_search';
$config['total_rows'] = $this->db->get('golfcourses')->num_rows();
$config['per_page'] = 1;
$config['num_links'] = 1;
$config['full_tag_open'] = '<div id="Pagination">';
$config['full_tag_close'] = '</div>';
//Initilizes Pagination
$this->pagination->initialize($config);
//set Lng / Lat /Dist values from session
$Lng = $this->session->userdata('Lng');
$Lat = $this->session->userdata('Lat');
$Dist = $this->session->userdata('Distance');
//load model
$this->load->model('golfcourses_model');
if($this->golfcourses_model->search_location($Lng, $Lat, $Dist, $config['per_page'], $this->uri->segment(3)) == FALSE)
{
$this->no_results();
}
else
{
// Populate an array with all the locations we want to put on our map.
$data['locations'] = $this->golfcourses_model->search_location($Lng, $Lat, $Dist, $config['per_page'], $this->uri->segment(3));
// Load our view
$data['MainContent'] = 'search_view';
$data['h1'] = 'The UK Golf Course Guide';
$data['h2'] = 'Golf Club Search Results';
$data['Lng'] = $this->session->userdata('Lng');
$data['Lat'] = $this->session->userdata('Lat');
$data['Distance'] = $this->session->userdata('Distance');
$data['postcode'] = $this->session->userdata('postcode');
$this->load->view('template', $data);
}
}
}
function no_results()
{
$data['MainContent'] = 'no_results_view';
$data['h1'] = 'The UK Golf Course Guide';
$data['h2'] = 'Sorry... No Results!';
// Load our view
$this->load->view('template', $data);
}
function check_search_details()
{
$data['MainContent'] = 'check_search_view';
$data['h1'] = 'The UK Golf Course Guide';
$data['h2'] = 'OOPs! Problem With Search';
// Load our view
$this->load->view('template', $data);
}
}
[eluser]JamesTaylor[/eluser]
continued...
Model:
Code: <?php
class Golfcourses_model extends Model {
function insert($data)
{
$this->db->insert('golfcourses', $data);
return;
}
function get_locations() {
// Get all the entries in the parties table.
$this->db->select('Name, Lat, Lng, Address1');
$query = $this->db->get('golfcourses');
// Are there any results from our query?
if($query->num_rows() > 0)
{
// Set a counter to 0
$n = 0;
// Create our array
$data = array();
// Loop through the results filling our array with the locations
foreach($query->result_array() as $row)
{
$data[$n]['Name'] = $row['Name'];
$data[$n]['Address1'] = $row['Address1'];
$data[$n]['Lat'] = $row['Lat'];
$data[$n]['Lng'] = $row['Lng'];
$n++;
}
// Return the array
return $data;
}
// Returns false if we didn't get any results from our query
return false;
}
function search_location($Lng, $Lat, $Dist, $LimitValue, $OffsetValue)
{
$this->db->select('ClubID, Name, Lat, Lng, Address1, Address2, Address3, County, PostCode, TelArea, TelNo, Web, Email, Review')
->select('"( 3959 * acos( cos( radians($Lat) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians($Lng) ) + sin( radians($Lat) ) * sin( radians( Lat ) ) ) )" AS distance');
$this->db->having('distance <', $Dist);
$this->db->order_by('distance');
$this->db->limit($LimitValue, $OffsetValue);
$query = $this->db->get('golfcourses');
// Are there any results from our query?
if($query->num_rows() > 0)
{
// Set a counter to 0
$n = 0;
// Create our array
$data = array();
// Loop through the results filling our array with the locations
foreach($query->result_array() as $row)
{
$data[$n]['ClubID'] = $row['ClubID'];
$data[$n]['Name'] = $row['Name'];
$data[$n]['Address1'] = $row['Address1'];
$data[$n]['Address2'] = $row['Address2'];
$data[$n]['Address3'] = $row['Address3'];
$data[$n]['County'] = $row['County'];
$data[$n]['PostCode'] = $row['PostCode'];
$data[$n]['TelArea'] = $row['TelArea'];
$data[$n]['TelNo'] = $row['TelNo'];
$data[$n]['Web'] = $row['Web'];
$data[$n]['Email'] = $row['Email'];
$data[$n]['Review'] = $row['Review'];
$data[$n]['Lat'] = $row['Lat'];
$data[$n]['Lng'] = $row['Lng'];
$n++;
}
// Return the array
return $data;
}
// Returns false if we didn't get any results from our query
return FALSE;
}
}
When using the 'golf_club_search' function iin the controller it calls the 'search_locations' function in the model which should cause 1 query to executed on the DB.
However i can see from the output profiler that 3 queries are being run which are:
Code: SELECT *
FROM (`golfcourses`)
SELECT `ClubID`, `Name`, `Lat`, `Lng`, `Address1`, `Address2`, `Address3`, `County`, `PostCode`, `TelArea`, `TelNo`, `Web`, `Email`, `Review`, "( 3959 * acos( cos( radians($Lat) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians($Lng) ) + sin( radians($Lat) ) * sin( radians( Lat ) ) ) )" AS distance
FROM (`golfcourses`)
HAVING `distance` < 15
ORDER BY `distance`
LIMIT 1
SELECT `ClubID`, `Name`, `Lat`, `Lng`, `Address1`, `Address2`, `Address3`, `County`, `PostCode`, `TelArea`, `TelNo`, `Web`, `Email`, `Review`, "( 3959 * acos( cos( radians($Lat) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians($Lng) ) + sin( radians($Lat) ) * sin( radians( Lat ) ) ) )" AS distance
FROM (`golfcourses`)
HAVING `distance` < 15
ORDER BY `distance`
LIMIT 1
I have no idea why the SELECT * is running and the other query should only run once, why's it duplicated?
I'm sure the problem comes from my coding but i just don't understand why at the minute! Anyone care to enlighten me?... i have a feeling i might just be about to learn an eye opening lesson here!
Cheers
James
[eluser]n0xie[/eluser]
query 1
Quote: $config['total_rows'] = $this->db->get('golfcourses')->num_rows();
query 2
Quote:if($this->golfcourses_model->search_location($Lng, $Lat, $Dist, $config['per_page'], $this->uri->segment(3)) == FALSE)
query 3
Quote: $data['locations'] = $this->golfcourses_model->search_location($Lng, $Lat, $Dist, $config['per_page'], $this->uri->segment(3));
[eluser]JamesTaylor[/eluser]
Well that makes me look stupid!!
Much appreciated tho n0xie!
Its thrown me because the 2nd query Quote:if($this->golfcourses_model->search_location($Lng, $Lat, $Dist, $config[‘per_page’], $this->uri->segment(3)) == FALSE)
isn't returning false and kicking the sequence out to the 'no_results function'when i know it absolutely should be?
When i saw the output profiler with the select * running 1st i thought that as it was selecting all before hand my query wouldn't return False... heads in a spin and missed the obvious!
so the next question is then: Why isn't the 2nd query returning false, and therefore stopping the 3rd query from running and insighting the no_results function?
[eluser]n0xie[/eluser]
I would think that your method doesn't return FALSE. Maybe output what it does return to see what's going on.
[eluser]JamesTaylor[/eluser]
Sorry, this is where my learning to date starts to fall down...
is there an easy way to test what is being output? a de-bugger similar to the output profiler?
[eluser]n0xie[/eluser]
You could install a debugger like xdebug which gives you the ability to insert break points. (there are some how-to's floating around these forums).
But the more usual debugging process is this ;-)
Code: ## <snip>
//load model
$this->load->model('golfcourses_model');
$test = $this->golfcourses_model->search_location($Lng, $Lat, $Dist, $config['per_page'], $this->uri->segment(3));
var_dump($test);
exit();
if($this->golfcourses_model->search_location($Lng, $Lat, $Dist, $config['per_page'], $this->uri->segment(3)) == FALSE)
{
$this->no_results();
}
## <snip>
[eluser]JamesTaylor[/eluser]
I think i have solved the problem... seems to be the Active Record code:
If i use traditional SQL as below:
Code: $sql = "SELECT ClubID, Name, Lat, Lng, Address1, Address2, Address3, County, PostCode, TelArea, TelNo, Web, Email, Review, ( 3959 * acos( cos( radians($Lat) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians($Lng) ) + sin( radians($Lat) ) * sin( radians( Lat ) ) ) ) AS distance FROM golfcourses HAVING distance < $Dist ORDER BY distance LIMIT" ." $LimitValue" ."$OffsetValue";
$query = $this->db->query($sql);
it works and the output profiler shows
Quote:SELECT ClubID, Name, Lat, Lng, Address1, Address2, Address3, County, PostCode, TelArea, TelNo, Web, Email, Review, ( 3959 * acos( cos( radians(53.850465) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(-2.004176) ) + sin( radians(53.850465) ) * sin( radians( Lat ) ) ) ) AS distance FROM golfcourses HAVING distance < 13 ORDER BY distance LIMIT 1
notice the values which are being inserted into the query via variables are present.
When i run the query as Active Records with the code
Code: $this->db->select('ClubID, Name, Lat, Lng, Address1, Address2, Address3, County, PostCode, TelArea, TelNo, Web, Email, Review')
->select('"( 3959 * acos( cos( radians($Lat) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians($Lng) ) + sin( radians($Lat) ) * sin( radians( Lat ) ) ) )" AS distance');
$this->db->having('distance <', $Dist);
$this->db->order_by('distance');
$this->db->limit($LimitValue, $OffsetValue);
$query = $this->db->get('golfcourses');
the output profiler shows
Quote:SELECT `ClubID`, `Name`, `Lat`, `Lng`, `Address1`, `Address2`, `Address3`, `County`, `PostCode`, `TelArea`, `TelNo`, `Web`, `Email`, `Review`, "( 3959 * acos( cos( radians($Lat) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians($Lng) ) + sin( radians($Lat) ) * sin( radians( Lat ) ) ) )" AS distance
FROM (`golfcourses`)
HAVING `distance` < 13
ORDER BY `distance`
LIMIT 1, 1
notice that the values which are meant to be inserted into the query are just being left as the variable name - the whole section of the query is just being treated as a string value i think
I have now updated (or played around with and stumbled upon it) the ->select line to be
Code: ->select("( 3959 * acos( cos( radians($Lat) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians($Lng) ) + sin( radians($Lat) ) * sin( radians( Lat ) ) ) ) AS distance");
this generates the sql query with the values etc. instead of interpreting it as a string!
The large problem was caused by the minor matter of getting the correct use of "" and '' in the structure!
Thanks for the pointer on the usual debugging method, i'm sure that will be used plenty of times in the future and help speed up my problem solving ability!
Many thanks for the responses too, always helpful to be able to the throw the problem out there and pick up tips along the way... i always feel like a dunse having to ask basic questions like how to generate the output but its part of the learning curve i guess!
[eluser]n0xie[/eluser]
No problem. A thing to remember is that although Active Record is very helpful, it's not the holy grail. For complex queries (like these) it's usually easier to just do the SQL yourself.
[eluser]CroNiX[/eluser]
For select statements, you can also set the 2nd parameter to TRUE and it won't escape them.
$this->db->select('some complex select', TRUE);
|
