$rand = rand(1, 3); $query = mysql_query("SELECT `tid` FROM `tasks` WHERE `tprice` = $rand");

Here is the problem:

if there is no tprice = 2 in the database, and rand is exactly 2, then the query will return void.

How can this be circumvented? That is, if rand got 2 and there is no tprice = 2 in the database, then output something like something else. Suppose tprice> 2

    3 answers 3

    @klopp , as far as I remember, RAND () in ORDER BY will be called for each line, i.e. if there are lots of records, RAND will be calculated, and then a lot of time will be spent on sorting.

    @sergiks ,

     SELECT DISTINCT tprice FROM ... $rand = array_rand( $tprices); SELECT `tid` FROM `tasks` WHERE `tprice` = $rand

    Again, if there are a lot of records, then $ tprices will weigh a lot, and all entries will be selected for too long.

    @ModaL , you can try:

     SELECT COUNT(`tid`) AS CNT FROM `tasks` WHERE `tprice` BETWEEN 1 AND 3 $rand = rand(0, CNT); SELECT `tid` FROM `tasks` WHERE `tprice` BETWEEN 1 AND 3 LIMIT $rand, 1

    Those. we get the number of records satisfying the condition, we get a random number ranging from 0 to the maximum, and then we select one element satisfying the condition from the position $ rand.

    The only thing I do not know how will affect BETWEEN speed

    • Well, we do not know all the conditions, as usual :) You can even choose by between (with or without limits), and from the resulting array you can choose without resorting to the database. - user6550 pm
    • @sergiks, exactly ... Since we get $ tprices, why not add a tid to the SELECT too? Then there will be no need for a second call to the database. But all the same - why get too much, huge and slow? - BOPOH
    • > if there are a lot of records, then $ tprices will weigh a lot, and all records will be selected too long. We do not know, maybe there are only 10 records. If there is a heap, you can put a set of unique tprices in a temporary table, for example. with engine=Memory , and solve the problem entirely on the SQL side. And, of course, we need an index on the tprice column, since such requests are of interest - everything will fly. - Sergiks

    Specify: can a query return several rows, or is tprice unique? Millions of records, or just a dozen?

    In general, you must first obtain an array of existing tprice values:

     SELECT DISTINCT tprice FROM ...

    and then choose a random one:

     $rand = array_rand( $tprices);

    and select entries with this tprice:

     SELECT `tid` FROM `tasks` WHERE `tprice` = $rand 
       SELECT `tid` FROM `tasks` WHERE `tprice` BETWEEN 1 AND 3 ORDER BY RAND()