sammy dot
asked on
Avoid duplicate value in PHP MySQL pagination with rand()
I have a pagination that load more when I click on the load more button, which I got from the below URL http://makitweb.com/load-more-results-with-jqueryajax-and-php/
Whenever I add RAND() it always duplicates the value.
index.php
getData.php
Whenever I add RAND() it always duplicates the value.
index.php
<?php
$rowperpage = 10;
$allcount_query = "SELECT count(*) as allcount FROM users order by id";
$allcount_result = mysql_query($allcount_query);
$allcount_fetch = mysql_fetch_array($allcount_result);
$allcount = $allcount_fetch['allcount'];
$query = "select * from users order by RAND(" . date("Ymd") . ") asc limit 0,$rowperpage ";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
?>
<div><h1><?php echo $row['id']; ?></div>
<?php
}
?>
<h1 class="load-more">Load More</h1>
<input type="hidden" id="row" value="0">
<input type="hidden" id="all" value="<?php echo $allcount; ?>">getData.php
<?php
$row = $_POST['row'];
$rowperpage = 10;
$query = 'SELECT * FROM users ORDER BY id limit '.$row.','.$rowperpage;
$result = mysql_query($query);
$html = '';
while($row = mysql_fetch_array($result)){
$html .= '<div><h1>'.$id.'</h1></div>';
}
?>
Why do you want to "order-by-rand"! Do you mean to display random users? Well in that case you may see the random-duplication :)
Anyways... so here's few things to consider:
SELECT count(*) as allcount FROM users order by id;
You should drop that ORDER-BY clause.
Also using LIMIT and RAND orders may trouble you for larger tables. Try using references!
I meant something like this:
$last_id=0;
...
SELECT * from user where id>$last_id limit $rowperpage;
...
<h1 class="load-more" PARAMETER="LAST_ID">Load More</h1>
There are few old blogs for your reference but concept holds:
OLD: https://stackoverflow.com/questions/10459328/how-to-optimize-mysql-order-by-huge-limit-offsets
Really OLD: https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
Anyways... so here's few things to consider:
SELECT count(*) as allcount FROM users order by id;
You should drop that ORDER-BY clause.
Also using LIMIT and RAND orders may trouble you for larger tables. Try using references!
I meant something like this:
$last_id=0;
...
SELECT * from user where id>$last_id limit $rowperpage;
...
<h1 class="load-more" PARAMETER="LAST_ID">Load More</h1>
There are few old blogs for your reference but concept holds:
OLD: https://stackoverflow.com/questions/10459328/how-to-optimize-mysql-order-by-huge-limit-offsets
Really OLD: https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
ASKER
@pawan kumar UUID() did not work, I want to display random users without duplicating the users
select * from users
ORDER BY UUID()
So you are saying the above is not giving you random records?
ORDER BY UUID()
So you are saying the above is not giving you random records?
ASKER
@KV Yes! I want to display random users without duplicating the users, that is why I added RAND().
Are you saying I should re-write the code like the below to get the result?
SELECT count(*) as allcount FROM users;
...
$last_id = $_POST['row'];
...
SELECT * from user where id > $last_id limit $rowperpage;
...
<h1 class="load-more" id = "$last_id">Load More</h1>
<input type="hidden" id="row" value="0">
<input type="hidden" id="all" value="<?php echo $allcount; ?>">
Are you saying I should re-write the code like the below to get the result?
SELECT count(*) as allcount FROM users;
...
$last_id = $_POST['row'];
...
SELECT * from user where id > $last_id limit $rowperpage;
...
<h1 class="load-more" id = "$last_id">Load More</h1>
<input type="hidden" id="row" value="0">
<input type="hidden" id="all" value="<?php echo $allcount; ?>">
ASKER
@pawan kumar Yes! it did not, I just tried it again no random records displaying.
How many users are there in the table USERS?
ASKER
@pawan kumar For now, I have 350 USERS, which is subjected to increase as users register. The idea is to display users randomly without duplicating any users.
How many random you want to show each time ?
ASKER
I want to random all users once, for that day, this is why I added RAND(" . date("Ymd") . ") to the code.
For example, let say I have 10 users in total, I display the 1st random user
user-8
user-2
user-10
user-4
user-9
(Then I click load more button) -- see how the load more button works here: http://demo.makitweb.com/loadmore/
To display the next 5 random users
user-6
user-7
user-1
user-5
user-3
SO THE NEXT DAY I DISPLAY ANOTHER SET OF RANDOM USERS.
For example, let say I have 10 users in total, I display the 1st random user
user-8
user-2
user-10
user-4
user-9
(Then I click load more button) -- see how the load more button works here: http://demo.makitweb.com/loadmore/
To display the next 5 random users
user-6
user-7
user-1
user-5
user-3
SO THE NEXT DAY I DISPLAY ANOTHER SET OF RANDOM USERS.
The problem with randomizing is it is easier than you think to get duplicates. Also as you near the end of the list, it can take longer to load because you will have to keep looping through all the generated numbers until you get to a match.
As a suggestion, run a cron job that generates what you need to a special table of all users in a random order. You can run that cron job daily or hourly.
Now when you are querying users, just go to your table of users in the order already generated. I have used this approach for something similar where I needed a million total rows and it worked very well.
All you need is two columns. A row id and the userid. Then run a query that joins this with the actual user table and order by the row id of the randomized users table.
As a suggestion, run a cron job that generates what you need to a special table of all users in a random order. You can run that cron job daily or hourly.
Now when you are querying users, just go to your table of users in the order already generated. I have used this approach for something similar where I needed a million total rows and it worked very well.
All you need is two columns. A row id and the userid. Then run a query that joins this with the actual user table and order by the row id of the randomized users table.
There is nothing about RAND() (or any other method of generating random numbers) that will prevent duplication or provide unique sequences.
What you can do to create a random order of all data is add a new column `randomorder` type varchar(36) and UPDATE yourtable SET `randomorder` = UUID()
Then you can SELECT * FROM user ORDER by `randomorder`
Apply any additional strategy on pagination.
If you want a random order per day you only need to update this once a day.
Bye, Olaf.
Then you can SELECT * FROM user ORDER by `randomorder`
Apply any additional strategy on pagination.
If you want a random order per day you only need to update this once a day.
Bye, Olaf.
If I understand the question he is saying that he gets the same results each time (duplicated = same dataset)
I wouldn't use UUID() RAND() is a perfectly good solution and is exactly there for this sort of requirement - you just need to know how to use it.
Let's look at your code
RAND(20171123)
And will be the same for every query run today - so you use the same value to seed RAND() for every query - you will get the same results
Just use RAND() without a seed
I wouldn't use UUID() RAND() is a perfectly good solution and is exactly there for this sort of requirement - you just need to know how to use it.
Let's look at your code
$query = "select * from users order by RAND(" . date("Ymd") . ") asc limit 0,$rowperpage ";RAND(" . date("Ymd") . ")RAND(20171123)
And will be the same for every query run today - so you use the same value to seed RAND() for every query - you will get the same results
Just use RAND() without a seed
That's only partly right if the number of records of the table changes your random series will start over in the same manner but produce one more result changing the order. Also, it's not only about reproducible results, it's about the performance cost. If you simply store this result you only do it once and reuse it all day, the order is repeatable and pageable easily, no matter if you use LIMIT start, count or a pagination with a condition col>lastmax.
You can use RAND to fill that new randomorder column, though, if you define it as numeric/float/double float.
Having a column dedicated to random sorting you can also stay in control in other aspects, giving a weight to records not having been at the top the alst 7 days, sorting in new records randomly or at the end or at the top, as you like, etc, etc, etc.
Bye, Olaf.
You can use RAND to fill that new randomorder column, though, if you define it as numeric/float/double float.
Having a column dedicated to random sorting you can also stay in control in other aspects, giving a weight to records not having been at the top the alst 7 days, sorting in new records randomly or at the end or at the top, as you like, etc, etc, etc.
Bye, Olaf.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
UUID()
$query = "select * from users order by UUID() asc limit 0,$rowperpage ";