[Michaelmoore]

I have been trying for days now to construct a script to randomly select members of my website to take part in a competition. They register their interest to give me a tag for the search.

This is what happens:
Members register their interest and that updates the db race_member column from 0 to 1
The script, when invoked, pulls ALL the flagged entries at random and selects team members according to the limit ($lim)
It then writes the names and their info to the race table and changes the tag back to 0 so they don't get listed twice. At least, that's the theory!

Here's a snippet of what I've got so far:

PHP Code:

$sql=("SELECT * FROM farmlist WHERE race_member = 1 ORDER BY RAND()");
$result = mysql_query($sql,$db);
$row=mysql_fetch_array($result);
while($row=mysql_fetch_array($result))
{
$team=("SELECT * FROM list WHERE race_member = 1 ORDER BY RAND() LIMIT $lim");
$res = mysql_query($team,$db);
$r=mysql_fetch_array($res);
while($r=mysql_fetch_array($res))
{
$user_id=$r["farm_id"];
get_magic_quotes_gpc();
$name=addslashes($r["farm_name"]);
echo $r["farm_name"]; echo ' | ' . $r["farm_id"] . ' | ' . $i . '<br>';
mysql_query("INSERT INTO race (farm_id, farm_name, team_no) VALUES ('$user_id', '$name','$i')");
mysql_query("UPDATE list SET race_member = 0 WHERE farm_id=$user_id");
}
$i++;
}
} 

What actually happens is that not all the names are pulled on the first call, and I can't find a reason for this. Then when the second part runs, it tends to run one number less than the limit, but not all the time! So I end up with teams of 3 or 4 when they should all be 4.

Am I going about this the wrong way, or is there something I've missed?

[ruteckycs]

Make sure you WHERE clause is being met.

[Michaelmoore]

Originally Posted by ruteckycs

Make sure you WHERE clause is being met.

The WHERE clause isn't giving me any problems, the count of the rows is always spot on.

I'm wondering actually if if has to do with the new version 5. I do a num_rows and end up with 15, do an echo and get 14. Run the allocation script and I end up with 1 record every time, and no way will it allocate that one row!

I have been refining the script today, trying to work out why it won't show the one record and every time I get the same result. Even when I just do a straight-forward query, there's always one record missing from the list.

I use a simple form input to generate the var for limit and all the actions, which are separated for so I can see what is happening. There are several commented lines where I have been trying different approaches to this problem.

Here is the script that I have got to now, maybe it will give some idea of what is happening:

PHP Code:

$a=1;

$sql=("SELECT * FROM farmlist WHERE race_member = 1");

$res=mysql_query($sql) or die(mysql_error());

$num_rows = mysql_num_rows($res);

echo 'Number of Participants: ';

echo "$num_rows Players<br>";

// Who are they?;

// Result = 15 records, so the following should show 15 records:;

$row=mysql_fetch_array($res);

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

$nme=$row["farm_name"];

$id=$row["farm_id"];

echo $a . ': ' . $nme;

echo ' | ';

$a++;

} 

Result = 14 records, one short every time, So I call it again when the form returns the next part of the script:

PHP Code:

$limit = $_POST["limit"];

$num=($_POST["num"]);

$lim = ($limit+1);

$a=1; 

$sql=("SELECT * FROM farmlist WHERE race_member = 1 LIMIT 0,$num");

$result = mysql_query($sql,$db);

$row=mysql_fetch_array($result);

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

$nme=$row["farm_name"];

$id=$row["farm_id"];

echo $a . ': ' . $nme;

echo '<BR>';

$a++;

} 

Call to show results: Only 14 Records show up. Then the script randomizes and copies them to the race db:

PHP Code:

$i=1;

$sql=("SELECT * FROM farmlist WHERE race_member = 1 LIMIT $num");

$result = mysql_query($sql,$db);

$row=mysql_fetch_array($result);

while($row=mysql_fetch_array($result))

{

$team=("SELECT * FROM farmlist WHERE race_member = 1 ORDER BY RAND() LIMIT 0,$lim");

$res = mysql_query($team,$db);

$r=mysql_fetch_array($res);

while($r=mysql_fetch_array($res))

{

$user_id=$r["farm_id"];

get_magic_quotes_gpc();

$name=addslashes($r["farm_name"]);

echo $r["farm_name"]; echo ' | ' . $r["farm_id"] . ' | ' . $i . '<br>';

mysql_query("INSERT INTO team_1 (farm_id, farm_name, team_no) VALUES ('$user_id', '$name','$i')");

mysql_query("UPDATE farmlist SET race_member = 0 WHERE farm_id=$user_id");

}

$i++;

} 

Move the results into the race table: 14 Records of the 15 copy across to the table. Then I display the results of that table:

PHP Code:

$num=$_POST["num"];

echo '<div align=left>';

//echo 'Team: 1 = ';

$team = ("SELECT * FROM team_1 ORDER BY team_no");

$result = mysql_query($team,$db);

$row=mysql_fetch_array($result);

while($row=mysql_fetch_array($result))



{

$nme=$row["farm_name"];

$id=$row["farm_id"];

$tn=$row["team_no"];

echo 'Team ' . $tn;

echo ': ' . $nme . '<br>';

}

echo '</div>'; 

and it only shows 13! Using phpMyAdmin, I can see that 14 records were copied across, and that one record remains in the main db as unchanged, so why can't I get them to show up properly?
Never had a problem like this before with MySQL, and although I was working with version 4 when I started this project, I have since gone to version 5.1, since the host I use now uses that version.

It has me completely confused at the moment, nothing I do changes it to show that missing record. I even tried -1 on the limit, but that doesn't work any more, you can see manipulations of the limit and number vars to see if that would make a difference, but nothing does.

Any ideas?

[wirehopper]

PHP Code:

$row=mysql_fetch_array($result);   /* The results of this fetch are being discarded/overwritten */

while($row=mysql_fetch_array($result)) 

[ruteckycs]

Good find wirehopper!

[Michaelmoore]

Thanks guys! There had to be a reason, I just couldn't see it! After commenting the line out, it worked fine! thanks again

[Michaelmoore]

BTW, is that different in V5, because I have always used that in V4 without any conflicts. Just wondering because I can go through all the other scripts on the site removing it if they have!

[wirehopper]

You can use grep to check the other scripts.

grep -B2 -A2 -r "mysql_fetch_assoc" *.php

grep MAN Page

This should find every instance of mysql_fetch_assoc, and print out two lines before and two lines after. Might save you some time.

Not tested.

[Michaelmoore]

Thanks again wirehopper, it's great to have helpful people around!

I only started this as a pass-time, using the basic skills I have picked up along the way, but now it's becoming a full time job! Just one more quick question, if you don't mind, it might save me heaps of time:

The next step of the process is to combine the scores of each member into a single total. I haven't even started on this one yet, so it's a blank canvas and open to suggestions. There will be 3 or 4 members of each team, each submitting scores at whatever time suits them to the main db. This isn't a problem, they do it all the time. The script is going to have an extra call so that the updates are copied onto the race db as well, again, no major. The problem area is in consolidating these scores for each team, on the fly, so that people can check their progress as they play. I know it will have to be an array sequence operation, and this is where my skill falls short of the mark.

Any ideas as to how to work out Players Present Score minus baseline score to give true nett, then add this to the team total from all the players, record the data on the db and then display it beside the team on the page?

I know I can do it, but you probably can think of a much quicker way to get the job done! (How did I get myself into this one? lol)

It's all for fun and no prizes, but I came up with the idea, now I have to code it!

[Jcbones]

I would hold the scores in an array. Something like:

PHP Code:

while($r = mysql_fetch_assoc($result)) {
$teamscore[$r['team']][$r['player']] += $r['score'];
$baseline = $r['baseline'];
}

foreach($teamscore as $team =>$pArray) {
  $teamTotal = 0;
 foreach($pArray as $player => $score) {
  $pScore = $score - $baseline;
   echo $player .' has a score of '. $pScore;
  $teamTotal += $pScore;
 }
  echo 'Team score: ' . $teamTotal;
}