Getting # of rows that are randomly picked...

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

I've got a little problem here. I want to select # of rows (at random) and the rows that would be selected should be randomly picked as well...

So I am using the following function that I provided here previously for something else to generate a # of random non-overlapping ids...
PHP Code: [ Select ]
<?php
    function get_rands($min, $max, $amount)
    {
        $amount = (int) $amount;
        $rands = array();
        $rand = false;
 
        for($i = 0; $i < $amount; ++$i)
        {
            while(!$rand || in_array($rand, $rands))
            {
                $rand = rand($min, $max);
            }
           
            $rands[] = $rand;
        }
 
        return $rands;
    }
?>
  1. <?php
  2.     function get_rands($min, $max, $amount)
  3.     {
  4.         $amount = (int) $amount;
  5.         $rands = array();
  6.         $rand = false;
  7.  
  8.         for($i = 0; $i < $amount; ++$i)
  9.         {
  10.             while(!$rand || in_array($rand, $rands))
  11.             {
  12.                 $rand = rand($min, $max);
  13.             }
  14.            
  15.             $rands[] = $rand;
  16.         }
  17.  
  18.         return $rands;
  19.     }
  20. ?>

And to select the given amount of MySQL rows (The amount is determined from the MySQL database [a different table]), I use the following method.
PHP Code: [ Select ]
 
        $n_sites = (($n_sites > $num_sites) ? $num_sites : $n_sites);
       
        // Getting the random IDs
        $rand_ids = $this->funcs->get_rands(0, $num_sites, $n_sites);
       
        // Generating the rest of the resultant
        while($id = $rand_ids)
        {
            mysql_data_seek($result, $id);
            while($row = mysql_fetch_assoc($result))
            {
                $populated_cat .= "<a href=\"{$row['url']}\" class=\"l\">{$title}</a>\n";
            <span style="color: #FF0000">} // <- This is where the script hangs...</span>
        }
  1.  
  2.         $n_sites = (($n_sites > $num_sites) ? $num_sites : $n_sites);
  3.        
  4.         // Getting the random IDs
  5.         $rand_ids = $this->funcs->get_rands(0, $num_sites, $n_sites);
  6.        
  7.         // Generating the rest of the resultant
  8.         while($id = $rand_ids)
  9.         {
  10.             mysql_data_seek($result, $id);
  11.             while($row = mysql_fetch_assoc($result))
  12.             {
  13.                 $populated_cat .= "<a href=\"{$row['url']}\" class=\"l\">{$title}</a>\n";
  14.             <span style="color: #FF0000">} // <- This is where the script hangs...</span>
  15.         }

It's in a class as you can see, but let me describe a few variables so you will understand what I'm trying to do.

The $n_sites is the number of rows to retrieve... in this case, it is five... the $num_sites is the number of rows I do have in the database.

When I run this, I get the following error message
browser wrote:
Fatal error: Maximum execution time of 30 seconds exceeded in C:\wamp\www\censite ttest\classes\sdb.php on line 98

Line 98 is shown in red and noted in the code I use above...

Any help on how to solve this? Maybe I'm going about this the wrong way, but this is how I know how to do it right now...

Thanks...
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

Nevermind, I fixed it with the following code...
PHP Code: [ Select ]
while($row = mysql_fetch_assoc($result))
{
    if(in_array($row['SID'], $rand_ids))
    {
        $populated_cat .= "My Text Here\n";
    }
}
  1. while($row = mysql_fetch_assoc($result))
  2. {
  3.     if(in_array($row['SID'], $rand_ids))
  4.     {
  5.         $populated_cat .= "My Text Here\n";
  6.     }
  7. }

Tell me if I'm doing it the right way though...
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

wow, I can't believe I was foolish enough to believe that that fixed my problem. Right now, here is how the table structure looks like:
SQL Code: [ Select ]
CREATE TABLE `censite_sites` (
 `SID` tinyint(4) NOT NULL AUTO_INCREMENT,
 `CID` int(10) NOT NULL,
 `UID` int(10) NOT NULL,
 `url` varchar(50) NOT NULL,
 `title` varchar(30) NOT NULL,
 `description` varchar(500) NOT NULL,
 `site_img` varchar(50) NOT NULL COMMENT 'Site Image Preview',
 PRIMARY KEY (`SID`)
)
  1. CREATE TABLE `censite_sites` (
  2.  `SID` tinyint(4) NOT NULL AUTO_INCREMENT,
  3.  `CID` int(10) NOT NULL,
  4.  `UID` int(10) NOT NULL,
  5.  `url` varchar(50) NOT NULL,
  6.  `title` varchar(30) NOT NULL,
  7.  `description` varchar(500) NOT NULL,
  8.  `site_img` varchar(50) NOT NULL COMMENT 'Site Image Preview',
  9.  PRIMARY KEY (`SID`)
  10. )

The first 8 of the ones in the database have the CID as '1' and the 9th has the CID of '2'.

I'm trying to limit it down to 5 results where CID = #2.

I want the 5 selected to be randomly picked from the database and put in the results ordered by name ascending.

I thought I've done that with the previous code, but the random number generates '1' but if the CID = '2' then the SID starts at '9' (Or whatever it could be... it is never set), so it doesn't give any results.

I don't really know how to explain :( I hope I made myself clear here ...
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

Please... I really need help on this. Let me try to explain this again.

I have a table with the structure shown in previous post. The fields are:

SID - The site ID.
CID - The category ID the site is on.
UID - The user's ID that submitted the site

... and the rest are self-explanatory. What I want though, is select 5 random rows where the CID is 1 and 5 random rows where the CID is 2 and so on... If there are 5 or less rows in the table, then it would retrieve 5 or as many rows there are...

I think that makes sense... is there any way to do that?
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

Add a floating-point column named "rand_val" to the end of your table.

Setup a cron job to execute a query similar to this every minute or as long as you can get by before cached data would be considered too stale to use.

Code: [ Select ]
UPDATE `db_name`.`censite_sites`
SET `rand_val` = RAND();
  1. UPDATE `db_name`.`censite_sites`
  2. SET `rand_val` = RAND();


Then you can simplify your retrieval and avoid needing to generate random ordering criteria with every read.

Code: [ Select ]
SELECT * FROM `censite_sites`
ORDER BY `rand_val` DESC, `title` ASC
LIMIT $random_limit
  1. SELECT * FROM `censite_sites`
  2. ORDER BY `rand_val` DESC, `title` ASC
  3. LIMIT $random_limit
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

Alright... thanks Joebert. I'll try that
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

Just to let you know... it worked perfectly :)

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 55 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.