nested mysql_fetch_array

[zulater]

My problem (highlighted by ------->  ) is that I know there's a way (or should be a way) around this but it's not working.

The way the code is shown works but it takes a long time to display because it's doing 800+ queries inside the loop.

 

What I have tried is putting the mysql query outside the loop but it will only compare the first value that passes through and change the "$crnt" value on the first item being compared.  I don't see why it would not reset each time it comes back to it without a new query.

 

What the code does at the end of the day is compare the tapes in the database to decide which "Line" is currently written on the tape.  The database holds archives as we re-use the tapes multiple times and we needed a way to see what is current when we search by "Tape Number".

 

Any help is appreciated.

//header row for display
echo "<table>";
echo "<td align=\"left\"><strong>Tape Number</td><td align=\"left\"><strong>Date</td><td align=\"left\"><strong>Client</td><td align=\"left\"><strong>System</td><td align=\"left\"><strong>Area</td><td align=\"left\"><strong>Line ID</td><td align=\"left\"><strong>Flow</td><td align=\"left\"><strong>Dataset</td><td align=\"left\"><strong>Notes</td><td align=\"left\"><strong>Locked</td><td align=\"left\"><strong>Current</td><tr>";

//value rows for display
while($row = mysql_fetch_array($result)) {
$lck="YES";
$crnt="";
$sqlquery = "Select `tapenumber` FROM `nuke_tapeindex`";
$sqlresult = mysql_query($sqlquery);
while($altrow = mysql_fetch_array($sqlresult)){
if($row[tapenum]==$altrow[tapenumber]){$lck="NO";}
}
------->  $query = "SELECT * FROM `nuke_tapetemp2`";
------->  $resultedited = mysql_query($query);
------->  while($othrow = mysql_fetch_array($resultedited)) {
------->   if($row[id]==$othrow[id]){$crnt="*";}
------->  }
echo "<tr align=\"left\" cellpadding=\"3\" cellspacing=\"0\" border=\"1\"> <td>". $row[tapenum] . "</td><td>" . $row[date] . "</td><td>" . $row[client] . "</td><td>" . $row[system] . "</td><td>" . $row[area] . "</td><td>" . $row[lineid] ."</td><td>" . $row[flow] ."</td><td>" . $row[dataset] ."</td><td>" . $row[comments] ."</td><td>" . $lck ."</td><td>" . $crnt ."</td></tr>";
}

echo "</table>";

[zulater]

ttt.

If I'm being unclear about something don't hesitate to ask.

[trq]

There is an article on our main site that covers sql joins. You should be able to achieve what your after using one query and a simple loop. That article ought get you started.

[zulater]

I'm not sure that is what I want.  It may be that I don't understand what I want though.

Here's what seems to be my problem and my understanding.

When I start the while statement pointed out "while($othrow = mysql_fetch_array($resultedited)" it will run through fine the first time.

However, the second time it will be skipped unless a new query on the database is made reinitalizing the $resultedited variable.

What I don't understand is why I need to run the sql query again.  Why doesn't $resultedited hold it's value and why doesn't the while loop run again without another query to define $resultedited?