New Query Inside of a looping query

aoyen81 · May 24, 2012

I am stuck here. I have a query that pulls a bunch of data from a table and groups some of it together by an ID. I run a while statment to display my data. I want to run another query inside of this statment to pull a name from the ID. Below is my code:

<?php

$startvar = '2012-05-24';

$connect = odbc_connect("***********", "********", "********");
$query = "SELECT AgentID, SUM(LogOnTime), SUM(BreakTime), SUM(MealTime), SUM(MeetingTime), SUM(OtherTime), SUM(TalkTime), SUM(WaitTime), SUM(WrapTime), SUM(HoldTime), SUM(DeadTime) FROM dbo.AgentSession WHERE SessionStart >= '$startvar' GROUP BY AgentID ORDER BY AgentID";
$result = odbc_exec($connect, $query);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>

<table width="90%" border="1" align="center" cellpadding="0">
  <tr>
    <td width="8.3%" align="center" valign="middle">Agent ID</td>
    <td width="8.3%" align="center" valign="middle">Agent Name</td>
    <td width="8.3%" align="center" valign="middle">LogOn</td>
    <td width="8.3%" align="center" valign="middle">Break</td>
    <td width="8.3%" align="center" valign="middle">Meal</td>
    <td width="8.3%" align="center" valign="middle">Meet</td>
    <td width="8.3%" align="center" valign="middle">Other</td>
    <td width="8.3%" align="center" valign="middle">Talk</td>
    <td width="8.3%" align="center" valign="middle">Wait</td>
    <td width="8.3%" align="center" valign="middle">Wrap</td>
    <td width="8.3%" align="center" valign="middle">Hold</td>
    <td width="8.3%" align="center" valign="middle">Dead</td>
  </tr>
<p> </p>
<?php while(odbc_fetch_row($result)){

$AgentID = odbc_result($result, 1);
$query_agent = "SELECT Lastname, Firstname FROM dbo.Agent WHERE AgentID = $AgentID";
$result_agent = odbc_exec($connect, $query_agent);

$AgentName = odbc_fetch_row($result_agent, 1);
$LogOn = round(((odbc_result($result, 2))/60/60), 2);
$Break = round(((odbc_result($result, 3))/60/60), 2);
$Meal = round(((odbc_result($result, 4))/60/60), 2);
$Meeting = round(((odbc_result($result, 5))/60/60), 2);
$Other = round(((odbc_result($result, 6))/60/60), 2);
$Talk = round(((odbc_result($result, 7))/60/60), 2);
$Wait = round(((odbc_result($result, )/60/60), 2);
$Wrap = round(((odbc_result($result, 9))/60/60), 2);
$Hold = round(((odbc_result($result, 10))/60/60), 2);
$Dead = round(((odbc_result($result, 11))/60/60), 2);
echo ("<tr>
<td align='center' valign='middle'>$AgentID</td>
<td align='center' valign='middle'>$AgentName</td>
<td align='center' valign='middle'>$LogOn</td>
<td align='center' valign='middle'>$Break</td>
<td align='center' valign='middle'>$Meal</td>
<td align='center' valign='middle'>$Meeting</td>
<td align='center' valign='middle'>$Other</td>
<td align='center' valign='middle'>$Talk</td>
<td align='center' valign='middle'>$Wait</td>
<td align='center' valign='middle'>$Wrap</td>
<td align='center' valign='middle'>$Hold</td>
<td align='center' valign='middle'>$Dead</td>
</tr>");

};

?>
</table>


</body>
</html>

<?php odbc_close($connect); ?>

All of the code executes, but the second query will only return 1 row, and will not display any "text" data. I am stumped and pretty green when it comes to SQL and PHP.

Thanks for any help!!

Andrew

Sign In

New Query Inside of a looping query

Recommended Posts

aoyen81

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information