MySQL – Select From Certain Record To The Last Record


To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 96, 18446744073709551615;

 

18446744073709551615 is 2^64-1 for those who were wondering. You may want to watch out because you won’t be able to store this value in an 32 bit integer. You have to make sure you store this as a string to ensure compatibility

18446744073709551615 is the maximum value of unsigned BIGINT

 

source:

http://stackoverflow.com/questions/255517/mysql-offset-infinite-rows

Advertisements

Google Maps – Find Nearby Location


To find locations in markers table that are within a certain radius distance of a given latitude/longitude, you can use a SELECT statement based on the Haversine formula. The Haversine formula is used generally for computing great-circle distances between two pairs of coordinates on a sphere. An in-depth mathemetical explanation is given by Wikipedia and a good discussion of the formula as it relates to programming is on Movable Type’s site.Here’s the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) – radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

 

 

source:

[CakePHP] Disable Model Queries Caching


Again a simple tip. CakePHP by default, in order to optimize performance, has a query caching system. This is a very cool feature but sometimes it may be self-defeating. For example when you do queries for Unit Testing.

In order to disable query caching, it’s enough to set the model cacheQueries variable to false. Setting it to true will re-enable queries caching.

You can disable it for the whole model “life-time” setting it during “design time”

<?php
class Project extends AppModel {
var $name = "Project";
var $customValidate = array("name");
var $cacheQueries = false;
...
?>

or you can temporarily disable it by calling the $this->Model->cacheQueries=false (use your model instead of “Model”)

Source:

http://edivad.wordpress.com/2008/04/15/cakephp-disable-model-queries-caching/