Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am doing a School Management System in php using Mysql DB. I am stuck in my Project. Please anybody suggest what I am doing wrong.

I have two tables in my database; one is to store Students records another one is to store their attendance day wise

Now I want to display a report of all students in particular class for the current month whether they were present or absent. But I am capturing only Absent students details only in attendance table.

I have written sql query to display the result here is:

SELECT tab.class, attend, DATE, ta.rollno, ta.StdNm 
FROM tbl_absentees tab, tbl_admission ta
WHERE ta.Cls = class
  AND ta.rollno = tab.rollno
  AND class =22
  AND attend =  'A'
  AND DATE =  '2013-06-07';

The result is:

Class Attend RollNo StudentName

But I want to display in 31 days table manner by taking only Date in attendance table if attend=A display A for absent Days otherwise Display 'P' for remaining days

How can I do this in mysql? Can anyone suggest/give me an idea to achieve this.

Sorry for misclarification in my question. Actually I want to display an attendance report for a particular month where data comes from two tables:

  • the first table consists of StudentName, RollNo, Class
  • the second table consists of Date, Status, RollNo, Class

Now I want to display report like this.

share|improve this question
1  
This seems pretty straight-forward to me, and I'd answer if I was more familiar with the syntax for MySQL. My only question is: do you want to return rows for days where attendance hasn't been taken? I.e., if I run this query right now for July 2013, should there be rows returned for July 20th? –  Jon Seigel Jul 9 '13 at 17:00
1  
@JonSeigel What is not clear is the wanted format of the output. I guess it will be 31 columns x (No of students) rows but it's not entirely clear. –  ypercube Jul 9 '13 at 17:13
1  
My suggestion would be to look at Stack Overflow and search mysql + pivot you will find a lot of answers. Even further look at prepared statements to do this dynamically. –  bluefeet Jul 9 '13 at 17:22
    
@ypercube: The date was selected in the query, so I assume it's one row per student per class per day. The date predicate in the WHERE clause is nonsensical given what's being asked, so I assume to ignore that. –  Jon Seigel Jul 9 '13 at 19:13
add comment

1 Answer

up vote 11 down vote accepted

This type of rotation of data from columns to rows is known as a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.

My first suggestion would be to determine if you have a calendar table or a table that contains all of the dates that you want to display. If not, then I would suggest creating one similar to the following:

CREATE TABLE calendar (`Date` datetime) ;

INSERT INTO calendar (`Date`)
VALUES
    ('2013-06-01 00:00:00'),
    ('2013-06-02 00:00:00'),
    ('2013-06-03 00:00:00'),
    ('2013-06-04 00:00:00'),
    ('2013-06-05 00:00:00'),
    ('2013-06-06 00:00:00'),
    ('2013-06-07 00:00:00'),
    ('2013-06-08 00:00:00'),
    ('2013-06-09 00:00:00'),
    ('2013-06-10 00:00:00');

This will make it possible for you to generate a list of all of the dates that you want to display.

Second, you will need to generate the list of each student and each date. You can do this by using a CROSS JOIN between your tbl_admission and the calendar table:

select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a;

See Demo. Once you have this list, then you can use a LEFT JOIN to your existing tbl_absentees table to get the result:

select 
  ca.studentname,
  ca.rollno,
  ca.class,
  max(case when ca.date = '2013-06-01' then coalesce(p.status, 'P') end) `2013-06-01`,
  max(case when ca.date = '2013-06-02' then coalesce(p.status, 'P') end) `2013-06-02`,
  max(case when ca.date = '2013-06-03' then coalesce(p.status, 'P') end) `2013-06-03`,
  max(case when ca.date = '2013-06-04' then coalesce(p.status, 'P') end) `2013-06-04`,
  max(case when ca.date = '2013-06-05' then coalesce(p.status, 'P') end) `2013-06-05`,
  max(case when ca.date = '2013-06-06' then coalesce(p.status, 'P') end) `2013-06-06`,
  max(case when ca.date = '2013-06-07' then coalesce(p.status, 'P') end) `2013-06-07`,
  max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-08`,
  max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-09`,
  max(case when ca.date = '2013-06-10' then coalesce(p.status, 'P') end) `2013-06-10`
from
(
  select c.date, a.studentname, a.rollno, a.class
  from calendar c
  cross join tbl_admission a
) ca
left join tbl_absentees p
  on ca.rollno = p.rollno
  and ca.date = p.date
group by ca.studentname, ca.rollno, ca.class
order by ca.rollno;

See SQL Fiddle with Demo. Of course for your request, you most likely want to query the data based on a date range so you will not want to hard-code the values. If that is the case, then you will need to look at using a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN ca.date = ''',
      date_format(date, '%Y-%m-%d'),
      ''' THEN coalesce(p.status, ''P'') END) AS `',
      date_format(date, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
FROM calendar
where date>='2013-06-01'
  and date <= '2013-06-05';

SET @sql 
  = CONCAT('SELECT ca.studentname,
              ca.rollno,
              ca.class, ', @sql, ' 
            from
            (
              select c.date, a.studentname, a.rollno, a.class
              from calendar c
              cross join tbl_admission a
            ) ca
            left join tbl_absentees p
              on ca.rollno = p.rollno
              and ca.date = p.date
            where ca.date>=''2013-06-01''
              and ca.date <= ''2013-06-05''
            group by ca.studentname, ca.rollno, ca.class
            order by ca.rollno');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo. Both of these queries will give a result similar to:

| STUDENTNAME | ROLLNO | CLASS | 2013-06-01 | 2013-06-02 | 2013-06-03 | 2013-06-04 | 2013-06-05 | 2013-06-06 | 2013-06-07 | 2013-06-08 | 2013-06-09 | 2013-06-10 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|       Naren |      1 |    22 |          A |          A |          A |          A |          P |          P |          P |          P |          P |          P |
|       Srinu |      2 |    22 |          P |          P |          P |          P |          P |          P |          P |          P |          P |          P |
|        Blah |      3 |    22 |          A |          P |          P |          P |          P |          P |          P |          P |          P |          P |
share|improve this answer
    
Thank you for posting answer to my question. Above code is working fine, but up to 11 days only. if i mention date>='2013-06-01' and date <= '2013-06-31'; i am getting error.MySql Query Browser showing me a Syntax error. But I didn't changed anything except date range. Could you pl tell me y the above code is not working for me. I want to display the report for 31 days –  Narendar_CH Jul 13 '13 at 5:48
    
@Narendar_Dotnet What is the error that you get? It could be that you are reaching the limits of the group_concat_max_len. The default length is 1024, you might need to adjust the length for your session using set session group_concat_max_len= = newValue. If you post your syntax error that would be helpful though. –  bluefeet Jul 13 '13 at 12:02
    
plz look at this also sqlfiddle.com/#!2/190fa/1 –  Narendar_CH Jul 15 '13 at 6:21
1  
@Narendar_Dotnet As I said in my previous comment, you need to expand the group_concat_max_len -- see this demo -- sqlfiddle.com/#!2/190fa/10 –  bluefeet Jul 15 '13 at 11:32
3  
@Narendar_CH My suggestion is if you have more questions, then to post a new question. –  bluefeet Jul 17 '13 at 11:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.