kirankumer — 2012-01-17T02:58:14-05:00 — #1
I'm new member to this nice site...
Here my first question ..
1) I have attendance table (fields like username,date,puch_time) which contains punch timings of every employee .
My question is ..
I want to show a report like this ..
username | date | first_punch | second_punch | third_punch ..... .
My problem was...
2) SELECT username,date,group_concat(punch_time) as punches FROM attendance GROUP BY username,date;
Here i can get data of every employee in single column but i want to show these single data column(punches ) in diff columns like first_punch, second_punch ... so on ...
Guys please help me out ...Thanks in advance
guido2004 — 2012-01-17T04:45:18-05:00 — #2
Welcome to the SP forums.
We have a forum dedicated to MySQL, so I've moved your post there.
About your question: you might want to query like this, and then loop through the result with PHP (or whatever language you're using) and create your report:
kirankumer — 2012-01-17T23:50:01-05:00 — #3
Thanks guido for your reply...
I have tried this query, result will look like this ..
Username | Date | Punch_time
1) kiran 2012-01-01 09:00
2) kiran 2012-01-01 09:10
3) kiran 2012-01-01 10:00
4) kiran 2012-01-01 13:00
5) kiran 2012-01-01 14:16
6) kiran 2012-01-01 15:30
7) kiran 2012-01-01 18:00
8) monali 2012-01-01 09:00
9) monali 2012-01-01 10:06
10)monali 2012-01-01 11:10
But i want to show every employees results in single row like this ..
Username | Date | first_punch | second_punch | third_punch ....... as many punches entered in attendance table.
1) kiran 2012-01-01 09:00 09:10 10:00 ........................
I hope you understand what my problem was.....
My english was poor pls forgive me if any thing was wrong..
guido2004 — 2012-01-18T08:26:21-05:00 — #4
Yes, I understand that. That's why I said you'll have to loop through the result with PHP (or whatever language you're using) and style the output the way you want.
Are you using PHP?
kirankumer — 2012-01-20T00:47:40-05:00 — #5
Hii Guido ..
Yes, am using PHP Runner, But thois is the first time am using it...
I have tried this query...
cast(group_concat(punch_time) as char) AS punches
GROUP BY Username
ORDER BY date
Output looks like this...
Username | Date | punches
1) kiran 2012-01-20 09:00:10 , 09:10:15, 10:15:10,......
These punches i want to show on report page like
Username | Date | first_punch | second_punch | third_punch | fourth_puch ....
1) kiran 2012-01-20 09:00:10 09:10:15 10:15:10 11:00 ..............
My tool (PHP Runner) doesnt allow me to create new rows while it takes columns from table (attendance) so it gives me report like this
Username | Date | Punch_time
not more than these columns i cant create while generating a report...
so is there anyway to generate columns randomly as based on my total number of punches (If i punch a total of 10 in a day it should display punches from first to ten randomly..)
All these are for display purpose only i dont need to do any operations on my table...(On front end i have to display my punches on report no action on my table)
Pls help me..