SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question on Mysql

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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:
    Code:
    SELECT 
        username
      , date
      , punch_time 
    FROM attendance 
    ORDER BY 
        username
      , date
      , punch_time

  3. #3
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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. #5
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •