A better way to query this?

[jonnyplow]

Hello all, this is my first post on phpfreaks.  
 
I'm trying to figure out what the better way to write this is.  I think that the way I have it is very slow and not the best way to.  Any idea?

 

 

function tot_time_worked_by_week(){
global $db;
$username = $_SESSION['username']; 
$sundayTime = $mondayTime = $tuesdayTime = $wednesdayTime = $thursdayTime = $fridayTime = $saturdayTime = 0;
$query = "SELECT user_id, time_zone FROM users WHERE user_name = '{$username}' ";
$resSet = $db->query($query,'assoc');
$user_id = $resSet[0]['user_id'];
$user_timezone = $resSet[0]['time_zone'];
$time = 0;
$query = "SELECT project_id FROM project WHERE user_id = '{$user_id}'";
$res = $db->query($query,'assoc');
if(!empty($res)):
foreach($res as $project):
$query = "SELECT track_id FROM project_track WHERE project_id = ".$db->prep($project['project_id']);
$resT = $db->query($query,'assoc');
if($resT != false){
	foreach($resT as $row):		
		$query = "SELECT time_start, time_end FROM track_time WHERE track_id = ".$db->prep($row['track_id'])." AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURRENT_DATE) AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 1";	
		$sunday = $db->query($query,'assoc');
		if(empty($sunday[0]['time_start'])){
		$sundayTime += 0;
		}else{
		$sundayTime += calculate_time_past($sunday[0]['time_start'],$sunday[0]['time_end'],'U');
		}
		$query = "SELECT time_start, time_end FROM track_time WHERE track_id = ".$db->prep($row['track_id'])." AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURRENT_DATE) AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 2";	
		$monday = $db->query($query,'assoc');
		if(empty($monday[0]['time_start'])){
		$mondayTime += 0;
		}else{
		$mondayTime += calculate_time_past($monday[0]['time_start'],$monday[0]['time_end'],'U');
		}
		$query = "SELECT time_start, time_end FROM track_time WHERE track_id = ".$db->prep($row['track_id'])." AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURRENT_DATE) AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 3";	
		$tuesday = $db->query($query,'assoc');
		if(empty($tuesday[0]['time_start'])){
		$tuesdayTime += 0;
		}else{
		$tuesdayTime += calculate_time_past($tuesday[0]['time_start'],$tuesday[0]['time_end'],'U');
		}
		$query = "SELECT time_start, time_end FROM track_time WHERE track_id = ".$db->prep($row['track_id'])." AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURRENT_DATE) AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 4";	
		$wednesday = $db->query($query,'assoc');
		if(empty($wednesday[0]['time_start'])){
		$wednesdayTime += 0;
		}else{
		$wednesdayTime += calculate_time_past($wednesday[0]['time_start'],$wednesday[0]['time_end'],'U');
		}
		$query = "SELECT time_start, time_end FROM track_time WHERE track_id = ".$db->prep($row['track_id'])." AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURRENT_DATE) AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 5";	
		$thursday = $db->query($query,'assoc');
		if(empty($thursday[0]['time_start'])){
		$thursdayTime += 0;
		}else{
		$thursdayTime += calculate_time_past($thursday[0]['time_start'],$thursday[0]['time_end'],'U');
		}
		$query = "SELECT time_start, time_end FROM track_time WHERE track_id = ".$db->prep($row['track_id'])." AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURRENT_DATE) AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 6";	
		$friday = $db->query($query,'assoc');
		if(empty($friday[0]['time_start'])){
		$fridayTime += 0;
		}else{
		$fridayTime += calculate_time_past($friday[0]['time_start'],$friday[0]['time_end'],'U');
		}
		$query = "SELECT time_start, time_end FROM track_time WHERE track_id = ".$db->prep($row['track_id'])." AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURRENT_DATE) AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 7";	
		$saturday = $db->query($query,'assoc');
		if(empty($saturday[0]['time_start'])){
		$saturdayTime += 0;
		}else{
		$saturdayTime += calculate_time_past($saturday[0]['time_start'],$saturday[0]['time_end'],'U');
		}		
	endforeach;
}
endforeach;
endif;
echo $sundayTime;
echo $mondayTime;
echo $tuesdayTime;
echo $wednesdayTime;
echo $thursdayTime;
echo $fridayTime;
echo $saturdayTime;
}

 

 

[lemmin]

The short answer is that you need to cut down on your queries.

 

It would be easier to answer the question knowing more about what you are trying to accomplish. Could you explain more about what your script does?

[Barand]

Use joins to get the data you need in a single query instead of running queries in loops

[jonnyplow]

I'm retrieving how much time was worked per day on projects for the user.

 

1. I'm getting the user.
2. I'm getting all projects associated with the user
3. Getting all recorded time track ids associated with each of the projects returned
4. Lastly getting and combining all start times and doing the same for stop times for those tracks found, then calculating the difference between them to get that day's total

[jonnyplow]

I'm not sure exactly how I would go about using a JOIN to do this.

[lemmin]

You should be able to get a lot of this information in just one query. This is an example of the tables "joined." (You can avoid JOIN when every row has a related row in every table. This is much more efficient than using the JOIN clause.)

SELECT
	u.user_id,
	u.time_zone,
	tt.time_start,
	tt.time_end
FROM
	users p,
	project p,
	project_track pt,
	track_time tt
WHERE
	p.user_id = u.user_id AND
	pt.project_id = p.project_id AND
	tt.track_id = pt.track_id AND
	u.user_name = $username

Try running that query and looking at the output. You can probably add more criteria to the WHERE clause to narrow down your results.

[Barand]

Better to use explicit joins thus separating the query structure from the selection criteria

SELECT u.user_id, u.time_zone t.time_start, t.time_end
FROM users u
    INNER JOIN project p ON u.user_id = p.user_id
    INNER JOIN project_track pt ON p.project_id = pt.project_id
    INNER JOIN track_time t ON pt.track_id = t.track_id
WHERE u.user_id = '{$user_id}'
    AND t.track_id = ".$db->prep($row['track_id'])." 
    AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURDATE()) 
    AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 1

[jonnyplow]

Barand, thank you so much!  Your suggestion helped me out!  Here's my final result.  It runs much faster now!

function tot_time_worked_by_week(){
global $db;
$username = $_SESSION['username']; 
      
$query = "SELECT table1.thetime AS day1, table2.thetime AS day2, table3.thetime AS day3, table4.thetime AS day4, table5.thetime AS day5, table6.thetime AS day6, table7.thetime AS day7 FROM (SELECT u.user_id AS user, (SUM(t.time_end) - SUM(t.time_start)) AS thetime
FROM users AS u
    INNER JOIN project AS p ON u.user_id = p.user_id
    INNER JOIN project_track AS pt ON p.project_id = pt.project_id
    INNER JOIN track_time AS t ON pt.track_id = t.track_id
WHERE u.user_name = '{$username}' 
    AND t.track_id = pt.track_id 
    AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURDATE()) 
    AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 1) as table1,
	  (SELECT u.user_id AS user, (SUM(t.time_end) - SUM(t.time_start)) AS thetime
FROM users AS u
    INNER JOIN project AS p ON u.user_id = p.user_id
    INNER JOIN project_track AS pt ON p.project_id = pt.project_id
    INNER JOIN track_time AS t ON pt.track_id = t.track_id
WHERE u.user_name = '{$username}' 
    AND t.track_id = pt.track_id 
    AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURDATE()) 
    AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 2) as table2,
	  (SELECT u.user_id AS user, (SUM(t.time_end) - SUM(t.time_start)) AS thetime
FROM users AS u
    INNER JOIN project AS p ON u.user_id = p.user_id
    INNER JOIN project_track AS pt ON p.project_id = pt.project_id
    INNER JOIN track_time AS t ON pt.track_id = t.track_id
WHERE u.user_name = '{$username}' 
    AND t.track_id = pt.track_id 
    AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURDATE()) 
    AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 3) as table3,
	  (SELECT u.user_id AS user, (SUM(t.time_end) - SUM(t.time_start)) AS thetime
FROM users AS u
    INNER JOIN project AS p ON u.user_id = p.user_id
    INNER JOIN project_track AS pt ON p.project_id = pt.project_id
    INNER JOIN track_time AS t ON pt.track_id = t.track_id
WHERE u.user_name = '{$username}' 
    AND t.track_id = pt.track_id 
    AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURDATE()) 
    AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 4) as table4,
	  (SELECT u.user_id AS user, (SUM(t.time_end) - SUM(t.time_start)) AS thetime
FROM users AS u
    INNER JOIN project AS p ON u.user_id = p.user_id
    INNER JOIN project_track AS pt ON p.project_id = pt.project_id
    INNER JOIN track_time AS t ON pt.track_id = t.track_id
WHERE u.user_name = '{$username}' 
    AND t.track_id = pt.track_id 
    AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURDATE()) 
    AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 5) as table5,
	  (SELECT u.user_id AS user, (SUM(t.time_end) - SUM(t.time_start)) AS thetime
FROM users AS u
    INNER JOIN project AS p ON u.user_id = p.user_id
    INNER JOIN project_track AS pt ON p.project_id = pt.project_id
    INNER JOIN track_time AS t ON pt.track_id = t.track_id
WHERE u.user_name = '{$username}' 
    AND t.track_id = pt.track_id 
    AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURDATE()) 
    AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 6) as table6,
	  (SELECT u.user_id AS user, (SUM(t.time_end) - SUM(t.time_start)) AS thetime
FROM users AS u
    INNER JOIN project AS p ON u.user_id = p.user_id
    INNER JOIN project_track AS pt ON p.project_id = pt.project_id
    INNER JOIN track_time AS t ON pt.track_id = t.track_id
WHERE u.user_name = '{$username}' 
    AND t.track_id = pt.track_id 
    AND YEARWEEK(FROM_UNIXTIME(time_start)) = YEARWEEK(CURDATE()) 
    AND DAYOFWEEK(FROM_UNIXTIME(time_start)) = 7) as table7
	";
$resT = $db->query($query,'assoc');

echo 'this.data.d1 = [
	[1, '.number_format(getTimeDifferenceInDetail($resT[0]['day1'],'',true),2,'.',',').'],
	[2, '.number_format(getTimeDifferenceInDetail($resT[0]['day2'],'',true),2,'.',',').'],
	[3, '.number_format(getTimeDifferenceInDetail($resT[0]['day3'],'',true),2,'.',',').'],
	[4, '.number_format(getTimeDifferenceInDetail($resT[0]['day4'],'',true),2,'.',',').'],
	[5, '.number_format(getTimeDifferenceInDetail($resT[0]['day5'],'',true),2,'.',',').'],
	[6, '.number_format(getTimeDifferenceInDetail($resT[0]['day6'],'',true),2,'.',',').'],
	[7, '.number_format(getTimeDifferenceInDetail($resT[0]['day7'],'',true),2,'.',',').']
	];';
}