I need to select total time for every jobID both in time and multitime tables.
I was using this piece of code:
$sel = mysql_query("select distinct jobID from `time` where staffID = '8' && (`start` > '".$_POST['dateIn']." 00:00:01' && `finish` < '".$_POST['dateOut']." 23:59:59')") or die (mysql_error());
while ($row = mysql_fetch_array($sel)) {
$sel2 = mysql_query("select sum(total) AS totalhrs from `time` where `staffID` = '8' && `jobID` = '".$row['jobID']."' && (`start` > '".$_POST['dateIn']." 00:00:01' && `finish` < '".$_POST['dateOut']." 23:59:59')") or die (mysql_error());
$row2 = mysql_fetch_array($sel2);
$mainhours = $row2['totalhrs'];
$sel2 = mysql_query("select sum(total) AS totalhrs from `multitime` where `staffID` = '8' && `jobID` = '".$row['jobID']."' && `start` > '".$_POST['dateIn']." 00:00:01' && `finish` < '".$_POST['dateOut']." 23:59:59'") or die (mysql_error());
$row2 = mysql_fetch_array($sel2);
$subhours = $row2['totalhrs'];
which still didn’t do its job, because if a task was stored only in multitime table (each time can have several multitimes), this was ommited, so the query didn’t return true data.
I guess some sort of JOIN is required here, but I am completely stuck, cannot even make the query work, contanstly making syntax errors.
Can anyone help me out here or guide me?
Any help much much appreciated.
So, I have written these two queries for selecting all jobIDs from both tables, this certainly works:
$sel2 = mysql_query("select DISTINCT jobID from `time` where `staffID` = '8' && (`start` > '".$_POST['dateIn']." 00:00:01' && `finish` < '".$_POST['dateOut']." 23:59:59')") or die (mysql_error());
while($row2 = mysql_fetch_array($sel2)) {
echo $row2['jobID'] . '<br>';
}
$sel2 = mysql_query("select DISTINCT jobID from `multitime` where `staffID` = '8' && `start` > '".$_POST['dateIn']." 00:00:01' && `finish` < '".$_POST['dateOut']." 23:59:59'") or die (mysql_error());
while($row2 = mysql_fetch_array($sel2)) {
echo $row2['jobID'] . '<br>';
}
Output:
main:
1652
1629
resumed:
1652
1620
Anyways, now, I don’t know how can I select (As a sum()) the column called ‘total’ (as for total time per each task), not to mention that I have no idea how to pull the data merged together
first, could you please run this query outside of php and show the results…
SELECT jobID
, NULL AS TaskID
, total
FROM `time`
WHERE staffID = 8
AND `start` >= '2010-02-02 20:20:00'
AND `finish` < '2010-02-02 20:49:37'
UNION ALL
SELECT jobID
, TaskID
, total
FROM `multitime`
WHERE staffID = 8
AND `start` >= '2010-02-04 03:00:00'
AND `finish` < '2010-02-04 03:19:37'
ORDER
BY jobID
, TaskID
I run the query you gave me directly in phpmyadmin and it didn’t execute it:
SELECT jobID, NULL AS TaskID, total
FROM `time`
WHERE staffID =8
AND `start` >= '2010-02-02 20:20:00'
AND `finish` < '2010-02-02 20:49:37'
UNION ALL
SELECT jobID, TaskID, total
FROM `multitime`
WHERE staffID =8
AND `start` >= '2010-02-04 03:00:00'
AND `finish` < '2010-02-04 03:19:37'
ORDER
BY jobID, TaskID
LIMIT 0 , 30
MySQL said: Documentation
#1064 - Something is wrong in your syntax obok 'UNION ALL
SELECT jobID
, TaskID
, total
FROM `mu' w linii 8
You need to enclose the two sub-queries in brackets:
From the MySQL Manual:
To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:
(SELECT jobID
, NULL AS TaskID
, total
FROM `time`
WHERE staffID = 8
AND `start` >= '2010-02-02 20:20:00'
AND `finish` < '2010-02-02 20:49:37')
UNION ALL
(SELECT jobID
, TaskID
, total
FROM `multitime`
WHERE staffID = 8
AND `start` >= '2010-02-04 03:00:00'
AND `finish` < '2010-02-04 03:19:37')
ORDER
BY jobID
, TaskID
there’s another reason i never use the “php” tags on this forum – you can’t really see the stupid green parentheses
however, with “code” tags, especially if i apply the colour myself, they becomes visible –
[COLOR="Red"][B]([/B][/COLOR] SELECT jobID
, NULL AS TaskID
, total
FROM `time`
WHERE staffID = 8
AND `start` >= '2010-02-02 20:20:00'
AND `finish` < '2010-02-02 20:49:37' [COLOR="Red"][B])[/B][/COLOR]
UNION ALL
[COLOR="Red"][B]([/B][/COLOR] SELECT jobID
, TaskID
, total
FROM `multitime`
WHERE staffID = 8
AND `start` >= '2010-02-04 03:00:00'
AND `finish` < '2010-02-04 03:19:37' [COLOR="Red"][B])[/B][/COLOR]
ORDER
BY jobID
, TaskID
The initial tests I performed via my local host, where the version of mysql is pretty much outdated and the query still cuses an error:
VERSION()
3.23.58-max-debug
But when I run the query on the real thing, it did execute however it did not print anything for me to see, so I changed the dates and hours to include one full day, and then it spat out something:
if you are still running 3.23 on your local host, you really should upgrade
now that you have seen the detail rows produced by the UNION, you can proceed to modify the query to produce the SUMs you want…
I need to select total time for every jobID both in time and multitime tables.
SELECT jobID
, SUM(total) AS total_total
FROM ( SELECT jobID
, total
FROM `time`
WHERE staffID = 8
AND `start` >= '2010-02-02 20:20:00'
AND `finish` < '2010-02-02 20:49:37'
UNION ALL
SELECT jobID
, total
FROM `multitime`
WHERE staffID = 8
AND `start` >= '2010-02-04 03:00:00'
AND `finish` < '2010-02-04 03:19:37'
) AS u
GROUP
BY jobID
First of all, this piece of code works as a charm:
SELECT jobID
, SUM(total) AS total_total
FROM ( SELECT jobID
, total
FROM `time`
WHERE staffID = 8
AND `start` > '2010-02-01 00:00:01'
AND `finish` < '2010-02-28 23:59:59'
UNION ALL
SELECT jobID
, total
FROM `multitime`
WHERE staffID = 8
AND `start` > '2010-02-01 00:00:01'
AND `finish` < '2010-02-28 23:59:59'
) AS u
GROUP
BY jobID
I wanted to slightly modify the query to show total hours of time and total hours of multitime. A logic step, would be to add SUM() onto both internal (well, within the bracket) queries taking data directly from two tables.
However, when I added it, it immediately produces an error:
SELECT jobID
, SUM(total) AS total_total
FROM ( SELECT jobID
, SUM(total) AS TIME_total
FROM `time`
...
...
result:
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
Why can I sum main part of the query, while cannot do same with chunks of it?
SELECT jobID
, SUM(t_total) AS time_total
, SUM(mt_total) AS multitime_total
FROM ( SELECT jobID
, SUM(total) AS t_total
, NULL AS mt_total
FROM `time`
WHERE staffID = 8
AND `start` >= '2010-02-02 20:20:00'
AND `finish` < '2010-02-02 20:49:37'
GROUP
BY jobID
UNION ALL
SELECT jobID
, NULL AS t_total
, SUM(total) AS mt_total
FROM `multitime`
WHERE staffID = 8
AND `start` >= '2010-02-04 03:00:00'
AND `finish` < '2010-02-04 03:19:37'
GROUP
BY jobID
) AS u
GROUP
BY jobID