Question on Mysql

Databases
1

Hii Guys…
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

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:


SELECT 
    username
  , date
  , punch_time 
FROM attendance 
ORDER BY 
    username
  , date
  , punch_time
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…

Thanks…
Kiran

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?

5

Hii Guido …
Yes, am using PHP Runner, But thois is the first time am using it…

I have tried this query…
SELECT
Username,
date,
cast(group_concat(punch_time) as char) AS punches
FROM attendance
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…

Thanks ,
Kiran