2

Well guys i have this query 

$mysql = "select * from xxx where active = 1 order by Rand() limit $start,12";
mysql_query($mysql);

Everything works great so far.

I want: when i am pressing the next button (page 2 or three etc) to see the next 12 random records but do not display the first 12 random records that i had in my previus page!

Thank you all! p.s Sorry guys for my bad english!

Sirko
  • 72,589
  • 19
  • 149
  • 183
George L.
  • 65
  • 2
  • 13
  • 1
    `ORDER BY RAND()` [should be avoided like the plague](http://stackoverflow.com/questions/6592751/why-dont-use-mysql-order-by-rand) unless your table has 100 rows or some such number. – Jon Sep 20 '12 at 12:36
  • It looks like the probability of randomly selecting the same record twice in a row is too high. You may just need more records, if possible. Otherwise, you would need some (rather clumsy?) mechanism of tracking the last selected portion of records for each session, etc. If you really need something like that, you might consider splitting the range of records logically into segments (of >= 12 records), keep track of the recent segment in the session, and query each time from a different segment using the limit clause in your select. – full.stack.ex Sep 20 '12 at 18:32

5 Answers5

1

Just try to retrieve the data you need in an array, randomize it with shuffle() in PHP, and paginate the result with some JQuery, it will be awesome, just one query and no refresh. ;)

Fry_95
  • 241
  • 1
  • 6
1

You can keep track of the previously shown records' ids and put them in an array.

In your query use id NOT IN (array)

Asciiom
  • 9,867
  • 7
  • 38
  • 57
Jurgo
  • 907
  • 4
  • 18
  • Whilst this method would work, it is slightly impractical as it requires two different queries, @Fry_95's answer is a lot more practical – Ben Carey Sep 20 '12 at 12:47
  • You will still have to store the id's somewhere! – Ben Carey Sep 20 '12 at 12:55
  • My point is that you don't have to get al those records at once. – Jurgo Sep 20 '12 at 12:57
  • Why execute multiple queries when you can retrieve all you data with just one query? This is not necessarily always best, but most of the time it is! To be honest, neither of the posted answers are particularly nice fixes, but it is a task that is not likely to have a nice fix so neither solution is going to be pretty. I am still thinking of a better solution to both of these, dont think I will succeed – Ben Carey Sep 20 '12 at 13:05
  • I found out the way to collect all used id's from first page and store them into an session like 12,34,45,54,34. Now is this correct for my results: $ids = $_SESSION['ALLIDS']; $sqln = "select * from coupons where active = 1 and id not in ($ids) order by Rand() limit $start,8"; – George L. Sep 20 '12 at 19:21
1

You need to keep one array (e.g $arrRecordIds) to track all the id's of records shown on previous pages.

When you are on first page:

$arrRecordIds=array(); // Empty array

When you are on second page:

$arrRecordIds=array_merge($arrRecordIds, $arrNewRecordIds);array_unique( $arrRecordIds );

If your select query simply concat- where id NOT IN ( implode(',', $arrRecordIds ) )

Here $arrNewRecordIds should contains id's of the records on the page.

Ben Carey
  • 16,540
  • 19
  • 87
  • 169
Ajay Khedkar
  • 73
  • 4
0

Apply the concept of Systematic Random Sampling,

  • Number the records N, decide on the n (pagination size, eg: 10, 20)
  • (sample size) that you want or need k = N/n = the interval size
  • Randomly select an integer between 1 to k then take every k th unit

Refer: http://www.socialresearchmethods.net/kb/sampprob.php

Try using the following script in your showdata.php file

$per_page = 12; 
$sqlc = "show columns from coupons"; 
$rsdc = mysql_query($sqlc); 
$cols = mysql_num_rows($rsdc); 
$page = $_REQUEST['page']; 
$start = ($page-1)*12; 

$N = 1000; //Total rows in your table (query to get it dynamically)
$n = $per_page;
$k = ceil($N/$n);

$range[] = $page;

for($i=1;$i<$n;$i++) {
 $range[] = ($page+$k)*$i;
}

$sqln = "SELECT * FROM ( SELECT @rownum:= @rownum+1 AS rindex, n.* FROM xxx n, (SELECT @rownum := 0) r ) AS rows WHERE rindex IN (".implode(',',$range).")";
$rsd = mysql_query($sqln);
Suriyamoorthy Baskaran
  • 635
  • 7
  • 17
  • Guys i am already use jquery for pegination, here is how i retrieve results: Code from index.php: $("#coupondiv").load("showdata.php?page=1", hideLoader); And showdata.php file: $per_page = 12; $sqlc = "show columns from coupons"; $rsdc = mysql_query($sqlc); $cols = mysql_num_rows($rsdc); $page = $_REQUEST['page']; $start = ($page-1)*12; $sqln = "select * from xxx where active = 1 order by Rand() limit $start,12"; $rsd = mysql_query($sqln); Now please can some one tell me excactly how can i solve my problem? – George L. Sep 20 '12 at 16:38
  • @GeorgeL. Try using the script i have posted in your showdata.php file. I am bit worried about the query performance. – Suriyamoorthy Baskaran Sep 21 '12 at 07:21
0

SOLUTION - that works a treat.

  1. do a select random search of all required records
  2. generate a random user-id eg. "smith".rand(1000,10000)
  3. form a string of all random keys upto required no of records per page
  4. insert above in a table/field containing a corresponding page no.
  5. repeat/loop above until no more pages/recs remaining - use array_splice(str,from,to) - then use $notscreen = print_r($splice, true) for string storage to table -> randompages:

tb.rec-no | user-id | pageno | string ( with keys of recs upto recs/page)

122 | aj7894 | p1 | [0]=>100[1]=>400[2]=>056[3]=>129

123 | aj7894 | p2 | [x]99=>[x]240=>[x]7895[x]458=>320

... upto whole array of pages /no of records / all pages - no duplication of data - only 1-column of key of recs stored in random as retrieved

  1. use user-id & pageno with WHERE to pull out random keys for that individual user & page

  2. convert string back to array and pull out matching key recs for specific pages using the array in a SELECT WHERE query with implode

  3. re-circ [ user-id & pageno ] using $_GET/POST for duration of search/view - reinitialise when new view or new search commences

notes:

-better to use list for search - but requires more work to format string - should give random page results as originally stored

  • problem with array matching is it orders records per page; lowest being first - not so random for the page display

  • temp table no good - because cannot be accessed when script is thrown back to server for 2nd and more time - it's lost from memory by mysql

  • php rules - no flimsy cookies or java-script !

BIG PROBLEM - SOLVED.

re-compsense for help received from your posts / answers.

Happy Days!

Ali
  • 1