Jump to content

problem with NESTED do{ }while loops


jwilliams

Recommended Posts

Using three queries, 'selectClients', 'selectProjects' and 'selectItems', I am building a detail set.  In the first step, you select a client, which then lists all projects for that client.
In the second step, I wish to have the user drill down the project to see all items associated with that project.  I can output the queries without any problem, the problem lies in where the output is going, and I think it has something to do with my looping.  When you drill down a project, it lists the proper items for that project, but it always lists them under the very first project, not the project you clicked on.  I will post my code in hopes someone can steer me in the correct direction...i've been racking my brain over this for days.  I'm only listing the problematic code, where you see all projects associated with a client and can drill down to see project details (items).

[code]
<table width="775" border="0" align="center" cellpadding="0" cellspacing="0">
    <tr class="columnHeaders">
      <td width="25">&nbsp;</td>
  <td width="250">project</td>
      <td width="300">description</td>
      <td width="75">hours</td>
      <td width="75">rate</td>
      <td width="150">total</td>
    </tr>
<?php
#alternate row colors
$current_row = 0;

do {
$current_row = 1 - $current_row;
?>
    <tr class="row<?php echo $current_row; ?>">
  <td><input name="items" type="checkbox" value=""></td>
      <td height="25"><a href="javascript:showItems('<?php echo $myID; ?>','<?php echo $row_selectProjects['projectID']; ?>');"><img src="images/carrot.gif" width="10" height="15" border="0"></a><?php echo $row_selectProjects['project_name']; ?></td>
      <td><?php echo $row_selectProjects['project_desc']; ?></td>
      <td><?php echo $row_selectProjects['total_hours_converted']; ?></td>
      <td><?php echo $row_selectProjects['billable_rate']; ?></td>
      <td>$<?php echo $row_selectProjects['total_billable']; ?></td>
    </tr>

<?php
$item_row = 0;
do {
$item_row = 1 - $item_row;
?>
<form name="ten" action="" method="post">
    <tr class="item_row<?php echo $item_row; ?>">
  <td><input name="test" type="text" value="hi" size="5" id="nine"></td>
      <td height="25"><img src="images/small_arrow.gif" width="24" height="12"><?php echo $row_selectItems['item_date']; ?></td>
      <td><?php echo $row_selectItems['item_details']; ?></td>
      <td><?php echo $row_selectItems['total_hours']; ?></td>
      <td><?php echo $row_selectItems['billable_rate']; ?></td>
  <td><?php echo $row_selectItems['total_billable']; ?></td>
     
    </tr>
<?php
}
while ($row_selectItems = mysql_fetch_assoc($selectItems));
?>
</form>
<?php }
while ($row_selectProjects = mysql_fetch_assoc($selectProjects)); ?>

</table>

[/code]


And here are some screenshots for your reference.

Here is a screen shot of Project 003 being drilled down to show ITEMS under Project 003

[img]http://www.drs-flash.com/drs/project003.jpg[/img]

And here is a screenshot of Project 001 being drilled down.  Notice that Project 001 items appear under Project 003 rather than Project 001.

[img]http://www.drs-flash.com/drs/project001.jpg[/img]


if you need to see more code please let me know, and thanks in advance for your help.
Link to comment
Share on other sites

oops, forgot to post, here they are:


[code]
<?php



mysql_select_db($database_drs_database, $drs_database);
$query_selectClients = "SELECT drs_time_tracker.clientID, drs_clients.client_name AS client_name FROM drs_time_tracker, drs_clients WHERE drs_time_tracker.clientID = drs_clients.clientID GROUP by clientID ORDER by client_name";
$selectClients = mysql_query($query_selectClients, $drs_database) or die(mysql_error());
$row_selectClients = mysql_fetch_assoc($selectClients);
$totalRows_selectClients = mysql_num_rows($selectClients);

$itemID = $_GET['itemID'];
$colname_selectItems = "-1";

if (isset($_GET['itemID'])) {
    $colname_selectItems = (get_magic_quotes_gpc()) ? $_GET['itemID'] : addslashes($_GET['itemID']);
$query_selectItems = "SELECT drs_time_tracker.itemID, drs_time_tracker.childID, drs_time_tracker.projectID, drs_time_tracker.item_date, drs_time_tracker.item_details,
drs_time_tracker.billable_rate,
TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(drs_time_tracker.out_time) - TIME_TO_SEC(drs_time_tracker.in_time)), '%H:%i') AS total_hours,
ROUND(((((TIME_TO_SEC(TIME_FORMAT(drs_time_tracker.out_time, '%H:%i')) - TIME_TO_SEC(TIME_FORMAT(drs_time_tracker.in_time, '%H:%i'))) / 60) / 60 ) * drs_time_tracker.billable_rate), 2) AS 'total_billable' 
FROM drs_time_tracker
WHERE drs_time_tracker.projectID = $itemID";
$selectItems = mysql_query($query_selectItems, $drs_database) or die(mysql_error());
$row_selectItems = mysql_fetch_assoc($selectItems);
$totalRows_selectItems = mysql_num_rows($selectItems);
}
#grab our variable from the url
#if(isset($_GET['recordID'])){
$myID = $_GET['recordID'];
$colname_selectProjects = "-1";
if (isset($_GET['recordID'])) {
    $colname_selectProjects = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
mysql_select_db($database_drs_database, $drs_database);
$query_selectProjects = "SELECT drs_time_tracker.itemID, drs_time_tracker.childID, drs_time_tracker.projectID, drs_projects.project_name AS project_name, drs_clients.client_name AS client_name, drs_time_tracker.item_date,
LEFT(drs_time_tracker.item_details, 25) AS short_details,
LEFT(drs_projects.project_desc, 40) AS project_desc,
drs_time_tracker.in_time,
drs_time_tracker.out_time,
TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(drs_time_tracker.out_time) - TIME_TO_SEC(drs_time_tracker.in_time)), '%H:%i') AS total_hours, 
drs_time_tracker.hours_logged,
drs_time_tracker.hours_converted,
drs_time_tracker.billable_rate,
drs_time_tracker.item_total,
drs_time_tracker.invoiced,
SUM(ROUND((((TIME_TO_SEC(drs_time_tracker.out_time) - TIME_TO_SEC(drs_time_tracker.in_time)) / 60) /60), 2)) AS 'total_hours_converted',
SUM(ROUND(((((TIME_TO_SEC(TIME_FORMAT(out_time, '%H:%i')) - TIME_TO_SEC(TIME_FORMAT(in_time, '%H:%i'))) / 60) / 60 ) * billable_rate), 2)) AS 'total_billable'
FROM drs_time_tracker, drs_projects, drs_clients
WHERE drs_time_tracker.projectID = drs_projects.projectID AND drs_time_tracker.clientID = drs_clients.clientID AND drs_time_tracker.clientID = $myID
GROUP BY drs_time_tracker.projectID
ORDER BY drs_time_tracker.item_date DESC";
$query_selectProjects . "TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(drs_time_tracker.out_time) - TIME_TO_SEC(drs_time_tracker.in_time)), '%H:%i') AS total_hours";
$query_selectProjects . "SET @total_hours = TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(drs_time_tracker.out_time) - TIME_TO_SEC(drs_time_tracker.in_time)), '%H:%i')";
$query_selectProjects . "SET @total_billable = SUM(ROUND(((((TIME_TO_SEC(TIME_FORMAT(out_time, '%H:%i')) - TIME_TO_SEC(TIME_FORMAT(in_time, '%H:%i'))) / 60) / 60 ) * billable_rate), 2)) AS 'total_billable";
$selectProjects = mysql_query($query_selectProjects, $drs_database) or die(mysql_error());
$row_selectProjects = mysql_fetch_assoc($selectProjects);
$totalRows_selectProjects = mysql_num_rows($selectProjects);
}
?>


[/code]
Link to comment
Share on other sites

no this is a private site on my local intranet.

I can post screenshots or answer any questions you may have. 
my current host isn't using mysql 4.0 so i can't upload online for a preview for you.

Will this deter us in troubleshooting?


Regarding your last post, both the selectItems and selectProjects query call to projectId in the where clause.

Link to comment
Share on other sites

according to this it is not
[code]WHERE drs_time_tracker.projectID = drs_projects.projectID AND drs_time_tracker.clientID = drs_clients.clientID AND drs_time_tracker.clientID = $myID[/code]

you are linking the two tables but you are not selecting the data by project id

maybe this
[code]WHERE drs_time_tracker.projectID = drs_projects.projectID AND drs_time_tracker.clientID = drs_clients.clientID AND drs_time_tracker.clientID = $myID AND drs_time_tracker.projectID = $itemID[/code]

Ray

Link to comment
Share on other sites

either way you query has no reference for selecting data based on projectID. I assume a client can have multiple projects.

[code]WHERE drs_time_tracker.projectID = drs_projects.projectID AND drs_time_tracker.clientID = drs_clients.clientID AND drs_time_tracker.clientID = $myID[/code]

If you say $myID is actually the project code then you are using the projectID to select the clientID.

Either way you need to select the clientID and the projectID and somewhere along the way you are losing one or the other you should be selecting both.

EDIT: OK I posted after you :)
Link to comment
Share on other sites

hi again, my second query (selectItems), which only gets called if $itemID exists in the URL, does reference the project id in the where close as noted below from selectItems query:

[code]WHERE drs_time_tracker.projectID = $itemID"; [/code]


The selectItems query is passed the projectID through the URL as referenced by itemID

All the data is pulling properly based on this, it's just putting it in the wrong row!
See images above.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.