While loop within while loop problems
OK so i'm pulling the data for a job from a SQL Server 2008 through php to a html table. each job can/does have multiple tasks. i'm using 1 while loop ( know here after as the 'outer' while)and another while loop within one of the cells of the previous loop to display the tasks (know here after as the 'inner' while) to display each job i've found that it exits the 'inner' while loop but then does not trigger the next round of the 'outer' while loop. if i comment out the inner while it all works.
$sql = "SELECT * FROM [euroJobSrv].[dbo].[Job],[euroJobSrv].[dbo].[Site]";
$result = dbQuery($sql);
while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
$sql1 = "SELECT Task_Type FROM [euroJobSrv].[dbo].[JobTask] WHERE Task_Job_ID='".$row['Job_ID']."'";
$result1 = dbQuery($sql1);
$Site_Address = str_replace(',', ', <br>', $row['Site_Address']);
$days = $row['Job_Finish_Date'] - $row['Job_Start_Date'] + '1';
echo " <tr>
<th width=50> Job ID: </th>
<th width=50> Site Address:</th>
<th width=50> Start Date: </th>
<th width=50> Finish Date: </th>
<th width=50> Days: </th>
<th width=50> Overnight: </th>
<th width=50> Tasks: </th>
</tr>";
echo " <tr>
<td width=50>".$row['Job_ID']."</td>
<td width=50>".$Site_Address."</td>
<td width=50>".$row['Job_Start_Date']."</td>
<td width=50>".$row['Job_Finish_Date']."</td>
<td width=50>".$days."</td>
<td width=50>".$row['Job_Overnight']."</td>
<td width=50><ul>";
while ($row1 = sqlsrv_fetch_array($result1, SQLSRV_FETCH_ASSOC)){
echo "<li>".$row1['Task_Type']."</li>";}
echo" <ul></td>
<td><a href='include/job/modify.php?j=".$row['Job_ID']."'>modify</a>开发者_开发知识库</td>
</tr>";
}
I don't believe you can have multiple open result sets on the same connection with the sql server driver in PHP.
You have a couple options here.
Use two different database connections, one for the outer query and one for the inner query.
Just do one query, joining the two tables and ordering by
Job_ID
, then use logic in your code to determine when you change from one Job_ID to another.
Option 2 would probably offer the best performance as it avoids the need to do multiple queries.
You might want to look into the Multiple Active Results Sets (MARS) feature that started in SQL Server 2005. You can find info here on it:
http://msdn.microsoft.com/en-us/library/h32h3abf(v=VS.90).aspx
I have not tried this but it sounds like it addresses what you are trying to do.
I am not sure if it will work from PHP but it is worth trying.
精彩评论