LOOPing Problem

[inspireddesign]

Hello All!

 

The following code loops through the data and displays the data accordingly.  My problem is that the $listPR->HoursWorked; array repeats itself.  The foreach($listLocations as $listLoc) loops and displays the locations but the data array for HourWorked doesn't change per location.  I'm not sure that this is a MySQL question but here is the SQL Statement:  I can't figure out where I would "break" the loop and start it again to display the information correctly per location?  Can someone give me a hand on this one?  Thanks for the help in advance! 

 

<?php 

$locSql  = "SELECT * FROM Location AS loc WHERE companyID=". $_SESSION['company_id'] ." AND (" . $selLoc . ")";	
$listLocations = locData::find_by_sql($locSql);

	  $sql = "SELECT *,
			  DATE_FORMAT( Clockin, '%H:%i' ) AS FormattedTimeIn,				  
			  DATE_FORMAT( Clockout, '%H:%i' ) AS FormattedTimeOut,				  
			  TIMEDIFF( Clockout, Clockin ) AS HoursWorked
			  FROM time_clock AS tc
			  JOIN employees AS e ON e.Id = tc.Emp_id 
			  JOIN location AS loc ON loc.LocationID = tc.LocationID
			  JOIN employmentdetail AS ed ON ed.UserId = e.Id
			  WHERE tc.Clockin BETWEEN '".$Date1."' AND '".$Date2."' 
			   AND (" . $selLoc . ") AND ed.Active = 1 
			  ORDER BY e.LastName ASC";

	$listPayrollSum = mysql_query( $sql );	

?>

 

And here is the loop that I'm having trouble getting to not duplicate the data:

 

<?php
                                <!-- Loop thru and generate output -*** Based on Number of Employees and Number of Locations *** Some data may return NULL for location 
                                
                                
                                foreach Loop < tr > # of Employee create table cells  -->

							<?php while( $listPR = mysql_fetch_object( $listPayrollSum ) ) { $row_count++; ?>
                              
                                  <tr class="grid-content"<?php if ($row_count%2) { ?> bgcolor="#ebebeb" <?php } ?> >
                                        
                                            <td style="padding:5px;" ><?php echo $listPR->LastName. ", " .$listPR->FirstName ?></td>
                                            
                                                    <!-- foreach Loop < # of Locations > -->
                                            
					                            <?php foreach($listLocations as $listLoc){ ?>
                                                    

                                                  <td style="padding:5px; border-left:#333 1px solid;" align="center"> <?php echo $listPR->HoursWorked;?> </td>
                                                  
                                                  <td style="padding:5px;" align="center">$2,400.80</td>
                                                  <td style="padding:5px;" align="center">$1,000</td>

									        <?php  } ?>
                                                    
                                                   <!-- End foreach Loop < Locations > -->
                                                
                                        <!-- Total -->
                                             
                                            <td align="center" style="border-left:#333 1px solid;"> </td>
                                            <td align="center" style="padding:5px; border-left:#333 1px solid;" >2000</td>
                                            <td align="center" style="padding:5px;" >$25,000.00</td>
                                            <td align="center" style="padding:5px;" >$6,000,000.00</td>
                                  </tr>

						<?php  } ?>
                            
                                <!-- End Loop for Employee -->


?>

 

[inspireddesign]

Not sure if I explained this well enough to get an answer to my problem.  Attached is an image of the data output in the table code above.  Please let me know if you need more of the code to understand what I'm trying to accomplish.  The problem is that the Locations information is getting duplicated in each location group.  So in the attached data dump the information is getting duplicated for each location. The Names are outputting correctly but the data going left to right is not.

 

[attachment deleted by admin]

[inspireddesign]

Okay, I think I figured out how I can make this work but need a little help.  I'm looping through the first array then getting the data needed based on the LocationID.  When I echo the SQL statement it returns correctly in each cell but when I comment out the SQL echo the $listHours['HoursWorked'] gets repeated... meaning the same data is being displayed.

 

How can I keep that from happening?  How can separate the array so that each time the loop it displays the correct information is shown in each cell?  Here's what I have now:  Thanks!

 

<?php
							<?php while( $listPR = mysql_fetch_object( $listPayrollSum ) ) { $row_count++; ?>
                              
                                  <tr class="grid-content"<?php if ($row_count%2) { ?> bgcolor="#ebebeb" <?php } ?> >
                                        
                                            <td style="padding:5px;" ><?php echo $listPR->LastName. ", " .$listPR->FirstName ?></td>
                                            
                                                    <!-- foreach Loop < # of Locations > -->

					                            <?php foreach($listLocations as $listLoc){ ?>
                                                                                                        
                                                  <td style="padding:5px; border-left:#333 1px solid;" align="center"> 

													  <?php 

													$locSql  = "SELECT *,  
												    			TIMEDIFF( Clockout, Clockin ) AS HoursWorked
																FROM time_clock AS tc
																WHERE tc.Clockin BETWEEN '".$Date1."' AND '".$Date2."' 
																AND tc.LocationID = " . $listLoc->LocationID . "
																AND tc.Emp_Id = " . $listPR->Id . " 
																GROUP BY tc.Emp_Id";	

													$Hours = mysql_query( $sql );
													$listHours = mysql_fetch_assoc( $Hours );

												   
												   echo $locSql;
													  
												   echo $listHours['HoursWorked'];
													  
													  ?> 
                                                   
                                                  </td>
                                                            
                                                  <td style="padding:5px;" align="center">$2,400.80</td>
                                                  <td style="padding:5px;" align="center">$1,000</td>

											<?php  } ?>




?>