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
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:
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..
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?
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..
This topic is now archived. It is frozen and cannot be changed in any way.