SQL, MySQL and php

Question

I have 2 tables

CREATE TABLE `employee` (
  `Employee_ID` smallint(4) NOT NULL AUTO_INCREMENT,
  `Name` varchar(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`Employee_ID`)
) ;  
--- -----------------  
CREATE TABLE `attendance` (
  `Attendance_ID` int(9) NOT NULL AUTO_INCREMENT,
  `Employee_ID` smallint(3) NOT NULL,
  `Date` date NOT NULL,
  `Attendance` char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (`Attendance_ID`)
) ;  

For Attendance field it's a "P" when the employee is present "L" when late and "A" when absent.
I want to make a query that crosses the dates with employees names and show atendance.
Something similar to:

Attendance 07/02/2015   14/02/2015    21/02/2015  ...  
Diane           P           P               L  
Robert          A           P               P  
Karine          P           P               A  
...         

I didn't post any query because actually I failed many times to figure out how to Thanks for help

Answer 1

I do not know how to get dynamic columns in MySQL. This is how I would do it:

SELECT attd.Date, attd.Attendance, empl.Name FROM attendance attd, employee empl WHERE empl.Employee_ID = attd.Employee_ID

That will give result:

Then I would use server side code (example PHP):

<?php
$attendance = array();
foreach ($query as $row) {
    if (isset($attendance[$row['Name']])) {
        $attendance[$row['Name']][$row['Date']] = $row['Attendance'];
    } else {
        $attendance[$row['Name']] = array($row['Date'] => $row['Attendance']);
    }
}

print_r($attendance);

/* Array(
    "Diane" => Array("2015-02-07" => 'P', "2015-02-14" => 'P'),
    "Robert" => Array("2015-02-07" => 'P', "2015-02-14" => 'P'),
    "Karine" => Array("2015-02-07" => 'L', "2015-02-14" => 'L')
  )
*/

Answer 2

select 
    a.Employee_ID,
    min(e.Name) as Employee_Name,
    case when a.Date = dateadd(:basedate, interval 7*0 day) then min(a.Attendance) end as d0 /* d for data? */
    case when a.Date = dateadd(:basedate, interval 7*1 day) then min(a.Attendance) end as d1
    case when a.Date = dateadd(:basedate, interval 7*2 day) then min(a.Attendance) end as d2
    case when a.Date = dateadd(:basedate, interval 7*3 day) then min(a.Attendance) end as d3
    dateadd(:basedate, interval 7*0 day) as week0
    dateadd(:basedate, interval 7*1 day) as week1
    dateadd(:basedate, interval 7*2 day) as week2
    dateadd(:basedate, interval 7*3 day) as week3
from
    attendance as a inner join employee as e ...
where a.Date between :basedate and dateadd(:basedate, interval 7*3 day)
group by
    a.Employee_ID

You could pass in a :basedate parameter and offset a fixed number of weeks from that point. (I don't know what's the parameter convention for PHP and MySQL.) This will work if you can fix your results to a set number of weeks.

Pick out the dates from the first row to build the header and then build the rest of the table as you normally would. Yes there's some redundant data but there's no easy way to alias the columns with the dates themselves.

Answer 3

Following query you can use to gain the shown output along with a bit of PHP application

SELECT E.Name, A.Date, A.Attendance
FROM attendance AS A
INNER JOIN employee AS E
ON A.Employee_ID = E.Employee_ID
ORDER BY A.Date, E.Name

For implementing this in PHP

<?php

if (!$link = mysql_connect('< mysql_host >', '< mysql_user >', '< mysql_password >')) {
    echo 'Could not connect to mysql';
    exit;
}

if (!mysql_select_db('< mysql_dbname >', $link)) {
    echo 'Could not select database';
    exit;
}

$sql    =   'SELECT E.Name, A.Date, A.Attendance '.
            'FROM attendance AS A '.
            'INNER JOIN employee AS E '.
            'ON A.Employee_ID = E.Employee_ID '.
            'ORDER BY E.Name, A.Date';

$result = mysql_query($sql, $link);

if (!$result) {
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

$name = '';
$resultArray = array();

$attendance = array();
foreach ($query as $row) {
    if (isset($attendance[$row['Name']])) {
        array_push($attendance[$row['Name']], $row['Date'] => $row['Attendance']);
    } else {
        $attendance[$row['Name']] = array($row['Date'] => $row['Attendance']);
    }
}

print_r($attendance);

mysql_free_result($result);

?>

You would get an a associative array like this

Array(
    "Diane" => Array("2015-02-07" => 'P', "2015-02-14" => 'P'),
    "Robert" => Array("2015-02-07" => 'P', "2015-02-14" => 'P'),
    "Karine" => Array("2015-02-07" => 'L', "2015-02-14" => 'L')
)

Now you can use this associative array to in a loop to render these elements in the Web page

Hope this helps...