I'm making a timesheet submit/approve function and currently working on the pending.php page, which the manager/admin can go to pending.php and view the pending timesheets for review...
my code now is:
list($qh,$num) = dbQuery(
"SELECT start_time, end_time, uid, submitid, submitstatus, submitdate, submitapprover
FROM $TIMES_TABLE
WHERE submitstatus=1
ORDER BY submitid");
right now it shows all the timesheet entries for that week: example
what I really need is just one line for each week submitted. Basically, grabbing the first start_time and the last end_time and making it together (start - end) (start_time - end_time | username | id | submitdate | submit status..etc)
Someone told me to use group_catcon or something but I'm unfamiliar with that.
From my pic I would want something like:
2012-12-30 - 2013-01-05 | admin | submitid#### | submitdate | status | approver
2013-01-06 - 2013-01-09 | admin | submitid#### | submitdate | status | approver
I'm pretty new to php/mysql so my apologies
You may find with all these columns it divides things up more than you want. For example of there's various approvers. To that end you may want to remove some from the query.
select
concat(min(start_time), ' - ', max(end_time)),
uid,
submitid,
submitstatus,
submitdate,
submitapprover
FROM
$TIMES_TABLE
WHERE
submitstatus=1
GROUP BY
uid,
submitid,
submitstatus,
submitdate,
submitapprover
ORDER BY
submitid
I have built many timesheet applications in many languages. You can use group_concat it ties strings together with a comma, but i do not think that is what you need.
The catch is that you have to use it together with group by. From your db structure it might not work for you.
The rule with "group by" is that you have to select columns that are either used in the "group by" list of columns or you have to use an aggregate function on that column.
on a books table that has the following structure books_sold (id, author_id, date, price) you can do the following operations
select sum(price), author FROM books_sold group by author
To get the total sum of the books per author.
select sum(price), date FROM books_sold group by date
to get the total sum of the books sold per date.
select group_concat(id), date FROM books_sold group by date
TO get the the id of all the books sold per date. The ids will be separated by comma
But you cannot do a
select group_concat(id), date, author FROM books_sold group by date
because on the author column you are not doing a group by, or a mass operation. The query works but the author column is not reliable.
Now from your db structure, I do not think you can do a group_concat and still get the fields you desire. What happens if the approver is not the same guy in a week? Your approver column will not make sense. What happens if the submitid is not the same in an entire week? What happens if the submitdate is not the same on an entire week?
If those columns are always the same you can do a
select CONCAT(min(start_time), ' - ', max(end_time)), uid, submitid, submitstatus, submitdate, submitapprover FROM $TIMES_TABLE WHERE submitstatus=1 GROUP BY uid, submitid, submitstatus, submitdate, submitapprover ORDER BY submitid