Select a random record from two mysql tables with limited date?

Question

I have two tables

CREATE TABLE `shares` (
  `id` int(11) NOT NULL auto_increment,
  `entry_id` int(11) default NULL,
  `service` int(11) default NULL,
  `created_date` datetime default NULL,
  `ip` varchar(45) default NULL,
  PRIMARY KEY  (`id`),
  KEY `entry_id` (`entry_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2570 DEFAULT CHARSET=latin1;

CREATE TABLE `entries` (
  `id` int(11) NOT NULL auto_increment,
  `first_name` varchar(255) default NULL,
  `last_name` varchar(255) default NULL,
  `street_address` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `postal_code` varchar(255) default NULL,
  `province` varchar(255) default NULL,
  `daytime_phone` varchar(255) default NULL,
  `email_address` varchar(255) default NULL,
  `tos` int(11) default NULL,
  `subscribe` int(11) default NULL,
  `ip` varchar(45) default NULL,
  `created_date` datetime default NULL,
  `pin` varchar(255) default NULL,
  `return` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `email_address` (`email_address`)
) ENGINE=InnoDB AUTO_INCREMENT=36503 DEFAULT CHARSET=latin1;

And I need to generate 10 random records from both tables.

Info: Each share is a single extra ballot, but people can share using multiple services (each service is still a single extra ballot) and share multiple times (which is still a single extra ballot).

So I need to random 10 records from entries, taking into account if a single entry id exists then select from that also.

Example,

User 1
User 2
User 3
User 4 1 Share
User 5
User 6 1 Share
User 7 2 Shares
User 8
User 9
User 10 3 Shares

It would take that and generate a random record where

User 1 (1 chance)
User 2 (1 chance)
User 3 (1 chance)
User 4 1 Share (2 chances)
User 5 (1 chance)
User 6 1 Share (2 chance)
User 7 2 Shares (2 chance)
User 8 (1 chance)
User 9 (1 chance)
User 10 3 Shares (2 chance)

I don't know how I can do this? Any help

Related: http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql — Maxime Pacary, Dec 12 '11 at 16:07

score 0 · Accepted Answer · answered Dec 12 '11 at 17:13

My approach would be to create an array of 10 random ids from 'entries', then see if those ids exist in 'shares'. If they do, put that id into the array a second time, giving them that second chance. Then it is a random selection from that end array.

One way to do this could be something like:

$entryIdArray = array();
$query = mysql_query(SELECT * FROM entries ORDER BY RAND LIMIT 10);
while($row = mysql_fetch_array($query)){
   $entryIdArray[] = $row['id'];
}


foreach($entryIdArray as $thisEntrant){
   $query = mysql_query(SELECT * FROM shares WHERE entry_id = $thisEntrant);
   if(mysql_num_rows($query) > 0){
      $entryIdArray[] = $thisEntrant;
   }
}

$winningEntrantId = $entryIdArray[rand(0, sizeof($entryIdArray)-1)];

I've never tried adding to an array while looping through that same array, so you may have to make a separate array to add all the ids to that one instead, but this shows the basic idea.

Select a random record from two mysql tables with limited date?

1 Answers1