jwilliams Posted October 17, 2006 Share Posted October 17, 2006 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"> </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. Quote Link to comment Share on other sites More sharing options...
craygo Posted October 17, 2006 Share Posted October 17, 2006 looks like it may be something to do with your query's can you post those.Ray Quote Link to comment Share on other sites More sharing options...
jwilliams Posted October 17, 2006 Author Share Posted October 17, 2006 sure thing: Quote Link to comment Share on other sites More sharing options...
jwilliams Posted October 17, 2006 Author Share Posted October 17, 2006 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 = $myIDGROUP 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] Quote Link to comment Share on other sites More sharing options...
craygo Posted October 17, 2006 Share Posted October 17, 2006 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 Quote Link to comment Share on other sites More sharing options...
craygo Posted October 17, 2006 Share Posted October 17, 2006 looks like you did not put the projectid in your where clause in the second query. Have you tried that Quote Link to comment Share on other sites More sharing options...
jwilliams Posted October 17, 2006 Author Share Posted October 17, 2006 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. Quote Link to comment Share on other sites More sharing options...
craygo Posted October 17, 2006 Share Posted October 17, 2006 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 idmaybe 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 Quote Link to comment Share on other sites More sharing options...
jwilliams Posted October 17, 2006 Author Share Posted October 17, 2006 sorry for my ambiguity, $myID is a reference to projectID Quote Link to comment Share on other sites More sharing options...
jwilliams Posted October 17, 2006 Author Share Posted October 17, 2006 belay my last, $myID references clientID...i will try your suggestion and post my results.thanks Quote Link to comment Share on other sites More sharing options...
craygo Posted October 17, 2006 Share Posted October 17, 2006 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 :) Quote Link to comment Share on other sites More sharing options...
jwilliams Posted October 17, 2006 Author Share Posted October 17, 2006 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 itemIDAll the data is pulling properly based on this, it's just putting it in the wrong row!See images above. Quote Link to comment Share on other sites More sharing options...
jwilliams Posted October 17, 2006 Author Share Posted October 17, 2006 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=15user: testpass: testThanks for all of your help! Quote Link to comment Share on other sites More sharing options...
jwilliams Posted October 18, 2006 Author Share Posted October 18, 2006 Hey Ray,Was wondering if you got the chance to peek at the link i provided? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.