Jump to content

Archived

This topic is now archived and is closed to further replies.

jwilliams

problem with NESTED do{ }while loops

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.

Share this post


Link to post
Share on other sites
looks like it may be something to do with your query's can you post those.

Ray

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
Sorry but is this a public site, I would like to see what is going on by clicking and seeing the links. Can you post the url if it is.

Ray

Share this post


Link to post
Share on other sites
looks like you did not put the projectid in your where clause in the second query. Have you tried that

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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 :)

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
Hi, I posted a preview online for you to see...

here is the link:  http://www.drs-flash.com/drs/intranet/add_invoices.php?recordID=8&itemID=15

user: test
pass: test

Thanks for all of your help!

Share this post


Link to post
Share on other sites

×

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.