Jump to content


Photo

problem with NESTED do{ }while loops


  • Please log in to reply
13 replies to this topic

#1 jwilliams

jwilliams
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 17 October 2006 - 01:34 PM

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).

 <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>



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

Posted Image

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

Posted Image


if you need to see more code please let me know, and thanks in advance for your help.

#2 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 17 October 2006 - 01:47 PM

looks like it may be something to do with your query's can you post those.

Ray

#3 jwilliams

jwilliams
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 17 October 2006 - 01:55 PM

sure thing:




#4 jwilliams

jwilliams
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 17 October 2006 - 01:57 PM

oops, forgot to post, here they are:


<?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);
}
?>




#5 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 17 October 2006 - 02:00 PM

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

#6 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 17 October 2006 - 02:05 PM

looks like you did not put the projectid in your where clause in the second query. Have you tried that

#7 jwilliams

jwilliams
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 17 October 2006 - 02:07 PM

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.



#8 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 17 October 2006 - 02:17 PM

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

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

maybe this
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

Ray



#9 jwilliams

jwilliams
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 17 October 2006 - 02:22 PM

sorry for my ambiguity, $myID is a reference to projectID

#10 jwilliams

jwilliams
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 17 October 2006 - 02:33 PM

belay my last, $myID references clientID...i will try your suggestion and post my results.

thanks

#11 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 17 October 2006 - 02:34 PM

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

WHERE drs_time_tracker.projectID = drs_projects.projectID AND drs_time_tracker.clientID = drs_clients.clientID AND drs_time_tracker.clientID = $myID

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 :)

#12 jwilliams

jwilliams
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 17 October 2006 - 03:03 PM

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:

WHERE drs_time_tracker.projectID = $itemID";


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.

#13 jwilliams

jwilliams
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 17 October 2006 - 04:04 PM

Hi, I posted a preview online for you to see...

here is the link:  http://www.drs-flash...dID=8&itemID=15

user: test
pass: test

Thanks for all of your help!



#14 jwilliams

jwilliams
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 18 October 2006 - 01:46 AM

Hey Ray,

Was wondering if you got the chance to peek at the link i provided?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users