Array and a select limit

MitchEvans

I have an array within a select statement.
My results appear fine without a limit but as soon as I set a limit the results don't come up in the same order.

if (isset($_GET['pageno'])) {
 $pageno = $_GET['pageno'];
} else {
 $pageno = 1;
}
$rows_per_page = 15;
$lastpage      = ceil($totalcount1/$rows_per_page);
$pageno = (int)$pageno;
if ($pageno == 1) {
 $pageno = 1;
} 
elseif ($pageno > $lastpage) {
   $pageno = $lastpage;
}

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

$sorter = "SELECT * FROM directory WHERE authorised = 'Y' THE LIMIT WILL APPEAR HERE";
$sorterResults = mysql_query($sorter);
while ($sorterRow = mysql_fetch_array ($sorterResults)){
$sortid = $sorterRow["id"];
$distancetocleanersort = calc_postcode_seperation("$dir1","$checksortpost");
$sortarray[$sortid]=$distancetocleanersort;
}
asort($sortarray);
foreach ($sortarray as $key => $val) {	

//Show results for users that have been authorised by administrator and their clean types have been checked by the user
$directory = "SELECT * FROM directory WHERE id IN ('$key') 

!!!!!Results echoed here!!!!!

Like I sday without a limit the results appear fine, but when I LIMIT the results in the first select statement they don't appear in the same order

Any help cheers!?!?!?!

😕

bogu

You should ORDER your select so if will 'appear in the same order'!

zingbats

Also, indenting your code is fun and beneficial!

MitchEvans

The data is already ordered using asort, i shouldn't need to order the data in the select statement.

One array I use around the SELECT statement brings up the results in a strange order but still low to high just jumbled up between the pages and the other array i try just brings all the results up on one page.

NogDog

You are doing all of your sorting of the data after the query is performed. So when you add a limit to the query, you are then sorting some arbitrary subset of the data. To simplify it for illustration, suppose your query without a limit returned these values in this order: 5, 1, 9, 2, 10, 4, 7, 3, 8, 6. If you sort the whole set numerically, you get 1, 2, 3,...10. But if your query retrieves the data in two chunks of 5 based on a limit clause, each set then being sorted independently of the other, the two result sets would be 1, 2, 5, 9, 10 (the first 5 query values sorted) and 3, 4, 6, 7 8 (the last 5 query values sorted).

So your challenge is to either build all of the sorting logic you need into an ORDER BY clause of the query before the LIMIT clause (preferred solution), or else do the query with no limiting, do your sorting, and then grab a chunk of the sorted array of data based on your pagination offset/number-of-records requirements

MitchEvans

Wow and how could I build up my sorting logic into an ORDER BY.

Appreciate the help so much, would be so chuffed to get this working!