CodyMan CodyMan - 1 month ago 9
MySQL Question

How select random column value from Mysql database using PHP

I'm trying to fetch random column from database using Rand() function.
It's returning random value but many time it's 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.

Answer

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 id FROM cacheTable WHERE 1 ORDER BY RAND() LIMIT 1)");
$row = mysqli_fetch_assoc($query);
$foundId = (int)$row['id'];

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

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

$query = mysqli_query($mysql, "SELECT * FROM history WHERE id=".$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 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.