0

so I'm a bit stuck on this. Ok so, I'm making a small online store, when the user opens the products page a query runs using PHP to get the products from the database and display them on the page. This is the basic query:

SELECT product_id, name, price FROM products ORDER BY RAND() LIMIT 5;

That's running perfectly, the problem is when the user wants to load more products, I run the same query which can cause the same products already displayed to show up again.

Can anyone help, I want to get the products in random order and at the same time not return the products that are already being displayed on my page.

Also, while I was trying to figure this out I came across a question here that says that the RAND() function in MySQL can cause high load on the server. How can I achieve the same behavior without using the RAND() function. Any help is greatly appreciated.

Community
  • 1
  • 1
Brian Moreno
  • 977
  • 4
  • 11
  • 39
  • @jayblanchard are you ok? – Strawberry May 05 '17 at 20:20
  • Not enough coffee @Strawberry – Jay Blanchard May 05 '17 at 20:20
  • The `LIMIT 5` is to only return 5 rows from the database every time this query is executed. – Brian Moreno May 05 '17 at 20:21
  • Why does `RAND()` have a bad reputation? Because the query you showed us has no choice but to scan your whole table, generate a random number for each row, then sort the whole table by the random numbers, then discard that result for all but five rows. – O. Jones May 05 '17 at 20:24
  • @JayBlanchard with respect my answer to the question you mentioned in your kvellhammar blow doesn't address this question. This questioner's problem is that he's getting *some* duplicates, not all duplicates. This answer presents a good way of doing this deal-randomization (as opposed to roll-randomization) http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql#answer-214580 – O. Jones May 05 '17 at 20:27
  • `ORDER BY RAND()` is generally a bad idea performance-wise, and rarely makes much sense business-wise. I typically recommend that my customers set their default sort to order by most popular (i.e., most likely to sell.) Depending on your business, you might also benefit from something like order by newest or order by on-sale. You don't want `ORDER BY RAND()` to fill your home page with crap that nobody ever wants. – Alex Howansky May 05 '17 at 20:28
  • If you do not want to use RAND(), you could make a query for getting all id's and use the shuffle method to randomize the array, use array_chunk to to get the first five and then use it again later to get the next five and so on... Then you could have the array take care of the products and you would not get any duplicated products. – Ulrik McArdle May 05 '17 at 20:28
  • True @O.Jones, but that would be the very nature of `rand()`, would it not? I've a feeling the OP is looking for something more similar to pagination. The other alternative is marking the product as 'displayed' each time they are loaded for a particular consumer. That could become quite gnarly, depending on the number of products. – Jay Blanchard May 05 '17 at 20:29
  • @AlexHowansky True, I was initially thinking of ordering them from newest to oldest (which is easier) but if their is, let's say 500 products, the oldest ones will hardly be seen by the users. That's why I wanted to show them in a random order so the user wont see the same products over and over. – Brian Moreno May 05 '17 at 20:35
  • @UlrikMcArdle Thanks I'll look into that. – Brian Moreno May 05 '17 at 20:35
  • @O.Jones Ahh ok, I'll look into that type of randomization. – Brian Moreno May 05 '17 at 20:40
  • _"the oldest ones will hardly be seen by the users"_ Is that necessarily a bad thing? The sort order should put the most-likely-to-be-bought items first. Regardless, if you have 500 items, you should be giving the users a sort selector so they can pick the order they want. Don't make them scroll through everything. – Alex Howansky May 05 '17 at 20:41

0 Answers0