Malfunctioning While statement...

MySQL database
#1

I am attempting to write a code that will update two columns of my table for every entry in the table. The odd thing about it is I have to use other points of data from every entry to find out how to update each entry. I have tried to accomplish this by embedding one While loop inside another. As a whole it accomplishes the task, but only for the first entry in my table. Here is the code:

$result = mysql_query("SELECT * FROM subjects ");

While ($row = mysql_fetch_array($result)) {
$subject = $row['name'];
$a = $row['a'];
$b = $row['b'];
$c = $row['c'];
$d = $row['d'];
$e = $row['e'];
$f = $row['f'];
$g = $row['g'];
$h = $row['h'];
$i = $row['i'];
While ($row = mysql_fetch_array($result)) {
$bestpercent == 0;
$distance = sqrt(pow($a - $row['a'], 2) + pow($b - $row['b'], 2) + pow($c - $row['c'], 2) + pow($d - $row['d'], 2) + pow($e - $row['e'], 2) + pow($f - $row['f'], 2) + pow($g - $row['g'], 2) + pow($h - $row['h'], 2) + pow($i - $row['i'], 2));   
$compatibility = round(100 * (1 - pow($distance / $maxdistance, 2)), 0);
If ( $bestpercent < $compatibility ) {
$bestpercent = $compatibility;
$bestmatch = $row['name'];
}
}
mysql_query("UPDATE subjects SET bestname='$bestmatch' WHERE name='$subject' ") or die(mysql_error());
mysql_query("UPDATE subjects SET bestpercent='$bestpercent' WHERE name='$subject' ") or die(mysql_error());
}

Like I said, it comes up with the correct results, but only for the first entry in my table. Any help would by greatly appreciated, thanks!

#2

you placed a while loop inside while loop with same $row = mysql_fetch_assoc($result)…

so, this is reason for the unexpected results out of the total looping

pls check this…

i guess this should be the while loop structure
[php]
while ($row = mysql_fetch_array($result))
{
$subject = $row[‘name’];
$a = $row[‘a’];
$b = $row[‘b’];
$c = $row[‘c’];
$d = $row[‘d’];
$e = $row[‘e’];
$f = $row[‘f’];
$g = $row[‘g’];
$h = $row[‘h’];
$i = $row[‘i’];

$bestpercent == 0;
$distance = sqrt(pow($a - $row['a'], 2) + pow($b - $row['b'], 2) + pow($c - $row['c'], 2) + pow($d - $row['d'], 2) + pow($e - $row['e'], 2) + pow($f - $row['f'], 2) + pow($g - $row['g'], 2) + pow($h - $row['h'], 2) + pow($i - $row['i'], 2));   
$compatibility = round(100 * (1 - pow($distance / $maxdistance, 2)), 0);
If ( $bestpercent < $compatibility ) {
$bestpercent = $compatibility;
$bestmatch = $row['name'];

}
[/php]

#3

No, that doesn’t work because there has to be a while loop inside the first while loop. Your first comment though made me try something, and it actually worked! (With a bit of tweaking in other parts of the code) In case you’re curious, here’s the final code:

$result = mysql_query("SELECT * FROM subjects ") or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
$subject = $row['name'];
$a = $row['a'];
$b = $row['b'];
$c = $row['c'];
$d = $row['d'];
$e = $row['e'];
$f = $row['f'];
$g = $row['g'];
$h = $row['h'];
$i = $row['i'];
$distance = 0;
$bestpercent = 0;
echo "<u>".$subject."</u></br>";
$result1 = mysql_query("SELECT * FROM subjects WHERE name != '$subject' ") or die(mysql_error());
while ($row1 = mysql_fetch_array($result1)) {	
$matchsubject = $row1['name'];
$a1 = $row1['a'];
$b1 = $row1['b'];
$c1 = $row1['c'];
$d1 = $row1['d'];
$e1 = $row1['e'];
$f1 = $row1['f'];
$g1 = $row1['g'];
$h1 = $row1['h'];
$i1 = $row1['i'];
$distance = sqrt(pow($a - $a1, 2) + pow($b - $b1, 2) + pow($c - $c1, 2) + pow($d - $d1, 2) + pow($e - $e1, 2) + pow($f - $f1, 2) + pow($g - $g1, 2) + pow($h - $h1, 2) + pow($i - $i1, 2));   
$compatibility = round(100 * (1 - pow($distance / $maxdistance, 2)), 0);
echo $matchsubject." - ".$compatibility ;
If ( $compatibility > $bestpercent ) {
$bestpercent = $compatibility;
$bestmatch = $matchsubject;
}
echo " // ".$bestmatch." - ".$bestpercent."</br>";
}
echo "</br>";
mysql_query("UPDATE subjects SET bestname='$bestmatch' WHERE name='$subject' ") or die(mysql_error());
mysql_query("UPDATE subjects SET bestpercent='$bestpercent' WHERE name='$subject' ") or die(mysql_error());
}

Thanks!

#4

oh there is another resultset to be opened inside the main loop which was missing earlier :). glad i could help!

Sponsor our Newsletter | Privacy Policy | Terms of Service