0

I have a table called topics and in that there is field called vTopicName. I want to display random topics by clicking on the button without repeating them what should be the SQL query for it??

In controller I call

public function browse() { $data['topic'] = $this->topics_model->get_random_topic(); $topic = $data['topic']['vTopicName']; redirect("/$topic"); }

In the model there is

public function get_random_topic() { $query = $this->db->query("SELECT * FROM ws_topics ORDER BY RAND() LIMIT 1"); return $query->row_array(); }

THis will redirect to domainname.com/topicname

Improve this question
11
  • You'll have to keep track of the ones already displayed, by, for example, setting a flag against those already used. Show us what you've got so far and we'll be able to help you more. Commented Jul 24, 2012 at 16:51
  • I have used the below function in the codeigniter framework in the model: Commented Jul 24, 2012 at 16:53
  • public function get_random_topic() { $query = $this->db->query("SELECT * FROM ws_topics ORDER BY RAND() LIMIT 1"); return $query->row_array(); } Commented Jul 24, 2012 at 16:53
  • This will potentially return the same value on two subsequent calls. Commented Jul 24, 2012 at 16:55
  • @AleksG Assuming the page isn't being reloaded in between, they could just fetch all the topics in random order and serve them up one by one. It seems like in this case, one large query would be more efficient than repeating a small query with lots of temp data about what's already been retrieved. Commented Jul 24, 2012 at 16:59

2 Answers 2

Reset to default
1

First. Using Database

You should add a datetime field lets say 'displayed_on' in the same table, when you display a topic, set its the displayed_on to current time. Now, when you next fetch the records you can apply ascending sort to displayed_on field and limit the results to skip the ones that were displayed before (or recently displayed).

select vTopicName from (select * from topics order by rand() limit 30) order by displayed_on asc limit 5;

Second. Using Session

You can also put all the displayed topics in a session variable and can skip them while fetching next bunch of topics.

$query = "select vTopicName from topics where topics.id not in (".implode(',',$_SESSION['displayed_topics']).") order by rand limit 5;

If there are few records in the table, you should reset the session variable when you get less than 5 records from the above query.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

This will not ever return a result approaching 'random'. On the first past it's pseudo random but then each new cycle will be the same.
Think of a sub-query. like select vTopicName from (select * from topics order by rand() limit 30) order by displayed_on asc limit 5;
@NathanielFord "I want to display random topics without repeating them.. " is pseudorandom. Truly random would be "I want to display a random topic on each view.", and using a random method to grab a random entry in a large table would suffice. The OP is asking for a pseudorandom solution though -- one of which is described in this answer.
@DavidCowden It's random on each pass, it just random-with-removal. This answer now describes a random solution, I agree. Note that I indicated this answer's original form would be pseudo random not on any sort of randomized function but based on how the database happened to return a set of unordered rows (which probably would have been based on insert time).
@NathanielFord random-with-removal == pseudorandom
|
0

This will be your basic query:

SELECT * FROM vTopicName WHERE displayed = false ORDER BY RANDOM() LIMIT 1

You will then have to 'flag' that topic as displayed. Hard to say exactly how to do that without knowing your primary id on the table, but here is an approximation:

UPDATE vTopicName SET displayed = true WHERE primaryId = <id of the row you got>

However, if you want different users to be affected differently, you will need a separate table to cover their actions:

SELECT * FROM vTopicName as topic JOIN userActivity AS user ON topic.id = user.topicid WHERE user.displayed = false ORDER BY RANDOM() LIMIT 1;

Then you need to update the userActivity table:

UPDATE userActivity SET displayed = true WHERE topicid = <topic id> AND id = <userid>

In order to ensure that users will have all topics available once they've seen everything you will also have to run this query:

SELECT COUNT(topicid) FROM userActivity WHERE id = <user id> AND displayed = false;

If this is equal to 0 (or some low number), you will then have to run:

UPDATE userActivity SET displayed = false WHERE id = <user id>;
Improve this answer

17 Comments

this will create problem when multiple users are viewing the table if there are 10 topics and all of them are currently being viewed then all would be false so next user will not be able to see anything
So, that was not part of your specification, but you're now talking about a much larger issue. I'll try and modify my solution to provide for it.
@Aniket I have provided a solution, but you have poorly specified how and when you want your table to reset: you said no repeats - it is easy to get that functionality. Harder to get a particular pattern of repeats (such as when you run out of topics to show).
@Aniket Again, when you say 'this', what precisely do you mean? Do you mean a pseudo-random selection of a topic? Or do you mean a strictly random without repetition selection of a topic? These are distinct things.
by the saying no repeats i meant that the topic for example : "Apple" should not occur simultaneously
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.