Updating MySQL records to One of Two Values

[shane0714]

I'm trying to write a script that updates a group of between 70 and 150 MySQL records from a value of 'unsent' to one of two values: 'a' or 'b'. Half of the records need to become 'a' while the other half need to be 'b'. New groups of records are added to the database every week and hence need to be updated after they have run through another script that prints them out. I was trying to figure out the most efficient way to do this. I thought about just using the rand() function and randomly assigning either a or b to each record in the hope that after 100 or so records it would be split fairly well. But this did not seem to work quite well enough. I did several tests of 10,000 records and the a's outnumbered the b's pretty consistently. Anyone have a ideas for this one?

[AndyB]

random is ... random. You can't trust it to produce exactly 50-50 distribution.  Either stick with the degree of randomness you're getting via rand() or simply alternate 'a' and 'b' through consecutieve records for a near-perfect 50-50 split.

[shane0714]

I've been thinking about your idea to manually alternate between 'a' and 'b'. However, the only way I can think of to do that would be to use a for{} structure. The problem I see with that is that I will be making between 75 - 150 separate queries to the server. If there is a way to alternate between the queries as simply as you make it seem, I am interested to find out how.

 

I have worked on another solution which requires just three queries: count the total number of records to be updated, divide that number by two and update half of them to 'a' and half to 'b'. Then use the rand() function to alternate between 'a' and 'b' if there is an odd number of records.

 

Here's the code (minus error checking):

$sql = "SELECT mailing FROM dig_leads WHERE mailing='NA'";
$result = @mysql_query($sql)

$half = MySQL_num_rows($result) / 2;
$decimal = explode(".",$half);
if($decimal[1] != "0") {
	$flag = "true";
	$half = floor($half);
}

$aquery = "UPDATE dig_leads SET mailing = REPLACE(mailing,'NA','a') LIMIT $half";
$aresult = @mysql_query($aquery)
$bquery = "UPDATE dig_leads SET mailing = REPLACE(mailing,'NA','b') LIMIT $half";
$bresult = @mysql_query($bquery)
if($flag == "true") {
$random = rand(1,2);
switch($random) {
	case 1:
		$random = "a";
	case 2:
		$random = "b";
}
$lastquery = "UPDATE dig_leads SET mailing = REPLACE(mailing,'NA',$random)";
	$lastresult = @mysql_query($lastquery)
}

The problem is that LIMIT does not work correctly. I tested it from a command line and its updating 0 records even though it returns no errors. I have used the same query without LIMIT and it works perfectly. Any ideas on how I could either make it work with LIMIT or do it entirely different?