CODEWITHSUNDEEP
phpmysqlselectrandom
filipe
filipe

Reputation: 29

PHP MySQL select random value from table

I am trying to get a random city from the location table but is not working, I have searched over Stack and tried many ways still not getting it work.

<?php  include "../database.php"?>
<?php

$sSQLQuery = mysql_query("SELECT city FROM location ORDER BY RAND() LIMIT 1;");

$aResult = mysql_query($sSQLQuery); 

?>

<span class="h3"><?php echo $aResult ?></span>

Upvotes: 1

Views: 3000

Answers (3)

Martin
Martin

Reputation: 22760

Using the method you've given is resource intensive, not very random and slow and inefficient. MySQL RAND() and especially ORDER BY RAND() is notably inefficient.

To ORDER BY the random value the whole of the table needs to be called and then shuffled, like shuffling every card in the pack in order to pick out the first card from the top, that leaves 51 cards that are called, loaded and shuffled for no output.

There are various discussions on the topic that mutilate the basic SQL code to get a slightly better result such as : https://stackoverflow.com/a/17757787/3536236

More can be read here: http://theoryapp.com/select-random-records-in-mysql/

So, what can be done?

Well, if you have a Id column (indexed, of course) for your location table, which you really, really should, then this makes life a lot easier.

If you have NO GAPS in the Id column then you can simply find the maximum value and pick a random number from 1 to the maximum,

So 

$maxSql = mysqli_query($connection, "SELECT id FROM location ORDER BY id DESC LIMIT 1"); 
$max = mysqli_fetch_array($maxSql);
$max = $max['id'];

This orders the table by the indexed numerical ID column and takes the top value, which will be the highest, then plug this into a PHP randomiser to give you:

$id = mt_rand(1,$max);

Then 

$sSQLQuery = mysqli_query($connection, "SELECT city FROM location

WHERE id = '$id' LIMIT 1;"); $city = mysqli_fetch_array($sSQLQuery);

If you do have gaps then it would be easier to run the first part in a loop to check that the value exists, and if it exists then return that row, such as:

unset($found);
while(!$found){
$sSQLQuery = mysqli_query($connection, "SELECT city FROM location WHERE id = '$id' LIMIT 1;");
if (mysqli_num_rows($sSQLQuery)){
    $found = true;
    $city = mysqli_fetch_array($sSQLQuery);
    }
$id = mt_rand(1,$max);
}
unset($found);

(There is a lot more that can be done and the code here is not perfect- far from it- but the concept I want to get across is that with any meaningful sized database table then it is better and far more efficient for the "random" factor to be sourced by PHP rather than from MySQL internally. PHP offers more random scope and easier random generation that MySQL, and with tables above 1000 rows, RAND() really, really should be avoided, I have found it add ~0.25-0.4 seconds to page loads ordering a set of 100 results in a "random" way from a table of ~500. )

Also research and use MySQLi (or PDO) as a matter if high importance, there is no future (and little presence) in MySQL_ . Updating from MYSQL to MYSQLI

Upvotes: 1

Ali Mehdi
Ali Mehdi

Reputation: 924

Use something like this:

while($row = mysql_fetch_array($aResult))
                {
                  echo '<tr class="select">';
                  echo '<td>'.$row['city'].'</td>';
                  echo '</tr>';
                }

Upvotes: 0

MenukZ
MenukZ

Reputation: 79

Try this:

    <?php
$result = $mysqli->query("SELECT COUNT(*) FROM mytable")
while ($row=$result->fetch_row()) { $count = $row[0]; }
$offset = rand(0,$count);
$result = $mysqli->query("SELECT * FROM mytable LIMIT 1 OFFSET $offset");
...

Upvotes: 0

Related Questions