| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-30-12, 00:57
|
|
Registered User
|
|
Join Date: Jan 2012
Location: pune
Posts: 16
|
|
|
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
|
|
01-30-12, 05:59
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,538
|
|
use GROUP_CONCAT, just like in your other thread on the same problem
|
|
01-30-12, 06:52
|
|
Registered User
|
|
Join Date: Jan 2012
Location: pune
Posts: 16
|
|
|
|
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 ....
|
|
01-30-12, 07:38
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,538
|
|
Quote:
Originally Posted by kirankumer
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
|
|
01-30-12, 07:50
|
|
Registered User
|
|
Join Date: Jan 2012
Location: pune
Posts: 16
|
|
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 ....
|
|
01-30-12, 14:04
|
|
Registered User
|
|
Join Date: Mar 2004
Posts: 440
|
|
it has been explained to you in more than one forum, in more than one thread.
|
|
01-31-12, 00:03
|
|
Registered User
|
|
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...
|
|
01-31-12, 03:02
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 9,262
|
|
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 my Versys or my Tiger 800 let alone the Norton 
|
|
02-01-12, 11:06
|
|
Registered User
|
|
Join Date: May 2008
Posts: 270
|
|
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>—</td>\n";
}
}
echo "</tr>\n";
}
echo "</table>\n";
|
|
02-17-12, 23:37
|
|
Registered User
|
|
Join Date: Jan 2012
Location: pune
Posts: 16
|
|
Thanks Guys....I solved this problem
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How to show mutiple records on single record
