CODEWITHSUNDEEP
phpmysql
Aniket
Aniket

Reputation: 23

how to get random values from the MySQL database table without repeating?

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

Upvotes: 0

Views: 1490

Answers (2)

FatalError
FatalError

Reputation: 964

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.

Upvotes: 1

Nathaniel Ford
Nathaniel Ford

Reputation: 21220

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>;

Upvotes: 0

Related Questions