[dsumpter]

I am trying to set up my script to select a random entry for each clientID in my table. For some reason on the clientID's that have more entries I am getting a double or triple return. I think it has something to do with the client side timing out on the script while the sql statement is trying to run, but I am not sure. Does anyone have any advise? Below is my script. Thanks in advance!!!

<?



/* MySQL details */

$dbhost = "localhost";
$dbname = "aci";
$dbuser = "me";
$dbpass = "mypass";

### Default Connection String ###
$link = mysql_connect($dbhost, $dbuser, $dbpass) or die ("Unable to connect to MySQL server." . mysql_error());
$db = mysql_select_db($dbname, $link) or die ("Unable to select database: " . mysql_error());

### Further MySQL Info ###
// Name of the table you wish to get random items out of
$randtable = "target";

### Values you do NOT want included in the random drawing ###
// Turns this function off or on
$xvals = "on";
// set Array of values you do not want included - separated by commas
$xarrsql = "SELECT * FROM monthlyWinner WHERE mwID > '0'";

$xarrResult = @mysql_query($xarrsql,$link) or die("Couldn't Execute xarrsql query.");

while ($row = mysql_fetch_array($xarrResult)) {
$targetIDx = $row['targetID'];

$xarrBlock .= "\"$targetIDx\", ";

}

$xarr = array("$xarrBlock");

//print ("$xarrBlock <br>");

// id field to pick at random
$idvar = "targetID";



//get client table info

$sql = "SELECT clientID, clientName FROM client WHERE clientID > '0' ORDER BY clientName";

$clientResult = @mysql_query($sql,$link) or die("Couldn't Execute query.");

while ($row = mysql_fetch_array($clientResult)) {

$clientIDm = $row['clientID'];
$clientName = $row['clientName'];


//Start random for client 1

// Make a query to draw the records from the table
$randsql_result = mysql_query("SELECT targetID, clientID FROM $randtable WHERE clientID = $clientIDm",$link) or die ('Database Query Failed.
Please check the $randtable variable under Further MySQL Info, and make sure you have the correct table name.');

if(mysql_num_rows($randsql_result) == 0) {
print "- <b><i>$clientName</b></i> had no valid entries in the table, so no monthly winner was sent <br>";

//mysql_free_result($randsql_result);

}

if(mysql_num_rows($randsql_result) != 0) {

// Load entries into an array to select one randomly
$i = 0;
while($row = mysql_fetch_row($randsql_result)) {
if($xvals == "on") {
if(!in_array("$row[0]", $xarr) && $row[0] != "") {
$rand[$i] = $row[0];
$i++;
}
} elseif($row[0] != "") {
$rand[$i] = $row[0];
$i++;
}
}
mysql_free_result($randsql_result);

// Select a random entry from the assembled array
if(count($rand) == "0") { print "There are no entries in the array!"; exit; }
$randnum = array_rand($rand);

$rst = mysql_query("SELECT * FROM $randtable WHERE $idvar = '$rand[$randnum]' LIMIT 1", $link) or die('Could not retrieve random
entry from databse. Please check the $idvar variable under IMPORTANT TABLE INFO to make sure the id field is correct');
// $q = mysql_fetch_array($rst);

while ($row = mysql_fetch_array($rst)) {

$qtargetID = $row['targetID'];
$qtargetEmail = $row['targetEmail'];
$qclientID = $row['clientID'];
}

$qsql = "INSERT INTO monthlyWinner VALUES ('', '00000000000000', '$qtargetID', '$qclientID')";

$qsqlResult = mysql_query($qsql,$link) or die("Couldn't update monthlyWinner with query.");

// this is where the email info will go

print "<b>- $clientName:</b> targetID: $qtargetID, targetEmail: $qtargetEmail <i>SENT</i><br>";

mysql_free_result($rst);

}

}
?>

[ChristGuy]

Greetingz,

Firstly could you post the structure of the tables and some test data...

Also

I'd change:

PHP Code:

$rst = mysql_query("SELECT * FROM $randtable WHERE $idvar = '$rand[$randnum]' LIMIT 1", $link) or die('Could not retrieve random 
entry from databse. Please check the $idvar variable under IMPORTANT TABLE INFO to make sure the id field is correct');
// $q = mysql_fetch_array($rst);

while ($row = mysql_fetch_array($rst)) {

$qtargetID = $row['targetID'];
$qtargetEmail = $row['targetEmail'];
$qclientID = $row['clientID'];
} 


to

PHP Code:

$rst = mysql_query("SELECT * FROM $randtable WHERE $idvar = '$rand[$randnum]' LIMIT 1", $link) or die('Could not retrieve random 
entry from databse. Please check the $idvar variable under IMPORTANT TABLE INFO to make sure the id field is correct');
 $row = mysql_fetch_array($rst);

$qtargetID = $row['targetID'];
$qtargetEmail = $row['targetEmail'];
$qclientID = $row['clientID']; 


You could try that...

[dsumpter]

Thanks for your help... Here are the aplicable tables... I don't know how to get you some sample data, because I think the problems only exists on the clients that have a lot of targets... so I would have to almost give you all of the data. I don't mind doing that, but if I do, I want to do it on a more secure platform than uploading to the post because it is fairly sensitive info. Let me know... Thanks again, Dan

TABLE target (
targetID int(11) NOT NULL auto_increment,
targetEnteredDate varchar(8) NOT NULL default '0',
targetEmail varchar(50) default NULL,
targetFirstName text NOT NULL,
targetLastName text,
targetStreetAddress varchar(50) default NULL,
targetCity text,
targetState text,
targetZip varchar(11) default '0',
targetFirstTimeCustomer text NOT NULL,
targetBirthday1 varchar(4) default NULL,
targetBirthday1Name text,
targetBirthday2 varchar(4) default NULL,
targetBirthday2Name text,
targetBirthday3 varchar(4) default NULL,
targetBirthday3Name text,
targetBirthday4 varchar(4) default NULL,
targetBirthday4Name text NOT NULL,
targetBirthday5 varchar(4) default NULL,
targetBirthday5Name text NOT NULL,
targetAnniversary varchar(4) default NULL,
clientID int(11) NOT NULL default '0',
targetAdType text,
targetSpecialInterest1 text,
targetSpecialInterest2 text,
targetSpecialInterest3 text,
targetSpecialInterest4 text,
targetSpecialInterest5 text,
targetSpecialInterest6 text,
targetOther1 text,
targetOther2 text,
targetBillAmount int(11) default NULL,
targetServer varchar(25) default NULL,
targetFTEmailSent char(1) default NULL,
repID int(11) NOT NULL default '0',
PRIMARY KEY (targetID),
KEY clientID (clientID)
) TYPE=MyISAM;


TABLE client (
clientID int(11) NOT NULL auto_increment,
clientStatus text NOT NULL,
clientName varchar(50) NOT NULL default '',
clientEmail varchar(100) NOT NULL default '',
clientWeb varchar(50) NOT NULL default '',
clientCommentsClick varchar(100) NOT NULL default '',
clientPhone varchar(20) NOT NULL default '0',
clientSlogan text NOT NULL,
clientMaxEntries int(11) NOT NULL default '0',
clientEmailService text NOT NULL,
clientDirectMailService text NOT NULL,
clientSignatureFile varchar(50) default 'defaultSignature.gif',
clientSignatureFile2 varchar(50) default 'defaultSignature.gif',
clientSalutation text NOT NULL,
clientOwnerName text NOT NULL,
clientOwnerTitle text NOT NULL,
clientOwnerName2 text NOT NULL,
clientOwnerTitle2 text NOT NULL,
clientOfferExpire int(11) NOT NULL default '0',
clientFPBack text NOT NULL,
clientTileBack text,
clientBirthdayBorder text NOT NULL,
clientBirthdayImage text,
clientTopLeftBirthday text NOT NULL,
clientTopRightBirthday text NOT NULL,
clientBirthdayText text NOT NULL,
clientBirthdayTerms text NOT NULL,
clientFirstTimeCustomerText text NOT NULL,
clientFTterms text NOT NULL,
clientLogo text NOT NULL,
clientTopLeft text NOT NULL,
clientTopRight text NOT NULL,
clientAnniversaryBorder text NOT NULL,
clientAnniversaryImage text,
clientTopLeftAnniversary text NOT NULL,
clientTopRightAnniversary text NOT NULL,
clientAnniversaryText text NOT NULL,
clientAnniversaryTerms text NOT NULL,
clientSEFullTitle text NOT NULL,
clientSEFull text,
clientSpecialInterest1 text NOT NULL,
clientSpecialInterest1Logo text NOT NULL,
clientSpecialInterest1Border text NOT NULL,
clientSpecialInterest1UseFP text NOT NULL,
clientSpecialInterest1Text text NOT NULL,
clientSpecialInterest2 text NOT NULL,
clientSpecialInterest2Logo text NOT NULL,
clientSpecialInterest2Border text NOT NULL,
clientSpecialInterest2UseFP text NOT NULL,
clientSpecialInterest2Text text NOT NULL,
clientSpecialInterest3 text NOT NULL,
clientSpecialInterest3Logo text NOT NULL,
clientSpecialInterest3Border text NOT NULL,
clientSpecialInterest3UseFP text NOT NULL,
clientSpecialInterest3Text text NOT NULL,
clientSpecialInterest4 text NOT NULL,
clientSpecialInterest4Logo text NOT NULL,
clientSpecialInterest4Border text NOT NULL,
clientSpecialInterest4UseFP text NOT NULL,
clientSpecialInterest4Text text NOT NULL,
clientSpecialInterest5 text NOT NULL,
clientSpecialInterest5Logo text NOT NULL,
clientSpecialInterest5Border text NOT NULL,
clientSpecialInterest5UseFP text NOT NULL,
clientSpecialInterest5Text text NOT NULL,
clientSpecialInterest6 text NOT NULL,
clientSpecialInterest6Logo text NOT NULL,
clientSpecialInterest6Border text NOT NULL,
clientSpecialInterest6UseFP text NOT NULL,
clientSpecialInterest6Text text NOT NULL,
clientOther1 text NOT NULL,
clientOther2 text NOT NULL,
clientSpecialEvent1 text,
clientSpecialEvent1Logo text,
clientSpecialEvent1Border text,
clientSpecialEvent1UseFP text,
clientSpecialEvent1Text text NOT NULL,
clientSpecialEvent2 text,
clientSpecialEvent2Logo text,
clientSpecialEvent2Border text,
clientSpecialEvent2UseFP text,
clientSpecialEvent2Text text NOT NULL,
clientSpecialEvent3 text,
clientSpecialEvent3Logo text,
clientSpecialEvent3Border text,
clientSpecialEvent3UseFP text,
clientSpecialEvent3Text text NOT NULL,
clientSpecialEvent4 text,
clientSpecialEvent4Logo text,
clientSpecialEvent4Border text,
clientSpecialEvent4UseFP text,
clientSpecialEvent4Text text NOT NULL,
clientSpecialEvent5 text,
clientSpecialEvent5Logo text,
clientSpecialEvent5Border text,
clientSpecialEvent5UseFP text,
clientSpecialEvent5Text text NOT NULL,
clientSpecialEvent6 text,
clientSpecialEvent6Logo text,
clientSpecialEvent6Border text,
clientSpecialEvent6UseFP text,
clientSpecialEvent6Text text NOT NULL,
PRIMARY KEY (clientID),
UNIQUE KEY clientName (clientName)
) TYPE=MyISAM;

[dsumpter]

It almost seems as if the "$randsql_result" is ignoring the "WHERE clientID = $clientIDm". I just tried the modification that you said to try, and it still didn't work, and it seems like it just pics in some kind of somewhat random order. In english terms, this is what I am trying to do.

Pick a random target for each clientID. There should only be about 55 clientID's.

The way I am trying to do this is

1. get a list from the client table of all of the clientID's with their perspective clientName.
2. run a while loop that cycles through each clientID, then selects all of the targets that have that clientID, then pick a random target from the list of targets with that clientID, then goes to the next clientID.
3. it will eventually send an email to that target, but I know how to add that after I can make sure that it selects properly.

Let me know what you think... Thanks for your help!!! Dan

[Stefan]

have you tried to echo the query? that might help, as sometimes variables don't get passed through because you've done something wrong, or made a typo in the variable name. echo-ing the query often helps trace such problems

[dsumpter]

I tried that, and the query seems to work ok... the problem is that it seams to almost ignore the condition "WHERE clientID = $clientIDm". Any ideas?

[Stefan]

and you are sure $clientIDm has a value?

[dsumpter]

I decided to use a diferent script for it, and now it is working fine... Thanks for your help...