Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2012
    Location
    pune
    Posts
    16

    Unanswered: How to show mutiple records on single record

    Hii Friends...

    I have database with table called attendance..

    Userid Date Punch_time
    1 01-01-2012 09:00 am
    1 01-01-2012 09:15 am
    1 01-01-2012 11:02 am
    1 01-01-2012 12:59 pm
    1 01-01-2012 15:55 pm
    1 01-01-2012 17:59 pm
    2 01-01-2012 09:10 am
    2 01-01-2012 10:45 am
    2 01-01-2012 01:02 pm
    2 01-01-2012 18:02 pm

    like this for every employee and for every day records will be inserted in " attendance " table ...

    Now i want a query to show output like this ..

    Userid | Date | first_punch | second_punch | third_punch | fourth_punch | fifth_punch | sixth_punch

    1) 1 2012-01-01 09:00 am 09:15 am 11:02

    12:59 pm 15:55pm 17:59 pm

    2) 2 2012-01-01 09:10 am 10:45 am 01:02 pm

    18:02 pm ---- ----


    Is there anyway to show records like this ....Am worrying of this code from last week .


    Please help me guys...

    Thanks ,
    Kiran

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use GROUP_CONCAT, just like in your other thread on the same problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Location
    pune
    Posts
    16

    Arrow

    Thanks for your reply..

    With group_concat() the output looks like this ..

    Username | Date | first_punch
    1) kiran 2012-01-20 09:00 am,09:15 am,11:02,12:59 pm,15:55pm,17:59 pm


    All different punch_time's show under only single column..

    But i want to display all these on different columns starts from first_punch as mentioned above ....

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kirankumer View Post
    All different punch_time's show under only single column..
    what language are you using with this? php?

    because that's where you should be doing the re-formatting into columns

    in fact, you should probably be doing it from a simple SELECT, and not bother with the GROUP_CONCAT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2012
    Location
    pune
    Posts
    16

    Arrow

    Yes, am using php

    But with out using group_concat how can we do this...?

    I am trying it from last week in different ways (with out group_concat also) , but i dint get it..

    Please explain me how can we do this ....

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    it has been explained to you in more than one forum, in more than one thread.

  7. #7
    Join Date
    Jan 2012
    Location
    pune
    Posts
    16
    Yes sir,

    Actually am new to php that's why i posted here to know more about this problem..

    But i dint get the logic to work on the query ...

    That's the reason i asked many times...

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its a php problem then ask the question in the PHP section.
    if its a MySQL problem then ask it in the MySQL section

    doing this in PHP is pretty trivial
    query the db
    retrievbe the data and format it as you require
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2008
    Posts
    277
    PHP Code:
    /*
    sample data:
     Userid |    Date    | Punch_time
    -------+------------+------------
          1 | 01-01-2012 | 09:00 am
          1 | 01-01-2012 | 09:15 am
          1 | 01-01-2012 | 11:02 am
          1 | 01-01-2012 | 12:59 pm
          1 | 01-01-2012 | 15:55 pm
          1 | 01-01-2012 | 17:59 pm
          2 | 01-01-2012 | 09:10 am
          2 | 01-01-2012 | 10:45 am
          2 | 01-01-2012 | 01:02 pm
          2 | 01-01-2012 | 18:02 pm
    */

    // Determine max number of punch-ins by all users for the date in question.
    // For the sample data above, this is 6 (by user 1)
    $max_punches 6;

    // Fetch data into an array, ordered by user and punch-in time
    $rows = array(
        array(
            
    'user_id' => 1,
            
    'date' => '01-01-2012',
            
    'time' => '09:00',
        ),
        array(
            
    'user_id' => 1,
            
    'date' => '01-01-2012',
            
    'time' => '09:15',
        ),
        
    /* etc */
    );

    // output table header
    echo <<<OUT
    <table>
    <tr>
    <th>User</th>
    <th>Date</th>\n
    OUT;
    for (
    $i 0$i $max_punches$i++) {
        
    printf("<th>Punch-In %d</th>\n"$i+1);
    }
    echo 
    "</tr>\n";


    // now output each user's punch-in times
    $current_row 0;
    while (isset(
    $rows[$current_row])) {
        echo 
    "<tr>\n";
        echo 
    "<td>{$rows[$current_row]['user_id']}</td>\n";
        echo 
    "<td>{$rows[$current_row]['date']}</td>\n";

        
    $current_user $rows[$current_row]['user_id'];

        for (
    $i 0$i $max_punches$i++) {
            if (isset(
    $rows[$current_row])
                && 
    $current_user == $rows[$current_row]['user_id']
            ) {
                
    // still on the current user; output the time
                
    echo "<td>{$rows[$current_row]['time']}</td>\n";
                
    $current_row++;
            }
            else {
                
    // we've run out of times for the current user; just output dashes
                
    echo "<td>&mdash;</td>\n";
            }
        }
        echo 
    "</tr>\n";
    }

    echo 
    "</table>\n"

  10. #10
    Join Date
    Jan 2012
    Location
    pune
    Posts
    16
    Thanks Guys....I solved this problem

Posting Permissions

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