CODEWITHSUNDEEP
phpmysql
Mike
Mike

Reputation: 2751

Advice on SELECT statement and query

I need to either have a masive select statement or multiple queries. I need to break down data into specific timeframes during the day. This one works great for the first interval, but after that I'm stumped. If I have multiple queries, I'm having trouble with my "mysql_fetch_array", as to the syntax (using something like 'else' and going through them).

SELECT U.user_name,ROUND(SUM((TA.task_average*TC.completed)/60),2) AS equiv1, S.submit_date,
    SUM(TC.completed) AS ttasks1,
    FROM `summary` S
    JOIN users U ON U.user_id = S.user_id
    JOIN tasks TA ON TA.task_id = S.task_id
    JOIN tcompleted TC ON TC.tcompleted_id = S.tcompleted_id
    JOIN minutes M ON M.minutes_id = S.minutes_id
    WHERE DATE(submit_date) = curdate( )
      AND TIME(submit_date) BETWEEN '06:00:00' and '07:59:59'       
      GROUP BY U.user_name
    LIMIT 0 , 30

My fetch array ( I would need to have a bunch more, but how to I combine them?) 

<?php
while($rows=mysql_fetch_array($result1)){

?>

Upvotes: 2

Views: 153

Answers (2)

caitriona
caitriona

Reputation: 9131

ok, given that you want the data across regular 2 hourly intervals, you could try something like this:

SELECT FLOOR(hour(S.submit_date)/2)*2, U.user_name,ROUND(SUM((TA.task_average*TC.completed)/60),2) AS equiv1, S.submit_date
    SUM(TC.completed) AS ttasks1,
    FROM `summary` S
    JOIN users U ON U.user_id = S.user_id
    JOIN tasks TA ON TA.task_id = S.task_id
    JOIN tcompleted TC ON TC.tcompleted_id = S.tcompleted_id
    JOIN minutes M ON M.minutes_id = S.minutes_id
    WHERE DATE(submit_date) = curdate( )
      GROUP BY U.user_name, FLOOR(hour(S.submit_date)/2)
    LIMIT 0 , 30

where FLOOR(hour(S.submit_date)/2)*2 will map each hour to the first (even) hour of every 2 and you can group by this value. ie.

0, 1 -> 0
2, 3 -> 2
4, 5 -> 4
etc...

update with php included:

some notes:

  • i've used mysqli
  • i've left joined the original query with an hours derived table to ensure there are no 'gaps' in the time intervals (assumed 6:00 - 20:00)
  • i've ordered by user, so we as we loop through the results we can print table cells for a given user and then print a new table row when the user changes.

here's the code:

echo '<table border=1><tr><td></td>';
for ($i=6; $i<=18; $i=$i+2) {
    echo '<td colspan=2>'.$i.' - '.($i+2).'</td>';
}

$mysqli = new mysqli('MY_HOST', 'MY_USER', 'MY_PASSWORD', 'MY_DATABASE');

$sql = "
SELECT user_name, IFNULL(equiv1,0) AS equiv1, IFNULL(ttasks1,0) AS ttasks1, hour
FROM
(
    SELECT 6 AS hour
    UNION SELECT 8
    UNION SELECT 10
    UNION SELECT 12
    UNION SELECT 14
    UNION SELECT 16
    UNION SELECT 18
) hours LEFT JOIN

(
SELECT FLOOR(hour(S.submit_date)/2)*2 as task_hour, U.user_name, ROUND(SUM((TA.task_average*TC.completed)/60),2) AS equiv1, S.submit_date
    SUM(TC.completed) AS ttasks1
    FROM `summary` S
    JOIN users U ON U.user_id = S.user_id
    JOIN tasks TA ON TA.task_id = S.task_id
    JOIN tcompleted TC ON TC.tcompleted_id = S.tcompleted_id
    JOIN minutes M ON M.minutes_id = S.minutes_id
    WHERE DATE(submit_date) = curdate( )
      GROUP BY U.user_name, FLOOR(hour(S.submit_date)/2)
    LIMIT 0 , 30
) task_summary
ON hours.hour = task_summary.task_hour
ORDER BY user_name, hour
";

$result = $mysqli->query($sql);

$user_name = '';
while ($row = $result->fetch_assoc()) {
    if ($user_name <> $row['user_name']){
        echo '</tr><tr><td>'.$row['user_name'].'</td>'; //start a new row if user changes
        $user_name = $row['user_name']; //update user variable for checking on next iteration
    }
    echo '<td>'.$row['equiv1'].'</td>';
    echo '<td>'.$row['ttasks1'].'</td>';
}

echo '</tr><table>';

Upvotes: 3

Abhishek Saha
Abhishek Saha

Reputation: 2564

This is in regard to your question "how to combine the fetch array". Why not have an array to store all the values coming from your fetch array like the one below.

<?php
$customArray = array();
while($rows=mysql_fetch_array($result1)){

    $customArray['field1'] = $row['field1'];
    $customArray['field2'] = $row['field2'];

}

//another fetch array

while($rows=mysql_fetch_array($result2)){

    $customArray['field1'] = $row['field1'];
    $customArray['field2'] = $row['field2'];

}

//now $customArray will have all the required values you need. 
//This is not a great option as it is making the logic expensive. 
//Are you going to use this in a cron job ?
?>

Upvotes: 1

Related Questions