PHP pagination, limit per page, function in mysql

Question

I have big mysql database and I want to reject all rows that are not within range of wanted radius.

$results_per_page = 10;
$city = "London";
$radius = 20;

mysql query:

if($city != "") {
    $sql="SELECT * FROM city_items ORDER BY id ASC LIMIT " . $page_first_result . "," .  $results_per_page;
    find_all_in_radius($con, $sql, $city, $radius);
}

if $city and $radius are not set, everything is fine

else {
    $sql="SELECT * FROM city_items ORDER BY id ASC LIMIT " . $page_first_result . "," .  $results_per_page;
    find_all($con, $sql);
}

I tried with something like that:

function find_all_in_radius($con, $sql, $city, $radius) {
    $result1 = mysqli_query($con, $sql);  
    #geo_code find coordinates of provided city
    $long_lat = geo_code($city);
    echo '<div id="container">';
    $good_rows = array();

    while($row = mysqli_fetch_array($result1)) 
    { 
       $latitude = doubleval($long_lat[0]);
       $longitude = doubleval($long_lat[1]);
       $latit = doubleval($row[106]);
       $longtit = doubleval($row[107]);
       #distance() is function counting range between two coordinates
       if(!empty($latit) and !empty($longtit) and distance($latit, $longtit, $latitude, $longitude, "M") < $radius) 
        {
        array_push($good_rows, $row);
        }
    }
foreach($good_rows as $row) {
    echo $row[1];
    echo $row[2];
    }
}

So the problem is, if $city and $radius are empty, I get 100 items on 10 pages. If $city and $radius are set, I get 10 items, but one is one 2nd page, two are on 4th page. Some rows are blank. Without:

"ASC LIMIT " . $page_first_result . "," .  $results_per_page;"

in mysql_query, everything is fine, but I get all results on first page, without any pagination working.

EDIT: I'm looking for some magic trick which will allow me to run php function distance() in mysql query... or to bypass rows that not qualify, in find_all_in_radius() function, without destroying my pagination :D

What you are doing is getting all the results sorted by page and then filtering them based on city and radius. So any results that are left are still left in their original page. You need to first filter and then arrange them in pages. — clinomaniac, Nov 13 '18 at 23:40

score 1 · Answer 1 · answered Nov 14 '18 at 02:44

There is no magic trick. Either you

filter and paginate in MySQL, or
fetch all data and filter and paginate in PHP.

In 1st approach you have to implement your distance function in MySQL. That may be easier than you think. If you're using the standard great-circle distance, you can copy the code from MySQL Great Circle Distance (Haversine formula)

If you go with the 2nd approach, you have to fetch all rows keep a count of the matching rows.

In either case you'll run into performance problems if you truly have a big database (millions of rows).

I resolved it using array_slice() yesterday, thanks anyway – blabla Nov 14 '18 at 18:25 — blabla, Nov 14 '18 at 18:25

PHP pagination, limit per page, function in mysql

if $city and $radius are not set, everything is fine

I tried with something like that:

1 Answers1