How select random column value from Mysql database using PHP

Question

I'm trying to fetch random column from database using Rand() function. It's returning random value but many time it is returning duplicate.

This is what my database table look like.

Column	Type	Null	Default
no	int(30)	No
postid	varchar(100)	Yes	NULL
byuser	varchar(32)	Yes	NULL
likeslimit	int(30)	No
createdon	date	No

And this is what my PHP code is.

$query = mysqli_query(
    $mysql,
    "SELECT postid FROM history ORDER BY Rand() LIMIT 1"
);

if (mysqli_num_rows($query) == 1) {
    while ($row = mysqli_fetch_assoc($query)) {
        echo $row['postid'];
    }
}

I want it to always return random never the same till the end of data reached.

put the selection in array and check if in_array and ignore it — Samir Nabil
– Samir Nabil, Commented Oct 24, 2016 at 7:43

linktoahref · Accepted Answer · 2021-10-28 15:02:02Z

2

Don't use loop and condition you want only 1 limit try this

$query = mysqli_query(
    $mysql,
    "SELECT postid FROM history ORDER BY Rand() LIMIT 1"
);

$row = mysqli_fetch_assoc($query)

echo $row['postid'];

edited Oct 28, 2021 at 15:02

linktoahref

8,0273 gold badges32 silver badges54 bronze badges

answered Oct 24, 2016 at 7:44

Khetesh kumawat

7118 silver badges15 bronze badges

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

1 Comment

CodyMan Over a year ago

Still duplicates.

krasipenkov · Accepted Answer · 2016-10-24 09:08:26Z

1

This is the way RAND in mysql works and will repeat the results from time to time. But you can achieve such functionality by using mysql with php.

$query = mysqli_query($mysql, "SELECT postid FROM cacheTable WHERE 1 ORDER BY RAND() LIMIT 1");
$row = mysqli_fetch_assoc($query);
$foundId = (int)$row['postid'];

if((int) $foundId === 0) { // NO records left in cacheTable then fill it up again
    mysqli_query($mysql, "INSERT INTO cacheTable (postid) SELECT postid FROM history");
    $query = mysqli_query($mysql, "SELECT postid FROM cacheTable WHERE 1 ORDER BY RAND() LIMIT 1");
    $row = mysqli_fetch_assoc($query);
    $foundId = (int) $row['postid'];
}

mysqli_query($mysql, "DELETE FROM cacheTable WHERE postid=".$foundId); // DELETE the record

$query = mysqli_query($mysql, "SELECT * FROM history WHERE postid=".$foundId);
$result = mysqli_fetch_assoc($query);

cacheTable will have only one column - ID (primary key) which will hold the corresponding ID (primary key) from history. cacheTable structure:

|------
|Column|Type|Null|Default
|------
|postid|varchar(100)|Yes|NULL
|-----

cacheTable will fill with all the ids from history table (it will be done once the cacheTable is empty). You will select rand result from the cacheTable and you will delete it then so it will not appear in the next selects. When we are out of records in cache table it will populate again.

NB: this approach has one major drawback - when you have new entries in history table they won't be available in cache table until it is empty and filled again.

edited Oct 24, 2016 at 9:08

answered Oct 24, 2016 at 8:07

krasipenkov

2,0141 gold badge13 silver badges13 bronze badges

24 Comments

CodyMan Over a year ago

What is the cacheTable Structure?

CodyMan Over a year ago

While my history table have column named postid

CodyMan Over a year ago

Now I have to play with primary keys

krasipenkov Over a year ago

So postId will be the column in cacheTable.

CodyMan Over a year ago

Is it necessary to make it primary key ?

|

linktoahref · Accepted Answer · 2021-10-28 15:07:28Z

0

This is the code Samir Nabil Suggested :

session_start();

$_SESSION['dupes'] = array();

$query = mysqli_query(
    $mysql,
    "SELECT postid FROM history ORDER BY Rand() LIMIT 1"
);

if (mysqli_num_rows($query) == 1) {
    while ($row = mysqli_fetch_assoc($query)) {
        if (!in_array($row['postid'], $_SESSION['dupes'])) {
            echo $row['postid'];
            $_SESSION['dupes'][] = $row['postid'];
        }
    }
}

edited Oct 28, 2021 at 15:07

linktoahref

8,0273 gold badges32 silver badges54 bronze badges

answered Oct 24, 2016 at 7:48

Nabil Ali

1571 silver badge13 bronze badges

3 Comments

CodyMan Over a year ago

Basically array will be renewed everytime I refresh my page

CodyMan Over a year ago

maybe I gotta set value in $_SESSION ?

Nabil Ali Over a year ago

If you want the array to be persistent after page refresh you could store it in a session var ... code has been updated.

Collectives™ on Stack Overflow

How select random column value from Mysql database using PHP

3 Answers 3

1 Comment

24 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

24 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related