SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict ketting00's Avatar
    Join Date
    Jul 2011
    Posts
    325
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    How to prevent MySQL random picks 0 result

    Hi,

    I use these codes to pick up a value at random:
    Code:
    $offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
    $offset_row = mysql_fetch_object( $offset_result );
    $offset = $offset_row->offset;
    $result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " );
    I take it from here: http://akinas.com/pages/en/blog/mysql_random_row/

    It's fast and very good.

    However, some times the results are equal to zero.

    How do I always pick up at least 1 value or a constant number such as 10 results.

    Thanks

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,057
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    My first guess is because COUNT(*) returns the number of rows, so if you have 20 rows, your max value will be 20, LIMIT 20, 1 would result in no rows. So you likely want (COUNT(*) - 1), so your new max value is 19, so LIMIT 19, 1 is valid.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I have put together a stored procedure which takes a single parameter and number of rows to be returned as parameters. This in essence does everything on the server so that there is no back and forth and will be slightly more efficient in the stored procedure. I have a white paper describing the procedure and the way to call it http://www.it-iss.com/mysql/mysql-st...-from-a-table/

    Code:
    DROP PROCEDURE IF EXISTS sp_random;
    
    DELIMITER $$
    CREATE PROCEDURE sp_random(IN pTableName VARCHAR(64), IN pLimit SMALLINT UNSIGNED) 
    NOT DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'A procedure that returns a random set of rows from a table'
    BEGIN
    	DECLARE lCount INT UNSIGNED;
    
    	SET @uQuery := CONCAT('SELECT COUNT(1) INTO @uCount FROM ', pTableName);
    	PREPARE stmt FROM @uQuery;
    	EXECUTE stmt;
    
    	IF @uCount <= pLimit THEN
    		-- Return all values
    		SET @query := CONCAT('SELECT * FROM ', pTableName);
    	ELSE
    		-- Return limited number of entries
    		SET @offset := ROUND((@uCount - pLimit) * RAND(),0);
    		SET @query := CONCAT('SELECT * FROM ', pTableName, ' LIMIT ', @offset, ',', pLimit);
    	END IF;
    
    	PREPARE stmt FROM @query;
    	EXECUTE stmt;
    	DEALLOCATE PREPARE stmt;
    END;
    $$
    DELIMITER ;

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,057
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rcashell View Post
    Code:
    		SET @offset := ROUND((@uCount - pLimit) * RAND(),0);
    Just wanted to point out this very important line, which lines up with my assumption earlier exactly. Notice how he is subtracting the number of random items he wants returned from the COUNT()
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,994
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    For grabbing a random row from the table, have you considered just grabbing the possible rows and then using array_rand() to grab the number of random rows required?

    Have a read of this thread where different methods for grabbing a number of rows in a random order is discussed.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,784
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Depending on how many rows there are in the table and how many you want to randomly retrieve it may be worth considering simply using ORDER BY RAND() and then set the LIMIT to the number of entries you want. For tables with under about 1000 rows this is reasonably efficient. Similarly if you want to retrieve 30 rows at random from a table with fewere than about 50,000 rows.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #7
    SitePoint Addict ketting00's Avatar
    Join Date
    Jul 2011
    Posts
    325
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys,

    cpradio method works great. I don't have a chance to test what felgall explains, because my table records is still low. I'm looking forward to test SpacePhoenix method as well. rcashell codes look strange.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •